SQL Query : All Active PCs

This query returns all active PCs based on the fact the device has received policy in the last 31 days (or configured as you wish).  You can also configure the collection ID – in this case it is querying all machines in the Global Clients custom collection.

SELECT dbo.v_R_System.Name0,
    dbo.v_R_System.User_Name0,
    dbo.v_GS_OPERATING_SYSTEM.Caption0,
    dbo.v_CH_ClientSummary.LastPolicyRequest
 FROM dbo.v_R_System
    INNER JOIN dbo.v_CH_ClientSummary ON dbo.v_R_System.ResourceID = dbo.v_CH_ClientSummary.ResourceID
    INNER JOIN dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
    INNER JOIN dbo.v_FullCollectionMembership ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_FullCollectionMembership.ResourceID
    INNER JOIN dbo.v_GS_PC_BIOS ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_PC_BIOS.ResourceID
 WHERE (dbo.v_FullCollectionMembership.CollectionID = 'XX100038')
    AND (DATEDIFF(day, dbo.v_CH_ClientSummary.LastPolicyRequest, GETDATE()) < 31)

Sample Output

 

Related posts

Leave a Comment