0/ConfigMgr_RDC/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}Nonef8903870-0a80-492e-be96-9a24da6aea99DataSource1/****** 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.AntivirusSignatureUpdateDateTime15trueName0System.StringResourceIDSystem.Int32Operating SystemSystem.StringClient0System.StringLast_Logon_Timestamp0System.DateTimeActive0System.StringUser_Name0System.StringLastMessageTimeSystem.DateTimeLast AD updateSystem.DateTimeEngineVersionSystem.StringAntivirusSignatureVersionSystem.StringAntivirusSignatureUpdateDateTimeSystem.DateTimeDays OutSystem.StringDays Out SortSystem.StringDays Out PieSystem.String25mm36.66091mm62.67245mm16.79792mm11.77083mm26.85208mm29.7625mm25mm32.91826mm25mm30.29167mm33.73125mm25mm6mmtruetrueSCCM IDTextbox34#69be282pt2pt2pt2pttruetrueSystemTextbox8#69be282pt2pt2pt2pttruetrueOperating SystemTextbox11#69be282pt2pt2pt2pttruetrueSCCM ClientTextbox14#69be282pt2pt2pt2pt2truetrueLast LogonTextbox16#69be282pt2pt2pt2pttruetrueUser NameTextbox20#69be282pt2pt2pt2pttruetrueAD updateTextbox38#69be282pt2pt2pt2pttruetrueFEP Msg upateTextbox22#69be282pt2pt2pt2pttruetrueEngine VerTextbox26#69be282pt2pt2pt2pttruetrueSignature VerTextbox28#69be282pt2pt2pt2pttruetrueSignature Date Textbox30#69be282pt2pt2pt2pttruetrueDays OutTextbox32#69be282pt2pt2pt2pt6mmtruetrue=Fields!ResourceID.ValueResourceID2pt2pt2pt2pttruetrue=Fields!Name0.ValueName012pt2pt2pt2pttrue=Fields!Operating_System.ValueOperating_System2pt2pt2pt2pttruetrue=Fields!Client0.ValueClient02pt2pt2pt2pttruetrue=Fields!Active0.ValueActive02pt2pt2pt2pttruetrue=Fields!Last_Logon_Timestamp0.ValueLast_Logon_Timestamp02pt2pt2pt2pttruetrue=Fields!User_Name0.ValueUser_Name02pt2pt2pt2pttruetrue=Fields!Last_AD_update.ValueLast_AD_update2pt2pt2pt2pttruetrue=Fields!LastMessageTime.ValueLastMessageTime2pt2pt2pt2pttruetrue=Fields!EngineVersion.ValueEngineVersion2pt2pt2pt2pttruetrue=Fields!AntivirusSignatureVersion.ValueAntivirusSignatureVersion2pt2pt2pt2pttruetrue=Fields!AntivirusSignatureUpdateDateTime.ValueAntivirusSignatureUpdateDateTime2pt2pt2pt2pttruetrue=Fields!Days_Out.ValueDays_Out12pt2pt2pt2ptAfterAntimalwareHealthStatus135.96408mm4.91248mm12mm381.45787mm57.91826mm25mm6mmtruetrueNumber of systemsTextbox4#69be282pt2pt2pt2pttruetrue=Count(Fields!Name0.Value)Textbox40#69be282pt2pt2pt2pt6mmtruetrue=Count(Fields!Name0.Value)Name02pt2pt2pt2pttruetrueTextbox422pt2pt2pt2pt25mmtruetrueDays OutTextbox43#69be282pt2pt2pt2pt=Fields!Days_Out_Sort.Value=Fields!Days_Out_Sort.Value25mmtruetrue=Fields!Days_Out.ValueDays_Out#69be282pt2pt2pt2ptAntimalwareHealthStatus23.01875mm194.98542mm12mm107.91826mm1=Fields!Days_Out_Pie.Value=Fields!Days_Out_Pie.Value=Count(Fields!Name0.Value)truetrueOutputShapeExplodedPiePrimaryPrimaryBlack0pt
AntimalwareHealthStatus23.01875mm4.91248mm106.99681mm190.07294mm22pt#69be28TopBottomtruetrueSCEP Definition Update Outstanding as per daysTextbox133.35986mm4.91248mm11.64167mm381.45787mm3#69be282pt2pt2pt2pt151.97598mm388.48076mm297mm210mm20mm20mm20mm20mm0.13cmMmhttp://zavscmp01/ReportServer109e247d-76d6-44e4-bb5f-1621ff9946c2