SQL query to list all the softwares populated for an agent through Signature scanning and Heuristic scanning.

Products

• CA Client Automation - IT Client Manager
• CA Client Automation - Asset Management

Issue/Introduction

SQL query to list all the softwares populated for an agent through Signature scanning and Heuristic scanning.

Environment

All Versions.

Resolution

Here is a SQL Query which returns all Software Inventory for all machines :

SELECT h.host_name 'Computer Name', d.name 'Software Name',
d.sw_version_label 'Software Version',
CASE d.source_type_id
   WHEN 1 THEN 'CA'
   WHEN 2 THEN 'Custom'
   WHEN 3 THEN 'Heuristic'
   WHEN 5 THEN 'CA Intellisig'
END 'Detection Method',
CASE d.software_type_id
   WHEN 1 THEN 'Patch'
   WHEN 3 THEN 'Release'
END 'Software Type'
FROM ca_discovered_software s, ca_discovered_hardware h, ca_software_def d
WHERE d.sw_def_uuid = s.sw_def_uuid AND s.asset_source_uuid = h.dis_hw_uuid
order by 1,4,2,3

 

Then we could use it to filter on one machine and Software Type :

Example :
Query for Software Inventory 'CA Provided', 'Custom', 'CA Intellisig' for machine <Machine_Name>

SELECT h.host_name 'Computer Name', d.name 'Software Name',
d.sw_version_label 'Software Version',
CASE d.source_type_id
   WHEN 1 THEN 'CA'
   WHEN 2 THEN 'Custom'
   WHEN 3 THEN 'Heuristic'
   WHEN 5 THEN 'CA Intellisig'
END 'Detection Method',
CASE d.software_type_id
   WHEN 1 THEN 'Patch'
   WHEN 3 THEN 'Release'
END 'Software Type'
FROM ca_discovered_software s, ca_discovered_hardware h, ca_software_def d
WHERE d.sw_def_uuid = s.sw_def_uuid AND s.asset_source_uuid = h.dis_hw_uuid
and h.host_name='<Machine_Name>' and d.source_type_id in (1,2,5)
order by 1,4,2,3

Example :
Query for Software Inventory 'Heuristic' for machine <Machine_Name>

SELECT h.host_name 'Computer Name', d.name 'Software Name',
d.sw_version_label 'Software Version',
CASE d.source_type_id
   WHEN 1 THEN 'CA'
   WHEN 2 THEN 'Custom'
   WHEN 3 THEN 'Heuristic'
   WHEN 5 THEN 'CA Intellisig'
END 'Detection Method',
CASE d.software_type_id
   WHEN 1 THEN 'Patch'
   WHEN 3 THEN 'Release'
END 'Software Type'
FROM ca_discovered_software s, ca_discovered_hardware h, ca_software_def d
WHERE d.sw_def_uuid = s.sw_def_uuid AND s.asset_source_uuid = h.dis_hw_uuid
and h.host_name='<Machine_Name>' and d.source_type_id in (3)
order by 1,4,2,3