ConfigMgr Report: Workstations which have reported in within a specified number of days

  • Article

A customer of mine asked me to help them identify all the remaining, in their case Windows XP workstations that have reported in to ConfigMgr within the past 14 days. They were in the middle of a migration from Windows XP to Windows 7 and needed a quick way to identify the "straggler" systems so they could be targeted for migration. The report below is what I came up with:

SELECT
dbo.v_R_System.Netbios_Name0 AS [Workstation Name],
dbo.v_R_System.User_Name0,
dbo.v_R_System.Operating_System_Name_and0 AS [OS Version],
dbo.v_R_System.AD_Site_Name0,
dbo.v_GS_WORKSTATION_STATUS.LastHWScan

FROM dbo.v_R_System INNER JOIN
dbo.v_GS_WORKSTATION_STATUS ON dbo.v_R_System.ResourceID = dbo.v_GS_WORKSTATION_STATUS.ResourceID

WHERE dbo.v_GS_WORKSTATION_STATUS.LastHWScan > (SELECT DATEADD(day,-14, (SELECT MAX(dbo.v_GS_WORKSTATION_STATUS.LastHWScan) FROM dbo.v_GS_WORKSTATION_STATUS)) AS "-14 Days")
AND (dbo.v_R_System.Operating_System_Name_and0 LIKE '%5.1%')

ORDER BY dbo.v_R_System.AD_Site_Name0

 

The report will look similar to this:

 

 This report can easily be tailored to modify the number of days by changing both (-14)'s in the WHERE line to the number of days you need.
Also, you can modify the report syntax for many other items to meet your requirements.