Build a report using results from Custom File Scan

[CarlV]

Good Morning,

I have a custom file scan running to return results for C:\ProgramData\CTES\Ctes.exe  

I am getting results on this file scan, but want to build a report based on the results of this scan.  I want to locate all Laptops in the environment that do NOT have this file.  I did some searching in the forums and couldn't find a solution that matched this requirement.  

Any help would be greatly appreciated!

[rom]

I left some code in there if you want to see how to determine laptop/desktop, and left in the found/not found, in case you want to expand on those.

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt],
  Case
    When tblPortableBattery.AssetID Is Null And tblBattery.AssetID Is Null Then
      'Desktop'
    Else 'Laptop'
  End As [Desktop/Laptop],
  Case     
		When filelookup.Found = 1 Then 'Yes'     
		When filelookup.Found = 0 Then 'Not Found'     
		Else 'Not scanned'   
	End As [Has Ctest.exe]
From tblAssets
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Outer Join tblPortableBattery On
      tblAssets.AssetID = tblPortableBattery.AssetID
  Left Outer Join tblBattery On tblAssets.AssetID = tblBattery.AssetID
  Left Join 
	(Select 
		tblFileVersions.AssetID,         
		tblFileVersions.Found,         
		tblFileVersions.FilePathfull,         
		tblFileVersions.FileVersion,         
		tblFileVersions.CompanyName,         
		tblFileVersions.Filesize,         
		tblFileVersions.Lastchanged       
	From tblFileVersions       
	Where tblFileVersions.FilePathfull Like '%ProgramData\CTES\Ctes.exe') filelookup 
On tblAssets.AssetID = filelookup.AssetID   
Where (tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or
    tblAssetCustom.Model Not Like '%Virtual%') And
  tblAssets.Lastseen Is Not Null And tblAssets.Lastseen <> '' And
  tblState.Statename = 'Active' And tsysAssetTypes.AssetTypename In ('Windows',
  'Windows CE')
  and tblPortableBattery.AssetID is not null
Order By tblAssets.Domain,
  tblAssets.AssetName