Patch Group Compliance - For Automated Reports

Version 4
    Share This:

    Select "Patch Group", [COMPLIANT], [NON-COMPLIANT],[NON-CHECKEDIN]
    FROM (
    
    SELECT substring(ic.url,55,30) AS "Patch Group" , 'COMPLIANT' "STATUS" , a.[id] as "ID"
    FROM inv_compliance ic, inv_machine a
    WHERE UPPER(ic.compliance_level) = 'COMPLIANT'
    AND UPPER(ic.type) = UPPER('Machine')
    AND UPPER(ic.policy_agent) = UPPER('MRBA Subscription Manager')
    AND ic.machine_id = a.id
    AND (ic.url like '%/PatchManagement/PatchGroups/%')
    AND (ic.policy_name like 'ou=workstations,dc=company,dc=com')
    
    
    UNION ALL
    
    
    SELECT substring(ic.url,55,30) AS "Patch Group" , 'NON-COMPLIANT' "STATUS" , a.[id] as "ID"
    FROM inv_compliance ic, inv_machine a
    WHERE UPPER(ic.compliance_level) <> 'COMPLIANT'
    AND UPPER(ic.type) = UPPER('Machine')
    AND UPPER(ic.policy_agent) = UPPER('MRBA Subscription Manager')
    AND ic.machine_id = a.id
    AND (ic.url like '%/PatchManagement/PatchGroups/%')
    AND (ic.policy_name like 'ou=workstations,dc=company,dc=com')
    
    
    UNION ALL
    
    
    SELECT substring(isp.url,55,30) AS "Patch Group",  'NON-CHECKEDIN' "STATUS", a.[id] as "ID"
    from inv_machine a ,ldapsync_target_membership tme, inv_subscription_policy isp, ldapsync_targets_machines tma
    where isp.url like '%/PatchManagement/PatchGroups/%'
    and a.id = tma.machine_id
    and tma.target_id=tme.target_id
    and tme.memberof_target_id=isp.target_id
    and not exists (select 1 from inv_machinecompliance mc where mc.machine_id =a.id and mc.url=isp.url)
    ) p
    PIVOT
    (COUNT ("ID")
    FOR "STATUS" IN ([COMPLIANT], [NON-COMPLIANT],[NON-CHECKEDIN]))AS pvt
    Order by "Patch Group"
    
    
    
    

     

     

    Note: You may need to modify substring(ic.url,55,30) according to your patch group names.

    (55 - starting character on channel url, 30 length of characters captured after the 55th character in channel url)

     

    ic.url - Patch Group Url

    ic.policy_name - Target Details(OU) which are targetted for the Patch Group deployment through Policies

    ic.policy_name