→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
AnthelioHealth
Engaged Sweeper
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,
2 REPLIES 2
AnthelioHealth
Engaged Sweeper
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
Honored Sweeper II
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.)