File Search Report

[paladium]

Hello,

I am struggling to customize this query. I want to generate a report that tells us when our DLP executable is not found on just the WIN XP and WIN 7 clients. I can get the full report of everything with and without the DLP executable in it across all OS platforms, but only the WIN XP and WIN 7 clients without the DLP executable needs to be in the report.

Any assistance would be appreciated 🙂

-----------------------
Current query
-----------------------

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblFileVersions.Found,
  tblFileVersions.FileVersion,
  tblAssets.Assettype,
  tblAssets.IPAddress,
  tsysOS.OSname
From tblAssets
  Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblFileVersions.FilePathfull Like '%edpa.exe'
Order By tblAssets.AssetUnique,
  tblFileVersions.FilePathfull

[Hemoco · Accepted solution]

Please use the report below. (Do not bump threads that are less than a day old. We reply to each post as quickly as possible.)

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblFileVersions.Found,
tblFileVersions.FileVersion,
tblAssets.Assettype,
tblAssets.IPAddress,
tsysOS.OSname
From tblAssets
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblFileVersions.Found = 0 And (tsysOS.OSname = 'win xp' Or
tsysOS.OSname = 'win 7') And tblFileVersions.FilePathfull Like '%edpa.exe'
Order By tblAssets.AssetUnique,
tblFileVersions.FilePathfull