How to use SQL to Query Software Inventory Reports from the database.

Document ID : KB000074532
Last Modified Date : 22/03/2018
Show Technical Document Details
Introduction:
This document will provide an example of how to query discovered software inventory directly from the database.
 
Environment:
Client Automation (ITCM) -- any version.
Instructions:
The following queries can be used as a starting point for querying discovered software, and can be tailored as needed...

-- 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