How to use SQL to Query Software Inventory Reports from the database.
Article ID: 74532
Updated On:
Products
CA Client Automation - Asset Management
CA Client Automation - IT Client Manager
CA Client Automation
CA Client Automation - Remote Control
CA Client Automation - Asset Intelligence
CA Client Automation - Desktop Migration Manager
CA Client Automation - Patch Manager
Issue/Introduction
This document will provide an example of how to query discovered software inventory directly from the database.
Environment
Client Automation (ITCM) -- any version.
Resolution
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
-- 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
Feedback
Yes
No
Powered by
