Java - Flash - Adobe

[dteague]

I wish there was a report that would tell me computers that needed updated for Java - Flash - Adobe.

It wouldn't be hard hard code the versions in a report, but would be nice to have it dynamically updated, so when one of these is updated, the report would show it.

Anyone doing this, or have an idea how to dynamically do this?

[joegasper]

I use the following to give me a list of all the commonly exploitable software and my anti-virus installations:

Select Distinct Top 1000000 dbo.tblSoftware.softwareName As Software, 
   dbo.tblSoftware.softwareVersion As Version, 
   Count(dbo.tblSoftware.SoftwareID) As Total 
From dbo.tblSoftware 
Inner Join dbo.tblComputers 
On dbo.tblSoftware.ComputerName = dbo.tblComputers.Computername 
Inner Join dbo.web40ActiveComputers 
On dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername 
Where dbo.tblSoftware.softwareName Like 'java%' 
Or dbo.tblSoftware.softwareName Like '%j2se%' 
Or dbo.tblSoftware.softwareName Like '%jdk%' 
Or dbo.tblSoftware.softwareName Like 'adobe acrobat%' 
Or dbo.tblSoftware.softwareName Like 'macromedia shockware%' 
Or dbo.tblSoftware.softwareName Like 'mcafee%' 
Or dbo.tblSoftware.softwareName Like '%forefront%' 
Or dbo.tblSoftware.softwareName Like 'adobe air%' 
Or dbo.tblSoftware.softwareName Like 'adobe flash player%' 
Or dbo.tblSoftware.softwareName Like 'adobe reader%' 
Or dbo.tblSoftware.softwareName Like 'adobe shockwave%' 
Group By dbo.tblSoftware.softwareName, dbo.tblSoftware.softwareVersion 
Order By dbo.tblSoftware.softwareName, dbo.tblSoftware.softwareVersion

[dteague]

joegasper wrote:
I use the following to give me a list of all the commonly exploitable software and my anti-virus installations:

Select Distinct Top 1000000 dbo.tblSoftware.softwareName As Software, 
   dbo.tblSoftware.softwareVersion As Version, 
   Count(dbo.tblSoftware.SoftwareID) As Total 
From dbo.tblSoftware 
Inner Join dbo.tblComputers 
On dbo.tblSoftware.ComputerName = dbo.tblComputers.Computername 
Inner Join dbo.web40ActiveComputers 
On dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername 
Where dbo.tblSoftware.softwareName Like 'java%' 
Or dbo.tblSoftware.softwareName Like '%j2se%' 
Or dbo.tblSoftware.softwareName Like '%jdk%' 
Or dbo.tblSoftware.softwareName Like 'adobe acrobat%' 
Or dbo.tblSoftware.softwareName Like 'macromedia shockware%' 
Or dbo.tblSoftware.softwareName Like 'mcafee%' 
Or dbo.tblSoftware.softwareName Like '%forefront%' 
Or dbo.tblSoftware.softwareName Like 'adobe air%' 
Or dbo.tblSoftware.softwareName Like 'adobe flash player%' 
Or dbo.tblSoftware.softwareName Like 'adobe reader%' 
Or dbo.tblSoftware.softwareName Like 'adobe shockwave%' 
Group By dbo.tblSoftware.softwareName, dbo.tblSoftware.softwareVersion 
Order By dbo.tblSoftware.softwareName, dbo.tblSoftware.softwareVersion

Thanks. I have copied that, and will use it as well. My goal is to have something on the main page, so it will give my IT department something to look at and know they need to do some upgrades, as well as when they look up a computer by name see that it is not current.

What I wrote this morning works, just is not as dynamic as I want for the various apps because of sight differences on how they list different versions.

Thanks for your code!

[dteague]

Do you see any issue with this?

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
  tblComputers.Domain, tblSoftware.softwareName
From tblComputers Inner Join
  tblSoftware On tblComputers.Computername = tblSoftware.ComputerName
Where tblSoftware.softwareName Like 'java(TM) 6%' And
  tblSoftware.softwareName Like '%Update%' And tblSoftware.softwareVersion Not
  In (Select Top 1 tblSoftware.softwareVersion From tblSoftware
  Where tblSoftware.softwareName Like 'java(TM) 6%' And
    tblSoftware.softwareName Like '%Update%' Order By
    tblSoftware.softwareVersion Desc)
