IT Management Suite

 View Only
  • 1.  Getting list of computers policy assigned to

    Posted Jul 28, 2015 12:06 PM

    Hi,

    In the SMP you can look at a policy and very quickly display a list of all current computers (or Users) the policy is currently applicable to. I'm trying to write a report that basically does the same thing. i.e. Tell me for this policy, which computers it currently applies to and the easiest solution here would be to use the SQL query which provides this in the policy display.  Why? Well I need to check all the agent plug-in policies to see which computers are still in the policy list and therefore establish which agent installs may have failed.

    Anyone got any clues/pointers

     

    Cheers

     

    Steve

     

     



  • 2.  RE: Getting list of computers policy assigned to
    Best Answer

    Posted Jul 28, 2015 01:29 PM

    I created a custom report for this purpose called "List of Computers in Policy X" and added a parameter where you can select the name of the policy.   Here is the SQL for it:

     

    --Computer list for Policy X

    SELECT DISTINCT

       PolicyItem.Name [Policy],

       vc.Guid [_ItemGuid],

       vc.[Name][Computer Name],

       vc.[OS Name],

       vc.Domain,

       vc.[User] [Primary User],

       ( SELECT TOP 1 CC.[StartTime] FROM [evt_ns_client_config_request] CC

          WHERE CC.[ResourceGuid] = vc.[Guid]

          ORDER BY CC.[StartTime] DESC

       ) AS [Last Config Request],

       VC.[IP Address],

       sn1.[Manufacturer],

       sn1.[Model],

       ou.[Distinguished Name] as 'AD OU'

    FROM vComputer vc

       JOIN vPolicyAppliesToResource on vPolicyAppliesToResource.ResourceGuid = vc.guid

       JOIN Item PolicyItem on PolicyItem.Guid = vPolicyAppliesToResource.PolicyGuid

       LEFT JOIN vHWComputerSystem sn1 ON sn1.[_ResourceGuid] = vc.Guid

       LEFT JOIN [Inv_OU_Membership] ou on ou.[_ResourceGuid] = vc.[Guid] and ou.IsDirectMember = 1

     

    WHERE vc.[IsManaged] = 1

       --AND PolicyItem.Name like '%PolicyName%'

       AND PolicyItem.Name like 'Software Management Solution Plug-in Install'

    ORDER BY PolicyItem.Name, vc.Name



  • 3.  RE: Getting list of computers policy assigned to

    Posted Jul 28, 2015 05:50 PM

    Thanks - thats just what I need - I'd missed the VpolicyAppliestoResource View when going through and that just what I need.