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: 444  
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: 444  
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 Remote desktop custom port
by  sefaucher   Go to last post Go to first unread
Last post: Yesterday at 10:30:49 PM(UTC)
Lansweeper Unable to transfer user's data prior to removal
by  RickW99456  
Go to last post Go to first unread
Last post: Yesterday at 9:06:14 PM(UTC)
Lansweeper Unable to access the lansweeper consol for other computer
by  Kudnan Ingle   Go to last post Go to first unread
Last post: Yesterday at 7:49:32 PM(UTC)
Lansweeper Closing and re-opening of tickets
by  NWHiker  
Go to last post Go to first unread
Last post: Yesterday at 3:42:56 PM(UTC)
Lansweeper Wake on Lan Issues
by  woldummy   Go to last post Go to first unread
Last post: Yesterday at 3:42:07 PM(UTC)
Lansweeper Deploy and start Software
by  EDV_OHZ  
Go to last post Go to first unread
Last post: Yesterday at 12:49:08 PM(UTC)
Lansweeper Helpdek Call Re-Opened
by  mouaad   Go to last post Go to first unread
Last post: Yesterday at 7:30:59 AM(UTC)
Lansweeper macOS lsAgent 100% CPU Usage
by  McDeth187  
Go to last post Go to first unread
Last post: Yesterday at 2:30:33 AM(UTC)