Report on Discovered Software using the database

Products

• CA Client Automation

Issue/Introduction

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

Example of SQL Queries :

• All software per computer
• Signature-based software discovery only
• Heuristic-based software discovery only
• Intellisig-based software discovery only

Environment

Client Automation - All versions

Resolution

The following SQL 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, 
  DATEADD(ss, ca_discovered_software .creation_date+ datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Detected Date'
  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 h.label, s.name, s.sw_version_label, 
  DATEADD(ss, d.creation_date+ datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Detected Date'
  FROM ca_discovered_software d
  INNER JOIN ca_software_def s ON d.sw_def_uuid=s.sw_def_uuid
  INNER JOIN ca_discovered_hardware h ON d.asset_source_uuid=h.dis_hw_uuid
  WHERE dis_source_type_id=1
  ORDER BY name, sw_version_label

  
  
  
• Heuristic-based software discovery only
  

  SELECT ca_agent.agent_name, ca_software_def.name, ca_software_def.sw_version_label,
  DATEADD(ss, ca_discovered_software.creation_date+ datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Detected Date'
  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
  WHERE ca_discovered_software.dis_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,
  DATEADD(ss, ca_discovered_software.creation_date+ datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Detected Date'
  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.