![](https://community.lansweeper.com/html/@1E90B002263FF047A8698DA3E35D576F/assets/img_tile-default.png)
Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State,
Case
When tblAssets.AssetID = One.AssetID Then 'Installed'
Else 'Not Installed'
End As [Patch August],
Case
When tblAssets.AssetID = Three.AssetID Then 'Installed'
Else 'Not Installed'
End As [Patch September],
Case
When tblAssets.AssetID = Two.AssetID Then 'Installed'
Else 'Not Installed'
End As [Patch October],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
Case
When tsysOS.OScode Like '10.0.10240%' Then '1507'
When tsysOS.OScode Like '10.0.10586%' Then '1511'
When tsysOS.OScode Like '10.0.14393%' Then '1607'
When tsysOS.OScode Like '10.0.15063%' Then '1703'
When tsysOS.OScode Like '10.0.16299%' Then '1709'
When tsysOS.OScode Like '10.0.17134%' Then '1803'
When tsysOS.OScode Like '10.0.17763%' Then '1809'
When tsysOS.OScode Like '10.0.18362%' Then '1903'
End As Version,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
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,
Case
When tblAssets.AssetID = Two.AssetID 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 Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Inner Join tblQuickFixEngineeringHist On
tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4512491', 'KB4512476',
'KB4512486', 'KB4512506', 'KB4512482', 'KB4512518', 'KB4512489',
'KB4512488', 'KB4512497', 'KB4093109', 'KB4512517', 'KB4512507',
'KB4512516', 'KB4512501', 'KB4511553', 'KB4512508')) As One On
tblAssets.AssetID = One.AssetID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Inner Join tblQuickFixEngineeringHist On
tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4520009', 'KB4520002',
'KB4520003', 'KB4519976', 'KB4519985', 'KB4520007', 'KB4519990',
'KB4520005', 'KB4520011', 'KB4093109', 'KB4519998', 'KB4520010',
'KB4520004', 'KB4520008', 'KB4519338', 'KB4517389')) As Two On
tblAssets.AssetID = Two.AssetID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Inner Join tblQuickFixEngineeringHist On
tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4516051', 'KB4516026',
'KB4516033', 'KB4516065', 'KB4516062', 'KB4516055', 'KB4516064',
'KB4516067', 'KB4516070', 'KB4516044', 'KB4516068', 'KB4516066',
'KB4516058', 'KB4512578', 'KB4515384')) As Three On tblAssets.AssetID =
Three.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select Distinct Top 1000000 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 Top 1000000 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 <> 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And
tsysOS.OSname Not Like '%2003%' And (Not tsysOS.OSname Like 'Win 7%'
Or Not tblAssets.SP = 0) And tblAssetCustom.State = 1 And
tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Domain,
tblAssets.AssetName
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now