Report showing all notebooks without endpoint agent

[jpv]

Hello,

I would like genearate a report showing all notebooks/laptops without endpoint security isntalled in my environment.

So far this is what I have, but I can't seem to be able to query for only notebooks/laptops. Thanks in advance for any help.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Lastseen,
tblADusers.Name,
tblADusers.Department,
tblAssets.Description
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Where tblSoftwareUni.softwareName Like '%Pointsec PC%')
Order By tblAssets.AssetUnique

[RCorbeil · Accepted solution]

You could identify the notebooks/laptops by checking for the variety of chassis types under which they appear (laptop, notebook, portable)

SELECT
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  ...
  tSysChassisTypes.ChassisName
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  ...
  INNER JOIN tblSystemEnclosure ON tblAssets.AssetID = tblSystemEnclosure.AssetID
  INNER JOIN TsysChassisTypes   ON tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
WHERE
  tblAssetCustom.State = 1
  AND tSysChassisTypes.ChassisName IN ('laptop', 'notebook', 'portable')

however, I find it simpler to just see whether the asset has a portable battery

SELECT
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  ...
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  ...
  INNER JOIN tblPortableBattery ON tblAssets.AssetID = tblPortableBattery.AssetID
WHERE
  tblAssetCustom.State = 1

One table link instead of two, and by the nature of the INNER JOIN, any assets without a battery are automatically filtered out.

Try this:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Lastseen,
  tblADusers.Name,
  tblADusers.Department,
  tblAssets.Description
FROM
  tblAssets
  Inner Join tsysAssetTypes     ON tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblADusers         ON tblAssets.Username = tblADusers.Username
  Inner Join tblPortableBattery ON tblAssets.AssetID = tblPortableBattery.AssetID
WHERE
  NOT EXISTS (SELECT Top 1
                tblSoftware.AssetID
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
              WHERE
                tblSoftware.AssetID = tblAssets.AssetID
                AND tblSoftwareUni.softwareName LIKE '%Pointsec PC%'
             )
ORDER BY tblAssets.AssetUnique

(The WHERE tblAssets.AssetID NOT IN... would work too. To-may-to, to-mah-to.)