‎02-11-2021 08:37 PM
‎02-16-2021 11:48 AM
Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Domain,
Case
When tblAssets.AssetID = Four.AssetID Then 'Installed'
Else 'Not Installed'
End As [Patch Month M - 3],
Case
When tblAssets.AssetID = Three.AssetID Then 'Installed'
Else 'Not Installed'
End As [Patch Month M - 2],
Case
When tblAssets.AssetID = Two.AssetID Then 'Installed'
Else 'Not Installed'
End As [Patch Month M - 1],
Case
When tblAssets.AssetID = One.AssetID Then 'Installed'
Else 'Not Installed'
End As [Patch Month M],
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
tsysIPLocations.IPLocation,
tblAssetCustom.Custom4 As [System Coordinator],
tblAssetCustom.Custom5 As [Network Administrator],
tblAssetCustom.Custom6 As [System Administrator],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblOperatingsystem.Caption 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'
When tsysOS.OScode Like '10.0.18363%' Then '1909'
When tsysOS.OScode Like '10.0.19041%' Then '2004'
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())) As WindowsUpdateInfoLastScanned,
Case
When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) > 3 Then 'Rescan Asset'
Else ''
End As Comment,
Case
When tblAssets.AssetID = One.AssetID Or tblAssets.AssetID = Two.AssetID Or
tblAssets.AssetID = Three.AssetID Or
tblAssets.AssetID = Four.AssetID Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor,
'Januari' As [Month M]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Distinct Top 2000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Inner Join tblQuickFixEngineeringHist On
tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4598287', 'KB4598288',
'KB4598279', 'KB4598289', 'KB4598278', 'KB4598297', 'KB4598285',
'KB4598275', 'KB4598231', 'KB4598243', 'KB4598245', 'KB4598230',
'KB4598229', 'KB4598242')) As One On tblAssets.AssetID = One.AssetID
Left Join (Select Distinct Top 2000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Inner Join tblQuickFixEngineeringHist On
tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4592498', 'KB4592504',
'KB4592471', 'KB4592503', 'KB4592468', 'KB4592497', 'KB4592484',
'KB4592495', 'KB4592464', 'KB4593226', 'KB4592446', 'KB4592440',
'KB4592449', 'KB4592438')) As Two On tblAssets.AssetID = Two.AssetID
Left Join (Select Distinct Top 2000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Inner Join tblQuickFixEngineeringHist On
tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4586807', 'KB4586817',
'KB4586827', 'KB4586805', 'KB4586834', 'KB4586808', 'KB4457129',
'KB4586823', 'KB4586787', 'KB4586830', 'KB4586785', 'KB4586793',
'KB4586786', 'KB4586781')) As Three On tblAssets.AssetID = Three.AssetID
Left Join (Select Distinct Top 2000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID
= tblQuickFixEngineering.QFEID
Inner Join tblQuickFixEngineeringHist On
tblQuickFixEngineeringHist.QFEID = tblQuickFixEngineeringUni.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4580385', 'KB4580378',
'KB4580387', 'KB4580345', 'KB4580353', 'KB4580382', 'KB4580358',
'KB4580347', 'KB4580327', 'KB4580346', 'KB4580328', 'KB4580330',
'KB4577668', 'KB4577671', 'KB4577671', 'KB4579311')) As Four On
tblAssets.AssetID = Four.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 2000 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 2000 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 tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID Not In (Select Distinct Top 2000 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
tsysOS.OSname Not Like '%2003%' And tsysAssetTypes.AssetTypename Like
'Windows%' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now