Report request

[Cobra7]

This should be simple but I'm not having any luck.

Searching for SmcGui.exe. I confirmed it is showing up in tblfilevserions but when makeing a report I either get nothing or everything. The report is to tell me who does NOT have it.

This is modified from the Adobe 9 post a while back:

SELECT TOP 100 PERCENT 
  dbo.tblComputers.Computername AS Computer,
  dbo.tblComputers.Username,
  dbo.tblComputers.Lastseen
FROM
  dbo.tblComputers
WHERE
  computername NOT IN (SELECT DISTINCT dbo.tblComputers.Computername FROM dbo.tblComputers, dbo.tblfileversions WHERE dbo.tblFileVersions.FilePathfull LIKE '%SmcGui.exe%')
ORDER BY
  dbo.tblComputers.Computername

However it also shows PC's that have it. Little help please? 🙂

[Cobra7]

Seems to be working now, I used:

SELECT TOP 100 PERCENT 
  dbo.tblComputers.Computername AS Computer,
  dbo.tblComputers.Username,
  dbo.tblComputers.Lastseen
FROM
  dbo.tblComputers
WHERE
  computername NOT IN (SELECT DISTINCT dbo.tblComputers.Computername FROM dbo.tblfileversions INNER JOIN dbo.tblComputers ON (dbo.tblfileversions.Computername = dbo.tblComputers.Computername) WHERE dbo.tblFileVersions.FilePathfull LIKE '%SmcGui.exe%')
ORDER BY
  dbo.tblComputers.Computername

The INNER JOIN seemed to be my problem.

Something that may help a bit is a way to filter out the PC's that are having errors (about 8% of our PC's can't be scanned for one reason or another, not a LS problem). If anyone has the code it would help, otherwise no big deal, I'll work it out when I have more time.

[Hemoco]

Looks ok.

Could you post the output of query (SELECT DISTINCT dbo.tblComputers.Computername FROM dbo.tblComputers, dbo.tblfileversions WHERE dbo.tblFileVersions.FilePathfull LIKE '%SmcGui.exe%')

Most likely it's a problem with NULL values.