How do you query the list of Software Policy violators in SQL for a specific policy?
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%'
If you try to reference the policy name directly, you won't get any results.