How to query software policy violators in SQL

Products

• 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

Issue/Introduction

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

Environment

Release: UASIT.99000-12.9-Asset Intelligence
Component:

Resolution

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.