cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jpv
Engaged Sweeper
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

1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.)

View solution in original post

1 REPLY 1
RCorbeil
Honored Sweeper II
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.)