Adobe Reader 9 Help

[jreimer1]

Trying to get this to show me which computers don't have Adobe Reader 9 but every time i look doesn't show any computers and i know a bunch that don't have it. What is wrong with this SQL.

' This is a sample report that checks all computers if they have the latest version of SAV
' The file that we need to check : %programfiles%\Adobe\Reader 9.0\AcroRd32.exe

' Add the file that we need to scan to the tsysfiles table
' you can do the same from the GUI interface

' Create the query to check for the latest version
' the latest version of our software is 9.0.0.322

CREATE VIEW dbo.web30repAdobe
AS
SELECT TOP 100 PERCENT dbo.tblComputers.Computername AS Computer, dbo.tblOperatingsystem.Description, dbo.tblFileVersions.FileVersion, dbo.tblFileVersions.FileDescription, dbo.tblFileVersions.Filesize, dbo.tblFileVersions.Lastchanged
FROM dbo.tblComputers INNER JOIN
dbo.tblOperatingsystem ON dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername LEFT OUTER JOIN
dbo.tblFileVersions ON dbo.tblComputers.Computername = dbo.tblFileVersions.Computername
WHERE (dbo.tblFileVersions.FilePathfull LIKE '%AcroRd32.exe%') AND (NOT (dbo.tblFileVersions.FileVersion LIKE '9.0%'))
ORDER BY dbo.tblComputers.Computername

GO

INSERT INTO dbo.TsysFiles
(Searchfile, Enabled)
VALUES ('%programfiles%\Adobe\Reader 9.0\AcroRd32.exe', 1)

'Add the view to the reports table

INSERT INTO [tsysreports] ([Reportquery],[Reporttitle]) VALUES ('web30repAdobe','Adobe Reader Update')

GO

[Cobra7]

Useing MS SQL express (not the Lansweeper scripting)

select c.username,c.computername,s.softwarename,s.softwareversion
from tblcomputers c, tblsoftware s
where c.computername = s.computername
and s.computername in (select computername from tblsoftware
where s.softwarename like 'adobe reader%' and s.softwarename != 'Adobe Reader 8.1.2')
order by softwarename,softwareversion

This finds everyone who has Adobe but not version 8.1.2 (the version my company uses).

[jreimer1]

Alright tweaked it to this and now all machines are showing up as needing update which is not correct.

' This is a sample report that checks all computers if they have the latest version of SAV
' The file that we need to check : %programfiles%\Adobe\Reader 9.0\AcroRd32.exe

' Add the file that we need to scan to the tsysfiles table
' you can do the same from the GUI interface

' Create the query to check for the latest version
' the latest version of our software is 9.0.0.322

CREATE VIEW dbo.web30repAdobe
AS
SELECT TOP 100 PERCENT dbo.tblComputers.Computername AS Computer FROM dbo.tblComputers where computername not in (select distinct dbo.tblComputers.Computername from dbo.tblfileversions WHERE (dbo.tblFileVersions.FilePathfull LIKE '%AcroRd32.exe%') AND (dbo.tblFileVersions.FileVersion LIKE '9.0%'))
ORDER BY dbo.tblComputers.Computername

GO

INSERT INTO dbo.TsysFiles
(Searchfile, Enabled)
VALUES ('%programfiles%\Adobe\Reader 9.0\AcroRd32.exe', 1)

'Add the view to the reports table

INSERT INTO [tsysreports] ([Reportquery],[Reporttitle]) VALUES ('web30repAdobe','Adobe Reader Update')

Anything you can think of that i am doing wrong. SQL is all new to me.

[Hemoco]

You need to change the view into something like this (not checked for syntax errors)

SELECT TOP 100 PERCENT dbo.tblComputers.Computername AS Computer FROM dbo.tblComputers where computername not in (select distinct dbo.computername from dbo.tblfileversions WHERE (dbo.tblFileVersions.FilePathfull LIKE '%AcroRd32.exe%') AND (dbo.tblFileVersions.FileVersion LIKE '9.0%'))