More report help needed

[AnthelioHealth]

I built a report based on file scanning. When I run this report, I get a list showing each server multiple times (usually twice) with one entry for tblFileVersions.Found = ‘False’ and one for tblFileVersions.Found = ‘True’. I expected this, but now I need to “tweak” the results. I would like the report to only show servers where tblFileVersions.Found = ‘False’ for all possible paths and each server that fulfills this criteria only be on the report once.

File scanning for “logfile.log” (This file may be located in one of 4 paths) (let me take a moment to thank that particular vendor for changing their install path and making things soooo much easier…):

%programfiles%\directory1\logfile.log
%programfiles%\directory2\logfile.log
%programfiles(x386)%\directory1\logfile.log
%programfiles(x386)%\directory2\logfile.log

SQL statement:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssets.Domain,
tblFileVersions.Found,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName,
tblFileVersions.Filesize,
tblFileVersions.CreationDate,
tblFileVersions.LastAccessed,
tblFileVersions.LastModified,
tblFileVersions.Lastchanged,
tblAssets.Scanserver,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblFileVersions.FilePathfull Like '%logfile.log' And
tblComputersystem.Domainrole > 1
Order By tblAssets.AssetUnique,

[AnthelioHealth]

Seems to work great. Need to compare it to some other output from the monitoring software, but this certainly looks to be a step in the right direction. Thanks so much for your help.

[RCorbeil]

It's not the most elegant thing, but try this:

Select Top 1000000
  tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.OSname,
  tblAssets.Domain,
  tblAssets.Scanserver,
  tblComputersystem.Domainrole
From
  tblAssets
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where
  tblComputersystem.Domainrole > 1
  And ( Select Count(*)
        From tblFileVersions
        Where tblFileVersions.AssetID = tblAssets.AssetID
          And tblFileVersions.FilePathfull Like '%logfile.log') > 0
  And ( Select Count(*)
        From tblFileVersions
        Where tblFileVersions.AssetID = tblAssets.AssetID
          And tblFileVersions.Found = 0
          And tblFileVersions.FilePathfull Like '%logfile.log') =
   ( Select Count(*)
        From tblFileVersions
        Where tblFileVersions.AssetID = tblAssets.AssetID
          And tblFileVersions.FilePathfull Like '%logfile.log')
Order By
  tblAssets.AssetUnique

I.e. if there as many not-found results as there are results, then it's not found.

Edit: added a condition to ensure non-zero count results. (I did say it was inelegant.)