SQL Query : Computer name with user & last logged on date etc.

This query links a number of tables and views to return extensive inventory information about your clients.

 
 SELECT dbo.v_R_System.Name0, dbo.v_R_System.User_Name0, dbo.v_R_System.AD_Site_Name0, dbo.v_R_System.Client_Version0, dbo.v_GS_OPERATING_SYSTEM.Caption0, dbo.v_CH_ClientSummary.LastOnline, dbo.v_CH_ClientSummary.LastMPServerName, dbo.v_CH_ClientSummary.LastDDR, dbo.v_CH_ClientSummary.LastHW, dbo.v_CH_ClientSummary.LastSW, dbo.v_CH_ClientSummary.LastStatusMessage, dbo.v_CH_ClientSummary.LastPolicyRequest, dbo.v_CH_ClientSummary.IsActiveDDR, dbo.v_CH_ClientSummary.IsActiveSW, dbo.v_CH_ClientSummary.IsActivePolicyRequest, dbo.v_CH_ClientSummary.IsActiveStatusMessages, dbo.v_CH_ClientSummary.LastEvaluationHealthy, dbo.v_CH_ClientSummary.ClientRemediationSuccess, dbo.v_CH_ClientSummary.LastActiveTime, dbo.v_CH_ClientSummary.ClientActiveStatus, dbo.v_CH_ClientSummary.ClientStateDescription, dbo.v_CH_ClientSummary.IsActiveHW, dbo.v_FullCollectionMembership.CollectionID, dbo.v_GS_PC_BIOS.SerialNumber0
 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 v_FullCollectionMembership.CollectionID ='GS200038' and DATEDIFF(day,dbo.v_CH_ClientSummary.LastPolicyRequest,GetDate())<31
 ORDER BY v_R_System.Name0

Sample Output

Related posts

Leave a Comment