Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Case
When (sha2patch.AssetID Is Not Null) And (SSUMarApr.AssetID Is Not Null) And
(SSUSep.AssetID Is Not Null) And (MonthlyRollups.AssetID Is Not Null) Then
'Up to date'
Else 'Out of date'
End As [Patch status],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lasttried,
Case
When sha2patch.AssetID Is Null Then
'SHA-2 code signing support update missing'
When SSUMarApr.AssetID Is Null Then
'Servicing stack update from March(W7/W2008R2) or April(W2008) missing'
When SSUSep.AssetID Is Null Then
'Servicing stack update from September missing'
When MonthlyRollups.AssetID Is Null Then 'Monthly rollup patch missing'
Else ''
End As [Missing Patches],
Convert(nvarchar,DateDiff(DAY, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
Case
When (sha2patch.AssetID Is Not Null) And (SSUMarApr.AssetID Is Not Null) And
(SSUSep.AssetID Is Not Null) And (MonthlyRollups.AssetID Is Not Null) Then
'#d4f4be'
Else '#ffadad'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB4474419') As sha2patch On
sha2patch.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4493730', 'KB4490628')) As
SSUMarApr On SSUMarApr.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4516655', 'KB4517134')) As
SSUSep On SSUSep.AssetID = tblAssets.AssetID
Left Join (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Left Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4519976', 'KB4519972',
'KB4525235', 'KB4525251', 'KB4530734', 'KB4520002', 'KB4520015',
'KB4525234', 'KB4525244', 'KB4530695')) As MonthlyRollups On
MonthlyRollups.AssetID = tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select Distinct TsysLastscan.AssetID As ID,
TsysLastscan.Lasttime As QuickFixLastScanned
From TsysWaittime
Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
tblAssets.AssetID = QuickFixLastScanned.ID
Left Join (Select Distinct tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Where (tsysOS.OSname Like 'Win 7%' Or tsysOS.OSname Like 'Win 2008 R2'
Or tsysOS.OSname Like 'Win 2008') And tblAssetCustom.State = 1 And
tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.AssetName
Explore Lansweeper for free.
No credit card required.