SQL query to get violators of a Software Based policy

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

SQL Query to list all violators of a specific Software Based Policy.

Environment

Client Automation 14.x

Resolution

Query to list all Software Based policies:

select * from usd_cmp_grp

Query for links between computers and software based policies:

select * from usd_link_grp_cmp

Query that returns list of computers that violate a specific software policy:

select host_name 
from ca_discovered_hardware dh inner join (select comp from usd_link_grp_cmp 
where grp in (select objectid from usd_cmp_grp where name like 'Windows Computers -- Software Delivery Test%')) as t1 on t1.comp=dh.dis_hw_uuid

Additional Information

The software policy name is stored differently in the database than it is in DSM Explorer, as example:

- in DSM Explorer, this query is called "Policy_ES_to_DM":

- in Database, this same query is called "Policy_ES_to_DM [4/21/2023 2:54:43 PM]"

This is why the above query uses:

where name like 'PolicyName%'

Instead of:

where name='PolicyName'

Trying to reference the policy name directly won't get any results.