Installed software Report

[AM13337]

Can anybody help me out, i need a little help building this report. Basically i want it to check to see if Nitro PDF is installed and then say 'NitroPDF installed' else leave it blank.

Here's my code, but i'm having trouble with the case statement:

Select Distinct Top 1000000 a.AssetID,
a.AssetName,
tsysOS.Image As icon,

(Select (Case When tblSoftware.AssetID = 1 Then 'Nitro Pro Installed' Else '' End)
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName = '%Nitro Pro%')
As [Nitro PDF]

From tblAssets a
Inner Join tblAssetCustom ac On ac.AssetID = a.AssetID
Left Outer Join tsysOS On a.OScode = tsysOS.OScode
Where ac.State = 1 And a.Assettype = -1
Order By a.AssetName

[AM13337]

Could you give me a little more help?

I've run into an issue where some software has multiple names and i'd like build a report that searches for one thing then if not found it finds another.

My issue is when i run the report every line says "7-Zip 9 or 15".
The code looks like this:

--------------------
Select Distinct Top 1000000 a.AssetID,
a.AssetName,
Case When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where (tblSoftwareUni.softwareName Like '%7-Zip 9.20%') Or
(tblSoftwareUni.softwareName Like '%7-Zip 15.07%')) Then '7-Zip 9 or 15'
Else '' End As [7-Zip 9 or 15]
From tblAssets a
Inner Join tblAssetCustom ac On ac.AssetID = a.AssetID
Left Outer Join tsysOS On a.OScode = tsysOS.OScode
Where ac.State = 1 And a.Assettype = -1
Order By a.AssetName
--------------------

Also, i know i might run into issues where some computers have multiple versions of the software installed but i don't want my report to add a second line each time it finds an instance of a software what would you recommend?

After i'm done i'll share the full code maybe it will be useful for somebody else.

[Bruce_B · Accepted solution]

We were just working on a report for you, but we're glad to see you've already solved it. We'll add the report we had in mind below for reference.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case When tblAssets.AssetName = SubQuery.AssetName Then 'NitroPDF Installed'
    Else '' End As Installed
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select Top 1000000 tblAssets.AssetID,
    tblAssets.AssetName,
    tblSoftwareUni.softwareName
  From tblAssets
    Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%Nitro%') SubQuery
    On SubQuery.AssetID = tblAssets.AssetID
Where tsysAssetTypes.AssetTypename = 'windows' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

[AM13337]

I managed to find some help and got it going:

CASE
WHEN EXISTS (SELECT
tblSoftware.AssetID
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID=tblSoftware.SoftID
WHERE
tblSoftware.AssetID=tblAssets.AssetID
AND ( tblSoftwareUni.SoftwareName LIKE '%Nitro Pro%')
)
THEN 'Nitro Pro Installed'
ELSE ''
END AS [Nitro PDF]