→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
brianraley
Engaged Sweeper III
I'm trying to take the following report which I've been using to see which assets have a specific software but it only returns the PCs which meet the Like software name criteria supplied but does not include the actual software name.

What I'd like to see is the different software names from (tblSoftwareUni.softwareName) that match that criteria to be shown on the report.

It's okay if it may return multiple duplicate assets if there are more than one software names that meet the Like criteria.

Thank you,
Brian

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.OScode,
tblAssets.SP,
tblAssets.IPAddress As IP,
tblAssetCustom.Location,
tblAssetCustom.Department,
tblAssets.Description,
tblAssets.Lastseen,
tblAssetCustom.State
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Citrix%') And tblAssets.Lastseen <>
'' And tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Order By tblAssetCustom.Location,
tblAssets.OScode,
tblAssetCustom.Department
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Something like this?
SELECT Top 1000000
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.OScode,
tblAssets.SP,
tblAssets.IPAddress As IP,
tblAssetCustom.Location,
tblAssetCustom.Department,
tblAssets.Description,
tblAssets.Lastseen,
tblAssetCustom.State,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
FROM
tblAssets
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
tblAssets.Assettype = -1
AND tblAssets.Lastseen <> ''
And tblAssetCustom.State = 1
And tblSoftwareUni.softwareName LIKE '%Citrix%'
ORDER BY
tblAssetCustom.Location,
tblAssets.OScode,
tblAssetCustom.Department

View solution in original post

1 REPLY 1
RCorbeil
Honored Sweeper II
Something like this?
SELECT Top 1000000
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.OScode,
tblAssets.SP,
tblAssets.IPAddress As IP,
tblAssetCustom.Location,
tblAssetCustom.Department,
tblAssets.Description,
tblAssets.Lastseen,
tblAssetCustom.State,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
FROM
tblAssets
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
tblAssets.Assettype = -1
AND tblAssets.Lastseen <> ''
And tblAssetCustom.State = 1
And tblSoftwareUni.softwareName LIKE '%Citrix%'
ORDER BY
tblAssetCustom.Location,
tblAssets.OScode,
tblAssetCustom.Department