Obtain the Location of a Software using a SQL Query

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

When looking at Software inventory tab of any Agent, some of the items show the 'Location' in which it was detected:

How to get this data directly from the MDB?

Environment

Client Automation 14.x

Resolution

The data of the Software inventory can be found under "ca_discovered_software", and in order to link this to the actual Agent that has this data, the following query can be executed:

SELECT ag.agent_name Agent, df.name Software_Name, df.sw_version_label Version, ds.install_path Location
FROM ca_discovered_software ds
LEFT JOIN ca_software_def df ON df.sw_def_uuid=ds.sw_def_uuid
LEFT JOIN ca_agent ag ON ag.object_uuid = ds.asset_source_uuid
WHERE ag.agent_type ='1'
and ag.agent_name = 'XYZ'

This query returns the following:

NOTE: It's required to replace 'XYZ' with the actual hostname of the Agent.
NOTE2: If it's required to add more fields, first check in which table they are stored.