Now that you have created a custom inventory, how do you know which SQL tables are storing the data? This tip will show you how.
Note: This only applies to Notification Server 6.0 SP3 and Inventory Solution for Windows 6.1 SP3.
At the time a Custom Inventory file is created, the Altiris administrator is responsible for defining the schema that will be used by SQL to create the primary table that will store the data being captured. The InventoryClass name provides the table name and each AttributeType correspond to the names of the columns that will be used to create the table.
A visual list of the InventoryClass Name and the column names are available by opening the Notification Server Console, and navigating to the following location:
Configuration tab > Configuration > Resource Settings > Default Folder
By default, each custom inventory file will have an entry in this folder. Note that it is possible to move these items into other areas of the console.
Selecting an item from the Default Folder, the menu on the right-hand pane will display the information about the data class. The fifth entry from the top named Data Table Name provides the SQL table name that was created to store the information. The attributes listed below display the column headers.
Note: If an administrator requires changes to the dataclass, the correct method is to remove the data class from the Default Folder by right-clicking on the item and choosing Delete. When this action is performed, references to the Data table Name are removed from 14 tables in the Altiris Database. In the event that the proper delete method is not used, the list below can be used to clean up references to the DataClass. Remember that this step is not necessary if the DataClass was deleted using the supported method above.
Begin by identifying the GUID that is associated with the Dataclass. The following SQL query can assist with this:
select [name], [GUID] from item where [name] = 'Name of Custom Inventory Data Class'
Using the GUIDs from the previous query create a SQL statement to remove all rows referencing the GUID from the following tables:
- DataClass
- DataloaderTemplateInfo
- Item
- ItemCache
- ItemFolder
- ItemNameCache
- ItemPresentation
- ItemReference
- ResourceTypeDataClass
- ResourceUpdateSummary
- SecurityACE
- SecurityEntity
- String
- StringCacheRN
Prior to running any delete statements, it is recommended that the administrator review how the data is stored in these tables. In some cases, a single row is used, in other cases multiple rows are used. The SQL below might help:
select * from dataclass where Guid = 'PutYourGuidHere'
select * from DataloaderTemplateInfo where DataClassGuid = 'PutYourGuidHere'
select * from SecurityACE where EntityGuid = 'PutYourGuidHere'
select * from DataClass where Guid = 'PutYourGuidHere'
select * from DataloaderTemplateInfo where DataClassGuid= 'PutYourGuidHere'
select * from Item where Guid = 'PutYourGuidHere'
select * from ItemCache where ItemGuid= 'PutYourGuidHere'
select * from ItemFolder where ItemGuid= 'PutYourGuidHere'
select * from ItemNameCache where ItemGuid= 'PutYourGuidHere'
select * from ItemPresentation where BaseGuid= 'PutYourGuidHere'
select * from ItemPresentation where Guid= 'PutYourGuidHere'
select * from ItemReference where ChildItemGuid= 'PutYourGuidHere'
select * from ResourceTypeDataClass where ResourceDataClassGuid= 'PutYourGuidHere'
select * from ResourceUpdateSummary where InventoryClassGuid= 'PutYourGuidHere'
select * from SecurityACE where EntityGuid= 'PutYourGuidHere'
select * from SecurityEntity where Guid= 'PutYourGuidHere'
select * from String where BaseGuid= 'PutYourGuidHere'
select * from StringCacheRN where BaseGuid= 'PutYourGuidHere'
drop table '[Custom Inventory Data Table Name]'
After deleting all references to the Custom Data Class, changes to the custom inventory can be made. The first time the new custom inventory is run, the DataClass will be re-created.
It is also important to remove any previously created .nsi or .bak files from client computers that reference the old dataclass.