How to report on Discovered Software using the database

Products

• CA Client Automation

Issue/Introduction

How to generate a report of discovered software from the database?

Environment

Client Automation - All versions

Resolution

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:

MDB 1.4 Schema Overview

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.