Notification

Icon
Error

Report to show all PC's that have software X but not software Y

Posted: Monday, August 26, 2019 3:48:40 PM(UTC)
Cobra7

Cobra7

Member Original PosterPosts: 149
0
Like
We are upgrading some software from the old version X to a new version Y. Installing software Y does not remove software X so when Y is installed they are both on the PC. I tried to create a report showing that but it's not working.

Just a rephrease: I need a list of all PC's with software X but filter out all of those that have software Y.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Lastseen,
tblAssets.Username,
tblSoftware.softwareVersion,
tblSoftwareUni.softwareName,
tblOperatingsystem.Caption
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetName Not Like '%vxp%' And tblSoftwareUni.softwareName Like
'FileNet IDM Web Controls 3.3' And tblSoftwareUni.softwareName Not Like
'VaultDrawingSearch'
RC62N
#1RC62N Member Posts: 444  
posted: 8/26/2019 4:50:18 PM(UTC)
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  softwareX.softwareName AS X_name,
  softwareX.softwareVersion AS X_version,
  softwareY.softwareName AS Y_name,
  softwareY.softwareVersion AS Y_version
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputerSystem On tblComputerSystem.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID,
               tblSoftwareUni.softwareName,
               tblSoftware.softwareVersion
             From tblSoftware
               Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
             Where tblSoftwareUni.softwareName = 'software_x'
               And tblSoftware.softwareVersion = 'software_x_version') AS softwareX On softwareX.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID,
               tblSoftwareUni.softwareName,
               tblSoftware.softwareVersion
             From tblSoftware
               Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
             Where tblSoftwareUni.softwareName = 'software_y'
               And tblSoftware.softwareVersion = 'software_y_version') AS softwareY On softwareY.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1                -- active
  AND tblAssets.AssetType = -1            -- Windows machines
  AND tblComputerSystem.Domainrole <= 1   -- workstations
  AND softwareX.AssetID IS NOT NULL       -- software X found
  AND softwareY.AssetID IS NULL           -- software Y not found

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)