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

Read More