‎04-09-2020 03:07 PM
‎04-10-2020 11:39 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.Image As icon,
tblAssets.Domain,
tblAssets.IPAddress,
unioned.software As AVSoftware,
DATQuery.Value As DATversion,
DATQuery.Lastchanged As LastChanged,
Case CharIndex('|', EPOQuery.Value)
When 0 Then EPOQuery.Value
Else SubString(EPOQuery.Value, 1, CharIndex('|', EPOQuery.Value) - 1)
End As EPOServer,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssetCustom.Custom6 As 'System Administrator',
tblAssetCustom.Custom5 As 'Network Administrator',
tblAssetCustom.Custom4 As 'System Coördinator',
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Scanserver,
Case
When Convert(nvarchar,DateDiff(day, DATQuery.Lastchanged, GetDate())) > 7
Then '#ffadad'
When unioned.software = '' Or unioned.software Is Null Then '#ffff00'
Else '#d4f4be'
End As backgroundcolor
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Valuename Like '%ePOServerList%') EPOQuery On
EPOQuery.AssetID = tblAssets.AssetID
Left Join ((Select a.assetid As assetid,
a.software As software,
a.version As version,
'software comparison' As RetrievedFrom,
'' As Enabled,
'' As Uptodate
From (Select tblSoftware.AssetID As assetid,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) a)) unioned On tblAssets.AssetID =
unioned.assetid
Left Join (Select t.AssetID,
t.Regkey,
t.Valuename,
t.Value,
t.Lastchanged
From (Select tblRegistry.AssetID,
Max(tblRegistry.Lastchanged) As max_lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%McAfee%' And
tblRegistry.Valuename Like '%version%'
Group By tblRegistry.AssetID) As m
Inner Join tblRegistry As t On t.AssetID = m.AssetID And
t.Lastchanged = m.max_lastchanged And t.Valuename Like '%version%' And
t.Regkey Like '%McAfee%') DATQuery On DATQuery.AssetID =
tblAssets.AssetID
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.Image,
tblAssets.Domain,
tblAssets.IPAddress,
unioned.software,
DATQuery.Value,
DATQuery.Lastchanged,
tsysIPLocations.IPLocation,
tsysOS.OSname,
tblAssets.SP,
tblAssetCustom.Custom6,
tblAssetCustom.Custom5,
tblAssetCustom.Custom4,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Scanserver,
EPOQuery.Value
Order By tblAssets.AssetName
‎04-10-2020 05:18 PM
‎04-10-2020 02:45 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now