Request: Missing Software Report

[JaxIsland]

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.

[D_Howell]

That worked perfectly. Thank you

[RCorbeil]

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]

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]

That is incredible, exactly what I needed. I could not get this right but that was spot on. Thank you very much!

[RCorbeil · Accepted solution]

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