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,
Case
When tsysOS.OScode Like '10.0.10240%' Then '1507'
When tsysOS.OScode Like '10.0.10525%' 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'
End As Version,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case tblAssets.AssetID
When SubQuery1.AssetID Then 'Patched'
Else 'Not Patched'
End As [Patch status],
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
Case
When tsysOS.OSname = 'Win 2008' Then 'KB4463097'
When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
tsysOS.OSname = 'Win 2008 R2' Then 'KB4462923'
When tsysOS.OSname = 'Win 2012' Or tsysOS.OSname = 'Win 8' Then 'KB4462929'
When tsysOS.OSname = 'Win 8.1' Or
tsysOS.OSname = 'Win 2012 R2' Then 'KB4462926'
When tsysOS.OScode Like '10.0.10240' Then 'KB4462922'
When tsysOS.OScode Like '10.0.10525' Then 'KB4093109'
When tsysOS.OScode Like '10.0.14393' Or
tsysOS.OSname = 'Win 2016' Then 'KB4462917'
When tsysOS.OScode Like '10.0.15063' Then 'KB4462937'
When tsysOS.OScode Like '10.0.16299' Then 'KB4462918'
When tsysOS.OScode Like '10.0.17134' Then 'KB4462919'
When tsysOS.OScode Like '10.0.17763' Then 'KB4464330'
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,
Case tblAssets.AssetID
When SubQuery1.AssetID Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.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
Where tblQuickFixEngineeringUni.HotFixID In ('KB4463097', 'KB4462923',
'KB4462929', 'KB4462926', 'KB4462922', 'KB4093109', 'KB4462917',
'KB4462937', 'KB4462918', 'KB4462919', 'KB4464330')) As SubQuery1 On
tblAssets.AssetID = SubQuery1.AssetID
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
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
From tblAssets Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tsysOS.OSname Like 'Win 7%' And tblAssets.SP = 0) And
tsysOS.OSname != 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' 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