Order By tblSoftware.softwareVersion, tblComputers.Computername

This would give me a list of all machines that don't have the latest version that at least 1 machine in my network has. It won't tell me if there is a new version, but will give me a list of all that don't match whatever the newest version I have on at least 1 machine.

So you see anything wrong with it?

[Hemoco]

dteague wrote:
Do you see any issue with this?

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
  tblComputers.Domain, tblSoftware.softwareName
From tblComputers Inner Join
  tblSoftware On tblComputers.Computername = tblSoftware.ComputerName
Where tblSoftware.softwareName Like 'java(TM) 6%' And
  tblSoftware.softwareName Like '%Update%' And tblSoftware.softwareVersion Not
  In (Select Top 1 tblSoftware.softwareVersion From tblSoftware
  Where tblSoftware.softwareName Like 'java(TM) 6%' And
    tblSoftware.softwareName Like '%Update%' Order By
    tblSoftware.softwareVersion Desc)
Order By tblSoftware.softwareVersion, tblComputers.Computername

This would give me a list of all machines that don't have the latest version that at least 1 machine in my network has. It won't tell me if there is a new version, but will give me a list of all that don't match whatever the newest version I have on at least 1 machine.

So you see anything wrong with it?

The report should be written like the one below. Replace "YourSoftware" with the software package you want to report on. The query will then return a list of computers that do not have this software package installed.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain
From tblComputers
Where tblComputers.Computername Not In (Select tblSoftware.ComputerName
From tblSoftware Where tblSoftware.softwareName Like '%YourSoftware%')
Order By tblComputers.ComputerUnique

[dteague]

Lansweeper wrote:
The report should be written like the one below. Replace "YourSoftware" with the software package you want to report on. The query will then return a list of computers that do not have this software package installed.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
  tblComputers.Domain
From tblComputers
Where tblComputers.Computername Not In (Select tblSoftware.ComputerName
  From tblSoftware Where tblSoftware.softwareName Like '%YourSoftware%')
Order By tblComputers.ComputerUnique

I don't think this will not work for me, as I would have to know what version I am wanting to look for. What I wrote is more dynamic, and would find what the newest version I had, and display all the ones that do not match. In my case, I already have Java, Adobe Reader, or Flash installed, just my IT staff has not updated them to the current version. I am wanting something I can have on the home page, so they know that a machine is not up to date.

What I wrote works, just is not as dynamic as I would like, and needs tweeked for each of the applications due to versions being a text field and sorting to get the last value is harder (It also doesn't address my initial question/wish, but I can live with that).

[Hemoco · Accepted solution]

dteague wrote:
I don't think this will not work for me, as I would have to know what version I am wanting to look for. What I wrote is more dynamic, and would find what the newest version I had, and display all the ones that do not match.

This may work for Java, but we do not recommend relying on this. Version numbers may not always be uniform and even if they are, there are still issues due to Lansweeper considering the version field as a single string.

We've attached an example to this post. It shows Adobe Flash Player 10 ActiveX installations within a network, with the version number sorted in descending order. The SQL code is the following:

Select tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblSoftware.softwareName, tblSoftware.softwareVersion,
tblSoftware.SoftwarePublisher
From tblComputers Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName
Order By tblSoftware.softwareName Desc, tblSoftware.softwareVersion Desc

Human logic may dictate that the second entry is actually more recent than the first (and this is in fact the case). That is not how the code sorts the entries however. This is just one example of sorting problems that may occur.

[Hemoco]

dteague wrote:
I wish there was a report that would tell me computers that needed updated for Java - Flash - Adobe.

This cannot easily be accomplished.

[dteague]

Lansweeper wrote:
This cannot easily be accomplished.

Yeah, that is what I figured. There would have to be a place on the internet I would have to query that would have the current versions of all of those programs, and I would have to have some sort of AT job that executes a script that pulled that info and put it in a table (sorta how the Dell warranty script works).

If I could get the info, creating the SQL to see if they were right would be easy.

Oh well, Thanks.