Answer to FB post https://www.facebook.com/groups/ConfigMgr2012/permalink/4245192462170207
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
Recent Comments