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:
dbo.v_R_System.Netbios_Name0 AS [Workstation Name],
dbo.v_R_System.Operating_System_Name_and0 AS [OS Version],
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.