How to query software policy violators in SQL

Document ID : KB000014553
Last Modified Date : 02/03/2018
Show Technical Document Details
Question:

How do you query the list of Software Policy violators in SQL for a specific policy?

Answer:

Query for list of software delivery groups:
select * from usd_cmp_grp

Query for links between computers and software delivery groups:
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

Note: The software policy name is stored differently in the database than in DSM Explorer--
DSM Explorer --> Windows Computers -- Software Delivery Test
      Database --> Windows Computers -- Software Delivery Test [4/16/2017 12:35:09 PM]

This is why the above query uses:
where name like 'PolicyName%'

Instead of:
where name='PolicyName'

If you try to reference the policy name directly, you won't get any results.