0 /ConfigMgr_RDC/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602} None f8903870-0a80-492e-be96-9a24da6aea99 DataSource1 /****** Script for SelectTopNRows command from SSMS ******/ SELECT s.ResourceID ,s.Name0 ,(select top 1 op.Caption0 from v_GS_OPERATING_SYSTEM op where op.ResourceID=s.ResourceID ) as 'Operating System' ,s.Last_Logon_Timestamp0 ,s.User_Name0 ,s.whenChanged0 'Last AD update' ,case when s.Client0 = 1 then 'Installed' Else ' ' end as Client0 ,case when s.Active0 = 1 then 'Active' Else ' ' end as Active0 ,ah.LastMessageTime ,ah.EngineVersion ,ah.AntivirusSignatureVersion ,ah.AntivirusSignatureUpdateDateTime ,Case when ah.AntivirusSignatureVersion = '' then 'none' when s.Client0 <> '1' then 'none' when (datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) >= 100 then cast ((datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) as varchar) when (datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) >= 10 then cast ((datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) as varchar) when (datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) >= 0 then cast ((datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) as varchar) else cast ('none' as varchar) end as 'Days Out' ,Case when ah.AntivirusSignatureVersion = '' then 'none' when s.Client0 <> '1' then 'none' when (datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) > 7 then 'More than 7 days' when (datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) < 3 then 'Compliant' when (datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) <= 7 then 'More than 3 days' else cast ('none' as varchar) end as 'Days Out Pie' ,Case when ah.AntivirusSignatureVersion = '' then 'none' when s.Client0 <> '1' then 'none' when (datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) >= 100 then cast ((datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) as varchar) when (datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) >= 10 then '0'+ cast ((datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) as varchar) when (datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) >= 0 then '00'+ cast ((datediff(dd, AH.AntivirusSignatureUpdateDateTime,GetDate())) as varchar) else cast ('none' as varchar) end as 'Days Out Sort' --,* FROM v_R_System s left join SCCM_Ext.vex_EP_AntimalwareHealthStatus AH on s.ResourceID = ah.ResourceID --Where s.ResourceID > 2000000000 --where s.Client0 <> 0 --and s.Name0 = 'zavscmp01' order by 'Days Out sort', ah.AntivirusSignatureUpdateDateTime 15 true Name0 System.String ResourceID System.Int32 Operating System System.String Client0 System.String Last_Logon_Timestamp0 System.DateTime Active0 System.String User_Name0 System.String LastMessageTime System.DateTime Last AD update System.DateTime EngineVersion System.String AntivirusSignatureVersion System.String AntivirusSignatureUpdateDateTime System.DateTime Days Out System.String Days Out Sort System.String Days Out Pie System.String 25mm 36.66091mm 62.67245mm 16.79792mm 11.77083mm 26.85208mm 29.7625mm 25mm 32.91826mm 25mm 30.29167mm 33.73125mm 25mm 6mm true true SCCM ID #69be28 2pt 2pt 2pt 2pt true true System #69be28 2pt 2pt 2pt 2pt true true Operating System #69be28 2pt 2pt 2pt 2pt true true SCCM Client #69be28 2pt 2pt 2pt 2pt 2 true true Last Logon #69be28 2pt 2pt 2pt 2pt true true User Name #69be28 2pt 2pt 2pt 2pt true true AD update #69be28 2pt 2pt 2pt 2pt true true FEP Msg upate #69be28 2pt 2pt 2pt 2pt true true Engine Ver #69be28 2pt 2pt 2pt 2pt true true Signature Ver #69be28 2pt 2pt 2pt 2pt true true Signature Date #69be28 2pt 2pt 2pt 2pt true true Days Out #69be28 2pt 2pt 2pt 2pt 6mm true true =Fields!ResourceID.Value 2pt 2pt 2pt 2pt true true =Fields!Name0.Value 2pt 2pt 2pt 2pt true =Fields!Operating_System.Value 2pt 2pt 2pt 2pt true true =Fields!Client0.Value 2pt 2pt 2pt 2pt true true =Fields!Active0.Value 2pt 2pt 2pt 2pt true true =Fields!Last_Logon_Timestamp0.Value 2pt 2pt 2pt 2pt true true =Fields!User_Name0.Value 2pt 2pt 2pt 2pt true true =Fields!Last_AD_update.Value 2pt 2pt 2pt 2pt true true =Fields!LastMessageTime.Value 2pt 2pt 2pt 2pt true true =Fields!EngineVersion.Value 2pt 2pt 2pt 2pt true true =Fields!AntivirusSignatureVersion.Value 2pt 2pt 2pt 2pt true true =Fields!AntivirusSignatureUpdateDateTime.Value 2pt 2pt 2pt 2pt true true =Fields!Days_Out.Value 2pt 2pt 2pt 2pt After AntimalwareHealthStatus 135.96408mm 4.91248mm 12mm 381.45787mm 57.91826mm 25mm 6mm true true Number of systems #69be28 2pt 2pt 2pt 2pt true true =Count(Fields!Name0.Value) #69be28 2pt 2pt 2pt 2pt 6mm true true =Count(Fields!Name0.Value) 2pt 2pt 2pt 2pt true true 2pt 2pt 2pt 2pt 25mm true true Days Out #69be28 2pt 2pt 2pt 2pt =Fields!Days_Out_Sort.Value =Fields!Days_Out_Sort.Value 25mm true true =Fields!Days_Out.Value #69be28 2pt 2pt 2pt 2pt AntimalwareHealthStatus 23.01875mm 194.98542mm 12mm 107.91826mm 1 =Fields!Days_Out_Pie.Value =Fields!Days_Out_Pie.Value =Fields!Days_Out_Pie.Value =Count(Fields!Name0.Value) Axis Title False 0.5 NaN NaN NaN Axis Title False 0.5 NaN Opposite NaN NaN Axis Title 0.5 NaN NaN NaN Axis Title 0.5 NaN Opposite NaN NaN Black Black #4c68a2 #b5dddf #7292cc #89bcc5 #c4d5f3 #c6c595 #87acf3 #7b9291 #a8c4ec #a49ed4 Emboss No Data Available AntimalwareHealthStatus 23.01875mm 4.91248mm 106.99681mm 190.07294mm 2 2pt #69be28 TopBottom true true SCEP Definition Update Outstanding as per days Textbox13 3.35986mm 4.91248mm 11.64167mm 381.45787mm 3 #69be28 2pt 2pt 2pt 2pt 151.97598mm 388.48076mm 297mm 210mm 20mm 20mm 20mm 20mm 0.13cm