Notification

Icon
Error

Request: Missing Software Report - Need to list assets with installed or not installed software

Posted: Tuesday, August 6, 2019 4:15:18 PM(UTC)
JaxIsland

JaxIsland

Member Original PosterPosts: 2
0
Like
This issue has been solved! Click here to view the solution
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.
RC62N
#1RC62N Member Posts: 441  
posted: 8/6/2019 6:08:36 PM(UTC)
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.
Code:
-- 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
JaxIsland
#2JaxIsland Member Original PosterPosts: 2  
posted: 8/6/2019 7:10:34 PM(UTC)
That is incredible, exactly what I needed. I could not get this right but that was spot on. Thank you very much!
Lone Jedi
#3Lone Jedi Member Posts: 3  
posted: 2/12/2020 7:57:03 PM(UTC)
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.
RC62N
#4RC62N Member Posts: 441  
posted: 2/12/2020 9:50:25 PM(UTC)
I'm starting to feel like a broken record. Angel 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.
Lone Jedi
#5Lone Jedi Member Posts: 3  
posted: 2/12/2020 10:13:49 PM(UTC)
That worked perfectly. Thank you

Active Discussions

Lansweeper All USB devices connected
by  earmor   Go to last post Go to first unread
Last post: Yesterday at 1:35:37 PM(UTC)
Lansweeper Software audit with installation date
by  RC62N   Go to last post Go to first unread
Last post: 7/3/2020 3:39:41 PM(UTC)
Lansweeper Duplicates (3-4) in report please helppppp!
by  AlexMZetec  
Go to last post Go to first unread
Last post: 7/2/2020 3:15:16 PM(UTC)
Lansweeper Report to find all computers with no logon data
by  Andy.S  
Go to last post Go to first unread
Last post: 7/2/2020 9:55:53 AM(UTC)
Lansweeper Change processes report to show opposite results
by  RC62N   Go to last post Go to first unread
Last post: 6/29/2020 9:38:46 PM(UTC)
Lansweeper Keep link to items when using column names
by  Tom Galvin  
Go to last post Go to first unread
Last post: 6/27/2020 12:16:03 AM(UTC)