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: 430  
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 Find machines missing any 1 of several apps
by  BennettL   Go to last post Go to first unread
Last post: Yesterday at 8:35:28 PM(UTC)
Lansweeper Close ticket failed
by  JohnnyL  
Go to last post Go to first unread
Last post: Yesterday at 6:51:21 PM(UTC)
Lansweeper SNMP Asset dont work
by  implenia  
Go to last post Go to first unread
Last post: Yesterday at 3:44:02 PM(UTC)
Lansweeper Remote IT Asset MGMT during the Covid-19 Crisis
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 1:14:27 PM(UTC)
Lansweeper Max Software Version and Deploy to Only Outdated
by  GenDev  
Go to last post Go to first unread
Last post: Yesterday at 1:06:50 PM(UTC)
Lansweeper Patch Tuesday - Superseded MS Updates
by  doone128   Go to last post Go to first unread
Last post: Yesterday at 11:27:10 AM(UTC)
Lansweeper Unable to transfer user's data prior to removal
by  jbruyet  
Go to last post Go to first unread
Last post: 4/1/2020 11:40:35 PM(UTC)