→ 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: 
billiams
Engaged Sweeper
Hello - Been working on this for a few days based on already built reports in LS and online. I have 2 reports I am looking to merge into 1. I want to list all laptops in our environment (Report 1) and indicate if they have PGP or Symantec Encryption installed on them (report 2). No matter how I format the report, I am not able to get these 2 items together. Any suggestions?
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
I'm going to interpret the request slightly differently. As I read it, the request was to list all notebooks, not just those that have the specified software installed, and identify whether either of the two software titles is installed.
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
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 '%PGP%'
OR tblSoftwareUni.SoftwareName LIKE '%Symantec Encryption%'
)
)
THEN 'YES'
ELSE ''
END AS CryptoFound
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblPortableBattery ON tblPortableBattery.AssetID=tblAssets.AssetID
WHERE
tblAssetCustom.State = 1

Replace "%PGP%" and "%Symantec Encryption%" with actual text to match, of course.

If you want to know specifically which of the software titles is installed, duplicate the CASE block and have each block check for only one of the titles.

View solution in original post

3 REPLIES 3
billiams
Engaged Sweeper
Thank you both for you help. RC62N solution did what I was looking for. I did need all laptops listed reguardless of if the software was installed or not.

I appreciate all your help!
RCorbeil
Honored Sweeper II
I'm going to interpret the request slightly differently. As I read it, the request was to list all notebooks, not just those that have the specified software installed, and identify whether either of the two software titles is installed.
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
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 '%PGP%'
OR tblSoftwareUni.SoftwareName LIKE '%Symantec Encryption%'
)
)
THEN 'YES'
ELSE ''
END AS CryptoFound
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblPortableBattery ON tblPortableBattery.AssetID=tblAssets.AssetID
WHERE
tblAssetCustom.State = 1

Replace "%PGP%" and "%Symantec Encryption%" with actual text to match, of course.

If you want to know specifically which of the software titles is installed, duplicate the CASE block and have each block check for only one of the titles.
Daniel_B
Lansweeper Alumni
Laptops can be found by checking if the asset has an entry in tblPortableBattery.
Software installations are saved in tblSoftware which is linked to tblSoftwareUni. The following report should give you the required result. Be sure to use software names as they appear in the list of installed software on the asset pages of your computers.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID In (Select tblPortableBattery.AssetID
From tblPortableBattery) And (tblSoftwareUni.softwareName Like '%PGP%' Or
tblSoftwareUni.softwareName Like 'Symantec Encryption%') And
tblAssetCustom.State = 1