Patch Level and AV status report

[Argon0]

Hi,

Fairly new to LanSweeper, trying to get a report to show machines which are not up to date with MS patches and their AV status (AV software, installed/not installed, uptodate/not up to date).

So started with this report https://www.lansweeper.com/forum/yaf_postsm50430_Ransomware--MS17-010-Windows-computers-that-are-potentialy-vulnerable.aspx#post50430, and the 'Workstation: All workstations with Anti-virus software' built in report, to try to combine the two....

And then got lost...

Essentially I'd like to add three columns to the 'WannaCry' report: Software (AV software), Version, Enabled, Uptodate...

IDEALLY I'd like this in graphical form as well - maybe a couple of pie charts, one showing all WSs, split into those vulnerable and those not, then a separate one for 'vulnerable WSs only' showing those with up to date AV, out of date AV, no AV, and AV disabled.

Then the next idea would be to take this report and regularly populate it with new KBs/Fixes from MS, and ask LS to show me how many machines didn't have:
latest patch
Patches released more than 7 days ago
Patches released more than 30 days ago
(etc... maybe be able to select a time/cut off for patch release age).

Much of this may already be available, but I can't seem to find it ATM.

Thanks

Argon0

[Argon0]

SO... after bit of playing around I arrived at this variant, which includes the AV status, etc... BUT only shows those machines which have AV installed (it appears 10 of them may not have AV installed at all :-0 )...

So how do I list the machines with NO AV.

Select Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then 'Scanning Error: ' +
tsysasseterrortypes.ErrorMsg Else '' End As ScanningErrors,
Case When tblAssets.Lastseen Is Null Then 'Unknown' Else 'Vulnerable'
End As IsVulnerable,
Case When tsysOS.OSname = 'Win XP' Or tsysOS.OSname = 'Win 2003' Or
tsysOS.OSname = 'Win 2003 R2' Or tsysOS.OSname = 'Win 8' Then 'KB4012598'
When tsysOS.OSname = 'Win Vista' Or
tsysOS.OSname = 'Win 2008' Then 'KB4012598 / KB4018466'
When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname =
'Win 2008 R2' Then
'KB4012212 / KB4012215 / KB4015549 / KB4019264 / KB4019265 / KB4022719 / KB4022168 / KB4025341 / KB4025340 / KB4034664 / KB4034670' When tsysOS.OSname = 'Win 2012' Then 'KB4012214 / KB4012217 / KB4015551 / KB4019216 / KB4019218 / KB4022724 / KB4022721 / KB4025331 / KB4025332 / KB4034665 / KB4034659' When tsysOS.OSname = 'Win 8.1' Or tsysOS.OSname = 'Win 2012 R2' Then 'KB4012213 / KB4012216 / KB4015550 / KB4019215 / KB4019217 / KB4022726 / KB4022720 / KB4025336 / KB4025335 / KB4034681 / KB4034663' When tblOperatingsystem.Version Like '%10240%' Then 'KB4012606 / KB4015221 / KB4019474' When tblOperatingsystem.Version Like '%10586%' Then 'KB4013198 / KB4015219 / KB4019473 / KB4022714 / KB4032693 / KB4025344 / KB4034660' When tblOperatingsystem.Version Like '%14393%' Or tsysOS.OSname = 'Win 2016' Then 'KB4015438 / KB4015217 / KB4019472 / KB4023680 / KB4022715 / KB4022723 / KB4025339 / KB4025334 / KB4034658' When tsysOS.OScode Like '10.0%' And tsysOS.OScode Not In (Select Top 1000000 tsysOS.OScode From tsysOS Where tsysOS.OScode Like '10.0%') Then 'KB4012606 / KB4015221 / KB4019474 / KB4015438 / KB4015217 / KB4019472 / KB4023680 / KB4022715 / KB4022723 / KB4025339 / KB4025334 / KB4034658' Else Null End As [Install one of these updates],
Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
Case
When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) >
3 Then
'Windows update information may not be up to date. We recommend rescanning this machine.' Else '' End As Comment,
tblAntivirus.ProductState,
tblAntivirus.LastChanged As LastChanged2,
tblAntivirus.LastChanged As LastChanged1,
tblAntivirus.productUpToDate,
tblAntivirus.onAccessScanningEnabled,
tblAntivirus.LastChanged,
tblAntivirus.DisplayName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
TsysLastscan.Lasttime As QuickFixLastScanned
From TsysWaittime
Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Inner Join tblAssets On tblAssets.AssetID = TsysLastscan.AssetID
Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned
On tblAssets.AssetID = QuickFixLastScanned.ID
Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Inner Join tblAssets On tblAssets.AssetID = tblErrors.AssetID
Group By tblAssets.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Where
tblAssets.AssetID Not In (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4012216', 'KB4012215',
'KB4012217', 'KB4012212', 'KB4012213', 'KB4012598', 'KB4012214',
'KB4012606', 'KB4013198', 'KB4015551', 'KB4019216', 'KB4015550',
'KB4019215', 'KB4013429', 'KB4019472', 'KB4015217', 'KB4015438',
'KB4016635', 'KB4019264', 'KB4015549', 'KB4015221', 'KB4019474',
'KB4015219', 'KB4019473', 'KB4018466', 'KB4019217', 'KB4019265',
'KB4019218', 'KB4022719', 'KB4022724', 'KB4022726', 'KB4023680',
'KB4022715', 'KB4022714', 'KB4022720', 'KB4032693', 'KB4022723',
'KB4022168', 'KB4022721', 'KB4025336', 'KB4025344', 'KB4025339',
'KB4025341', 'KB4025331', 'KB4025335', 'KB4025334', 'KB4025340',
'KB4025332', 'KB4034681', 'KB4034660', 'KB4034658', 'KB4034664',
'KB4034665', 'KB4034663', 'KB4034670', 'KB4034659')) And
tsysOS.OSname != 'Win 2000 S' And tsysAssetTypes.AssetTypename Like 'Windows%'
And tsysOS.OScode Not Like '10.0.15%' And tsysOS.OScode Not Like '10.0.16%'
Order By tblAssets.Domain,
tblAssets.AssetName