﻿Select top 1000
		 S.ResourceID 'Resource ID'
		,S.Name0 'System Name'
		,S.operatingSystem0 'Operating System'
		,(
			Select top 1 
			Case
				When E.SignatureUpTo1DayOld = 1 then 'Up to 1 day'
				When E.SignatureUpTo3DaysOld = 1 then 'Up to 2 days'
				When E.SignatureUpTo7DaysOld = 1 then 'Up to 7 days'
				When E.SignatureOlderThan7Days = 1 then 'Older than 7 Days'
			Else 'No Signature' end
			from v_EndpointProtectionStatus E
			where s.ResourceID = e.resourceID
		 ) 'SCEP Updates'
		,S.whenChanged0 'Last AD change'
		,S.Last_Logon_Timestamp0 'Last Logon'
from V_R_system S
Where operatingSystem0 <> ''
  and s.Client0 = 1
  and DateDiff(dd,S.whenChanged0, GetDate ()) <= 30