cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JaxIsland
Engaged Sweeper
I am trying to put together a report that lists all Windows assets (Servers and Desktops). I am looking to have any asset with a specific application and version in Green and any asset that does not have the application installed or does not match the version to be in red. I have found examples in the forums but none will show and highlight the assets without the application as well as with.
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Since you're actually wanting two different sets of data (assets with software_name and assets without software_name), I think you're going to need to create a union of two data selections. Substitute the software name and version that you're looking for, of course.
-- 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

View solution in original post

5 REPLIES 5
D_Howell
Engaged Sweeper II
That worked perfectly. Thank you
RCorbeil
Honored Sweeper II
I'm starting to feel like a broken record. See this thread for an approach that should work. Use the two sub-queries to search for your two software titles. Adjust the filters at the bottom of the main query to check that both are present rather than "X but not Y" as was requested in that case.
D_Howell
Engaged Sweeper II
Quick question about this.

I was able to use the top portion of this code twice to find all my machines that have both the 64 and 32bit version of firefox installed.

But doing that listed every machine that had firefox installed. How can I show only those machines that have both installed?

Any help would be great.
JaxIsland
Engaged Sweeper
That is incredible, exactly what I needed. I could not get this right but that was spot on. Thank you very much!
RCorbeil
Honored Sweeper II
Since you're actually wanting two different sets of data (assets with software_name and assets without software_name), I think you're going to need to create a union of two data selections. Substitute the software name and version that you're looking for, of course.
-- 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