→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
AM13337
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
Bruce_B
Lansweeper Alumni
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

View solution in original post

3 REPLIES 3
AM13337
Engaged Sweeper III
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
Lansweeper Alumni
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
Engaged Sweeper III
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]