How to query software policy violators in SQL


Article ID: 14553


Updated On:


CA Automation Suite for Data Centers - Configuration Automation CA Client Automation - Asset Management CA Client Automation - IT Client Manager CA Client Automation CA Client Automation - Remote Control CA Client Automation - Asset Intelligence CA Client Automation - Desktop Migration Manager CA Client Automation - Patch Manager


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


Release: UASIT.99000-12.9-Asset Intelligence


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.