Create Report: Laptops with Encrypted C: OFF

[Notonyourradar]

I'm trying to edit the canned report of encryted volumes but I want to limit it to Laptops and only show those with OFF for C: volume. I have this and it's apparently all wrong. Any help would be great.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblEncryptableVolume.DriveLetter,
  Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
  End As ProtectionStatus,
  tblEncryptableVolume.LastChanged,
  TsysChassisTypes.Chassistype,
  TsysChassisTypes.ChassisName
From tblEncryptableVolume
  Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join TsysChassisTypes On TsysChassisTypes.ChassisName = tblAssets.OScode
Where (TsysChassisTypes.Chassistype = 8 Or TsysChassisTypes.Chassistype = 9 Or
    TsysChassisTypes.Chassistype = 10) And tblAssets.Lastseen <> '' And
  tblAssets.Assettype = -1

Thanks

[RCorbeil · Accepted solution]

You're mislinking from the asset's OS to try identifying the chassis type. You need to link tblAssets.AssetID -> tblSystemEnclosure.AssetID to get the chassis type code and, if you want the description, tblSystemEnclosure.ChassisTypes -> tSysChassisTypes.ChassisType.

Alternatively, a shortcut that's worked for my inventory is to link tblAssets.AssetID -> tblPortableBattery.AssetID.

My thinking: if it's a portable device (notebook/laptop), it has a portable battery. My inventory bears this out.

If all you want are notebooks, you can use an INNER JOIN. If you want all devices, use a LEFT JOIN and identify the notebooks with something like

CASE WHEN tblPortableBattery.AssetID IS NULL
     THEN 'desktop'
     ELSE 'notebook'
END

Based on the default LANSweeper query:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblEncryptableVolume.DriveLetter,
  CASE WHEN tblEncryptableVolume.ProtectionStatus = 0 THEN 'OFF'
       WHEN tblEncryptableVolume.ProtectionStatus = 1 THEN 'ON'
       ELSE 'UNKNOWN'
  END AS ProtectionStatus,
  tblEncryptableVolume.LastChanged,
  TsysChassisTypes.ChassisName,
  CASE WHEN tblPortableBattery.AssetID IS NULL
       THEN 'desktop'
       ELSE 'notebook'
  END AS Classification
FROM
  tblAssets
  INNER JOIN tblAssetCustom       ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes       ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblEncryptableVolume ON tblAssets.AssetID = tblEncryptableVolume.AssetId
  INNER JOIN tblSystemEnclosure   ON tblAssets.AssetID = tblSystemEnclosure.AssetID
  INNER JOIN TsysChassisTypes     ON tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
  INNER JOIN tblPortableBattery   ON tblAssets.AssetID = tblPortableBattery.AssetID
WHERE
  tblAssetCustom.State = 1
  AND tblAssets.Assettype = -1
  AND tblAssets.Lastseen <> ''
ORDER BY
  tblAssets.AssetName,
  tblEncryptableVolume.DriveLetter

If you don't want to use my notebook shortcut, remove the references to tblPortableBattery and add your chassis type filters.