Report for Laptops missing specific file installed

[mmeetze]

We have custom file scanning turned on and we are able to report on the machines that DO have the file installed, however when I try to create a report that will show the devices that do not have this file I am running into troubles. This file will only be present on Windows Laptops and Windows Tablets however every time I try to filter it down to only Windows Portable Chassis Types it gives me a SQL error. Here is the report as it stands without any filtering, please help :


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Lastseen,
tsysOS.OSname,
tsysOS.Image As icon,
tblAssets.Domain,
tblAssets.Username,
tblAssetCustom.State,
tblAssets.AssetUnique
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.AssetID Not In (Select tblFileVersions.AssetID
From tblFileVersions
Where
tblFileVersions.FilePathfull Like 'C:\Windows\System32\drivers\MyFile.sys'
And tblFileVersions.Found = 'True') And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique

[MikeMc]

I took your original query and added the portable types that Lansweeper uses in its reports.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Lastseen,
tsysOS.OSname,
tsysOS.Image As icon,
tblAssets.Domain,
tblAssets.Username,
tblAssetCustom.State,
tblAssets.AssetUnique
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSystemEnclosure On tblSystemEnclosure.AssetID = tblAssets.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes
Where tblAssetCustom.State = 1 AND (TsysChassisTypes.ChassisName = 'Laptop' Or TsysChassisTypes.ChassisName =
    'Notebook' Or TsysChassisTypes.ChassisName = 'Portable') AND tblAssets.AssetID Not In (Select tblFileVersions.AssetID
From tblFileVersions
Where
tblFileVersions.FilePathfull Like 'C:\Windows\System32\drivers\MyFile.sys'
And tblFileVersions.Found = 'True') 
Order By tblAssets.AssetUnique