→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Cobra7
Champion Sweeper
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'
1 REPLY 1
RCorbeil
Honored Sweeper II
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