
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-24-2020 05:41 PM
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-24-2020 06:40 PM
Try this:
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.
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.
2 REPLIES 2

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-24-2020 08:50 PM
Thank you so much, that worked perfectly!
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-24-2020 06:40 PM
Try this:
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.
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.
