List Machines with Multiple Software Versions

[atkinsjd]

Does anyone have a query for computers that have multiple versions of the same software installed? For example, I want to display a list of computers with multiple versions of Java installed.

[Hemoco · Accepted solution]

Please use the report below for the information you are after.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join (Select Top 1000000 tblSoftware.AssetID,
Count(tblSoftware.softID) As Count
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%java%'
Group By tblSoftware.AssetID) SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%java%' And tblAssetCustom.State = 1
And SubQuery1.Count > 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName