08-06-2019 05:15 PM
Solved! Go to Solution.
08-06-2019 07:08 PM
-- those assets with the desired software and maybe the right version
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
Case
When tblSoftwareUni.softwareName Like 'software_name%' And tblSoftware.softwareVersion = 'wanted_software_version'
Then '#ccffcc' -- green: good name and good version
Else '#ffcccc' -- red: good name, but wrong version
End As backgroundcolor
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName Like 'software_name%'
And tblAssetCustom.State = 1
And tblAssets.Assettype = -1
Union
-- those assets without the desired software
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
Null,
Null,
'#ffcccc' As backgroundcolor -- always red; software not installed
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where
tblAssetCustom.State = 1
And tblAssets.Assettype = -1
And Not Exists(Select tblSoftware.AssetID
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblAssets.AssetID = tblSoftware.AssetID
And tblSoftwareUni.softwareName Like 'software_name%')
Order By
AssetName
02-12-2020 10:13 PM
02-12-2020 09:50 PM
02-12-2020 07:57 PM