How to display General Inventory item in a column of the list of computer in DSM Explorer

Document ID : KB000010676
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:
In DSM Explorer in the list of computers it is possible to add columns like IP Address, MAC Address.
But column for General Inventory items could not be added in the list of columns displayed.
 
 
The computers have 4 additional fields which could be used to put custom information : User data 1, User data 2, User data 3, User data 4
 
One of this field could be used to put one inventory stored in General Inventory.
 
This could be done by using a SQL Query scheduled to be executed at regular time via an Engine SQL script.
 
 
 
Instructions:

Example for System/System ID in User Data 2 field:

 

Capture.JPG

 

 

1- In DSM Explorer select one engine (eg: SystemEngine) and create a new Engine task with type SQL Query :

 

Engine.jpg 
 
 
and put this SQL Query :
 

update ca_agent set ca_agent.user_def2=V.item_value_text FROM (select object_uuid, item_value_text from inv_generalinventory_item where item_parent_name_id=(select tree_name_id from inv_tree_name_id where tree_name='$System$' and domain_uuid=(select set_val_uuid from ca_settings where set_id=1)) and item_name_id=(select item_name_id from inv_item_name_id where item_name='System ID' and domain_uuid=(select set_val_uuid from ca_settings where set_id=1))) V WHERE V.object_uuid=ca_agent.object_uuid and ca_agent.agent_type=1

 

 

 

query3.jpg 

 

Remarks : The query should be put in a single line. It should not be cut with carriage return.

 

 

Then we could schedule it for an execution once a day

 

schedule.jpg 

 

 

2- Once the job executed by the Engine, the field User Data 2 of the computers should be filled with the System ID.

 

Capture2.JPG

 

 

Remark : If the field User data 2 is already used for another information the beginning of SQL Query should be updated with field number to use :

 

Example for the field User data 3

 

update ca_agent set ca_agent.user_def3

 

 

 

3- Then the column User data 2 could be added in DSM Explorer in order to display the System ID for all computers :

 Capture3.JPG

 

 

 

 

 

Other examples Queries :

 

- System/Asset Tag :

 

update ca_agent set ca_agent.user_def2=V.item_value_text FROM (select object_uuid, item_value_text from inv_generalinventory_item where item_parent_name_id=(select tree_name_id from inv_tree_name_id where tree_name='$System$' and domain_uuid=(select set_val_uuid from ca_settings where set_id=1)) and item_name_id=(select item_name_id from inv_item_name_id where item_name='Asset Tag' and domain_uuid=(select set_val_uuid from ca_settings where set_id=1))) V WHERE V.object_uuid=ca_agent.object_uuid and ca_agent.agent_type=1

 

 

- System/Total Memory (in GB) :

 

update ca_agent set ca_agent.user_def2=V.item_value_double/(1024*1024*1024) FROM (select object_uuid, item_value_double from inv_generalinventory_item where item_parent_name_id=(select tree_name_id from inv_tree_name_id where tree_name='$System$' and domain_uuid=(select set_val_uuid from ca_settings where set_id=1)) and item_name_id=(select item_name_id from inv_item_name_id where item_name='Total Memory' and domain_uuid=(select set_val_uuid from ca_settings where set_id=1))) V WHERE V.object_uuid=ca_agent.object_uuid and ca_agent.agent_type=1

 

 

 

- System/Processors/Core Count :

update ca_agent set ca_agent.user_def2=V.item_value_long FROM (select object_uuid, item_value_long from inv_generalinventory_item where item_parent_name_id=(select tree_name_id from inv_tree_name_id where tree_name='$Processors$' and domain_uuid=(select set_val_uuid from ca_settings where set_id=1)) and item_name_id=(select item_name_id from inv_item_name_id where item_name='Core Count' and domain_uuid=(select set_val_uuid from ca_settings where set_id=1))) V WHERE V.object_uuid=ca_agent.object_uuid and ca_agent.agent_type=1

 

 

- Operating System/Display Settings/Resolution

update ca_agent set ca_agent.user_def2=V.item_value_text FROM (select object_uuid, item_value_text from inv_generalinventory_item where item_parent_name_id=(select tree_name_id from inv_tree_name_id where tree_name='$Display Settings$' and domain_uuid=(select set_val_uuid from ca_settings where set_id=1)) and item_name_id=(select item_name_id from inv_item_name_id where item_name='Resolution' and domain_uuid=(select set_val_uuid from ca_settings where set_id=1))) V WHERE V.object_uuid=ca_agent.object_uuid and ca_agent.agent_type=1

 

 

See also this document for Agent Version column :

 

TEC1578246