How to display Agent version in a column of the list of computer in DSM Explorer ?

Document ID : KB000032503
Last Modified Date : 14/02/2018
Show Technical Document Details

Summary:

In DSM Explorer in the list of computers it is possible to add columns like IP Address, MAC Address. But ITCM Agent version is not available.
It could be interesting to have this information available in the list of computers to easily see the version of the computers.
 
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 the ITCM Agent version
And in DSM Explorer this field could be as a column in the list of computers.
 
This could be done by using a SQL Query scheduled to be executed at regular time via an Engine SQL script.

  

Instructions: 

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_def4=V.agent_component_version FROM (select U.object_uuid, MAX(U.agent_component_version) agent_component_version FROM (select a.object_uuid,c.agent_component_version from ca_agent_component c, ca_agent a WHERE c.agent_comp_id=5 and a.object_uuid=c.object_uuid and a.agent_type=1 UNION select a.object_uuid, '12.5.1110.142' FROM ca_agent a, ca_software_def s, ca_discovered_software d where s.name like '%12.5 SP1 C1%' and d.asset_source_uuid=a.object_uuid and d.sw_def_uuid=s.sw_def_uuid and a.agent_type=1 UNION select a.agent_name, '12.5.1010.1422' FROM ca_agent a, ca_software_def s, ca_discovered_software d where s.name like '%12.5 SP1 FP1%'and d.asset_source_uuid=a.object_uuid and d.sw_def_uuid=s.sw_def_uuid and a.agent_type=1) as U group by U.object_uuid) as V WHERE V.object_uuid=ca_agent.object_uuid and ca_agent.agent_type=1

 

 

 

query3.jpg 

 

Remark : 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 4 of the computers should be filled with the ITCM Agent version.

 

version.jpg 

 

 

 

Remark : If the field User data 4 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 4 could be added in DSM Explorer in order to display the Agent version :

 

 

gui2.jpg