My old employer (local goverment) got a message from Microsoft about an audit. Since they have an education and an enterprise agreement, they need to get a list of all computers that can go under education-agreement (since that is the cheapest ) and everything else that goes under the enterprise agreement. When I was working there, I would have used which IP subnet the machine is from to sort the machines, but since they now have a lot of machines only reporting their IP from a shared wireless subnet, that’s harder. Instead, I figured out that we could use top console user to get a list, in addition to the AD Site.

Created a user collection with all users that are considered educational (department groups or other access groups). The collection got the name CEN00XXX.

 

Ran the following SQL-query to get a list of systems that had been online for the last 2 months.

SELECT sys.Netbios_Name0,
um.TopConsoleUser0,
[Last_Logon_Timestamp0],
(SELECT CASE WHEN TopConsoleUser0 IS NULL THEN ‘False’ ELSE ‘True’ END AS IsSkole FROM _RES_COLL_CEN00XXX WHERE SMSID = um.TopConsoleUser0 ) AS userIsEdu,
CASE WHEN [AD_Site_Name0] LIKE ‘EducationSite‘ THEN ‘True’ ELSE ‘False’ END AS SiteIsEdu,

FROM v_R_System sys
LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP um
ON um.ResourceID = sys.ResourceID
WHERE Last_Logon_Timestamp0 IS NOT NULL
AND Last_Logon_Timestamp0 > DATEADD(month, -2, GETDATE())
ORDER BY Last_Logon_Timestamp0