How to report on Discovered Software using the database

Document ID : KB000072481
Last Modified Date : 01/03/2018
Show Technical Document Details
Question:
How to generate a report of discovered software from the database?
Environment:
Client Automation (ITCM) -- any version.
Answer:
The following queries can be used to generate a report of all software discovered, per agent.

-- All software per computer
select ca_agent.agent_name, ca_software_def.name, ca_software_def.sw_version_label
from ca_agent
inner join ca_discovered_software
on ca_agent.object_uuid=ca_discovered_software.asset_source_uuid
inner join ca_software_def
on ca_discovered_software.sw_def_uuid=ca_software_def.sw_def_uuid
order by ca_agent.agent_name, ca_software_def.name, ca_software_def.sw_version_label

-- Signature-based software discovery only
select ca_agent.agent_name, ca_software_def.name, ca_software_def.sw_version_label
from ca_agent
inner join ca_discovered_software
on ca_agent.object_uuid=ca_discovered_software.asset_source_uuid
inner join ca_software_def
on ca_discovered_software.sw_def_uuid=ca_software_def.sw_def_uuid
and ca_software_def.source_type_id in (1,2)
order by ca_agent.agent_name, ca_software_def.name, ca_software_def.sw_version_label

-- Heuristic-based software discovery only
select ca_agent.agent_name, ca_software_def.name, ca_software_def.sw_version_label
from ca_agent
inner join ca_discovered_software
on ca_agent.object_uuid=ca_discovered_software.asset_source_uuid
inner join ca_software_def
on ca_discovered_software.sw_def_uuid=ca_software_def.sw_def_uuid
and ca_software_def.source_type_id=3
order by ca_agent.agent_name, ca_software_def.name, ca_software_def.sw_version_label

-- Intellisig-based software discovery only
select ca_agent.agent_name, ca_software_def.name, ca_software_def.sw_version_label
from ca_agent
inner join ca_discovered_software
on ca_agent.object_uuid=ca_discovered_software.asset_source_uuid
inner join ca_software_def
on ca_discovered_software.sw_def_uuid=ca_software_def.sw_def_uuid
and ca_software_def.source_type_id in (5,6)
order by ca_agent.agent_name, ca_software_def.name, ca_software_def.sw_version_label
Additional Information:
A public version of the ITCM MDB schema can be found here:
https://support.ca.com/phpdocs/0/common/impcd/r11/MDBMain/schema/viewer/index.htm

Note that this schema was last updated in 2006.  While much has changed, not really much has changed.  Recommend browsing tables by using the "alphabetic" tab and locating the table by name.