Answer to FB post

select Distinct R.name0, R.User_Name0, R.Resource_Domain_OR_Workgr0, OS.Caption0, case when (os.caption0 = 'Microsoft Windows 10 Enterprise') then 'Windows 10' else os.caption0 end as 'OS', max(MUS.LastUsage) as 'Last Usage', DateDiff(dd,max(mus.lastusage),getdate()) as 'Days Last Used', CASE when (GSSCUM.TopConsoleUser0 is NULL or GSSCUM.TopConsoleUser0 = '-1') then 'Unknown' Else GSSCUM.TopConsoleUser0 End 'Top Console User' , U.Full_User_Name0 AS 'Full User Name', --U.Department0 AS 'Department', --U.Title0 AS 'Title', U.mail0 AS 'Email Address', --U.physicalDeliveryOfficeNam0 AS 'Office', '' from v_R_SYSTEM R join v_MonthlyUsageSummary MUS on R.ResourceID = MUS.ResourceID join v_MeteredFiles MF ON MUS.FileID = MF.MeteredFileID left join dbo.v_GS_OPERATING_SYSTEM OS on R.ResourceID = OS.ResourceID left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP GSSCUM on GSSCUM.ResourceID = R.ResourceID left JOIN v_R_User U ON U.Unique_User_Name0 = GSSCUM.TopConsoleUser0 left JOIN v_FullCollectionMembership FCM ON R.ResourceID = FCM.ResourceID WHERE FCM.CollectionID = 'xxx0008c' AND MF.RuleID = 16777418 and DateDiff(day, MUS.LastUsage, GetDate()) > 90 and R.ResourceID not in ( Select FCM.ResourceID from dbo.v_FullCollectionMembership FCM where FCM.CollectionID = 'xxx0008f') Group by R.Name0, R.User_Name0, R.Resource_Domain_OR_Workgr0, OS.Caption0, GSSCUM.TopConsoleUser0, U.Full_User_Name0, --U.Department0, --U.Title0, U.mail0--, --U.physicalDeliveryOfficeNam0, -- FCM.CollectionID