How to create a CMDB rule to automatically assign locations to computers or users

Products

• CMDB Solution

Issue/Introduction

 A computer or user's location is not automatically assigned. This requires the customer to either manually assign one or implement a method to automatically do so. 

Environment

ITMS 7.x, 8.x

Resolution

The following instructions demonstrate how this can be accomplished by using a CMDB rule. Note: The customer can adapt these instructions to automatically assign locations to other asset types, such as to users or monitors, or to automatically assign other types of associations, such as departments or cost centers, to assets. If so, the SQL script would need to be changed by the customer to leverage the appropriate resource type, such as to vUser for users or vMonitor for monitors.

Important Note: A CMDB rule requires standardized values to be present in records to be able to identify these as what to evaluate. For example, if a computer's name also includes its location name, this can be identified and used to associate a CMDB location to. Or for users, an address or city or state can be evaluated if that data is present. But if no such standardized values exist in some or all records to be evaluated, then there is no reasonable logic that can be used by the CMDB rule to automate the assignment of locations. The customer would need to either first determine how data normalization can be implemented or decide on a different way to automatically assign locations to assets other than using a CMDB rule.
 

1. In the Symantec Management Platform Console, click on the Settings button > All Settings.
2. Click to expand the folders Notification Server > Connector > CMDB Rules.
3. Right click on the CMDB Rules folder and then click on New >  CMDB Rule.
4. Click in the New CMDB Rule title at the top of the CMDB rule page to change its name.
5. Click on the "Resource type" drop-down list and select the type of resource to assign locations to, such as Computer or User.
6. Click on the "Target using" drop-down list and select "Sql Query".
7. Enter a SQL script into the "SQL query" field. For example, the following SQL scripts can be used. Note: The locations as identified in computer names must already exist as CMDB locations. Instructions for how to create locations can be found in HOWTO85119, referenced below in the Related Articles section.
   
   -- Use the following SQL script if setting a computer's location, if the location is also found in the computer's name. For example, the computer "XYZ Springfield" includes "Springfield".
   SELECT va._ResourceGuid, vi.Name, 'Location' = CASE
   WHEN vi.Name LIKE 'Springfield%' THEN 'Springfield'
   WHEN vi.Name LIKE 'Lindon%' THEN 'Lindon'
   ELSE ''
   END
   FROM vAsset va
   LEFT JOIN vItem vi
   ON vi.Guid = va._ResourceGuid
   WHERE va.[Asset Type] = 'Computer'
   ORDER BY 2
   /*
   This SQL script identifies two pre-set location names, Springfield and Lindon, as found in computer names. The customer should change these and make additional WHEN statements for each CMDB location that they want to automatically map assets to. These are programmed to identify the location as the first part of a computer's name, such as "Springfield Test PC", where the location is then derived as "Springfield". If the location name is at the end of the computer name, change the wildcard "%" to be at the beginning of the name, for example:
   
   WHEN vi.Name LIKE '%Springfield' THEN 'Springfield'
   
   This SQL script also sets the "location" value to be blank if nothing is found in the computer's name. This will result in the CMDB rule skipping this record during processing. If some or many computers don't have this naming convention, then the customer should expect many skipped rows; this is normal, therefore.
   */
   
   -- Or, use the following SQL script if setting a user's location, if the user's zip code and the location's match.
   SELECT vu.Guid, vu.Name 'User', vl.Name 'Location'
   FROM vUser vu
   JOIN vLocation vl
   ON vu.Zip = vl.Zip
   ORDER BY 2
    
8. In the "Step 3: Add resource associations to modify" section, find and modify the Location association by making the following changes to it:
   
   Lookup key: Resource Name
   Name: Location
    
9. Click on the "Save changes" button.   
    
10. Click on the "Test rule" button. The CMDB Rule Run Status window appears. Note: Before running this on a production server, ensure that the "Test rule" button is used to verify that the records to be set are what is expected.
11. When ready to begin a live run, click on the "Run now" button. This sets the locations to computers based on the SQL script. Note: To automate this CMDB rule to run on a schedule, click on the Schedule drop-down and configure a schedule for it. This will then automatically run and update computers based on the programmed schedule.
12. For convenience, the SQL script examples from step 7 are saved as two example CMDB rules to this article. To use  these, save them as .xml files to the Symantec Management Platform server, and then:
    
    a. In a Symantec Management Platform Console, click on Settings > All Settings.
    b. Click on to expand the folders Notificatin Server > Conneector > CMDB Rules.
    c. Right click on the CMDB Rules folder > Import, then point to the .xml file to import.

 

Additional Information

How to create locations and automatically assign them to computers or users
181507

How to create a Data Connector import rule to automatically assign computers or users to locations
181511

Import Rule fails to set or removes Asset Owner or Cost Center
155139