List all Laptops and if they have software on them

[billiams]

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?

[billiams]

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 · Accepted solution]

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]

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