→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
kuhlman
Engaged Sweeper
I've got a report that lists all of the assets with a certain piece of software installed (in this instance, the software is INSITE). What I'm having trouble with is getting the list to show all of the software installed on those machines as well. I lifted this code straight from these forums, but not sure how to get it to list the rest of the installed software. Creating reports is generally out of my realm, so apologies for that in advance.


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.FQDN,
tblAssets.IPAddress,
tblADComputers.OU,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.Lastchanged
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblSoftwareUni.softwareName Like N'INSITE%' And tblAssetCustom.State = 1
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Try this:
Select Top 1000000
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.FQDN,
tblAssets.IPAddress,
tblADComputers.OU,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.Lastchanged
From
tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
tblSoftwareUni.softwareName Like N'INSITE%') AS HasInsite ON HasInsite.AssetID = tblAssets.AssetID
Where
tblAssetCustom.State = 1

The sub-SELECT produces a list of assets that have the INSITE software installed.
The INNER JOIN limits the main SELECT to only those assets in the sub-SELECT list.
The main SELECT pulls a list of all software for those assets that are in the sub-SELECT list.

View solution in original post

2 REPLIES 2
kuhlman
Engaged Sweeper
Thank you so much, that worked perfectly!
RCorbeil
Honored Sweeper II
Try this:
Select Top 1000000
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.FQDN,
tblAssets.IPAddress,
tblADComputers.OU,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.Lastchanged
From
tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
tblSoftwareUni.softwareName Like N'INSITE%') AS HasInsite ON HasInsite.AssetID = tblAssets.AssetID
Where
tblAssetCustom.State = 1

The sub-SELECT produces a list of assets that have the INSITE software installed.
The INNER JOIN limits the main SELECT to only those assets in the sub-SELECT list.
The main SELECT pulls a list of all software for those assets that are in the sub-SELECT list.