‎01-06-2020 03:35 PM
‎01-23-2020 04:01 PM
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
TsysLastscan.Lasttime As LastRegistryScan,
Case
When TsysLastscan.Lasttime < GetDate() - 1 Then
'Last registry scan more than 24 hours ago! Scanned registry information may not be up-to-date. Try rescanning this machine.'
End As Comment,
SubQuery1.Value As WindowsOnlineUpdate,
SubQuery2.Value As WindowsOnlineUpdateUser,
SubQuery3.Value As WindowsOnlineUpdateDate,
SubQuery4.Value As DellOnlineUpdate,
SubQuery5.Value As DellOnlineUpdateDone,
SubQuery6.Value As DellOnlineUpdateDone,
SubQuery7.Value As OfficeVersion
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\CompanyName' And
tblRegistry.Valuename = 'WindowsOnlineUpdate') SubQuery1 On
SubQuery1.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\CompanyName' And
tblRegistry.Valuename = 'WindowsOnlineUpdateUser') SubQuery2 On
SubQuery2.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\CompanyName' And
tblRegistry.Valuename = 'WindowsOnlineUpdateDate') SubQuery3 On
SubQuery3.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\CompanyName' And
tblRegistry.Valuename = 'DellOnlineUpdate') SubQuery4 On
SubQuery4.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\CompanyName' And
tblRegistry.Valuename = 'DellOnlineUpdateUser') SubQuery5 On
SubQuery5.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\CompanyName' And
tblRegistry.Valuename = 'DellOnlineUpdateDate') SubQuery6 On
SubQuery6.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\CompanyName' And
tblRegistry.Valuename = 'OfficeVersion') SubQuery7 On
SubQuery7.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now