A common task is to find computers that don't have a certain software and version installed. I wish there was an easier way to do this in the web GUI without having to use SQL. For example, Sophos Anti-Virus should be auto-updating all clients to the latest version so we need to find the ones failing to update. The simple logic statement is:

Find all machines that don't have Sophos Anti-Virus version 9.0.5.

For now I'm ignoring the Macs which have a different current version (7.1.7) of Sophos and the exact same software title (Sophos Anti-Virus).

Since the software version number is not part of the software title this can't be done in the computer inventory web interface. The advanced search allows you to search for computers with a software title but not version numbers.

Looking at the software assets, we use a software filter to pull together all of the versions of Sophos under one record we call Sophos AV. In there all the version variations are listed. Inside each of those line entries are the details of which computers have the older version so I can get to the data. Because the computers are scattered at various sites it would be convenient to have a report sorted by machine name of all of the problem computers.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Rich,

One way to do this easily for a single application, like Sophos, would be to do a custom Software item that has a rule that looks at file version < x, call it "Old Sophos Version" or something like that. Then you could easily use the normal label stuff in Machine UI or report easily on machines that have that software title to identify anyone that is not current as well as the inverse to find those that are compliant. To use the data as it is presented in the Asset module will be somewhat nasty SQL, unfortunately. A good feature request to expose more of the software attributes in the report editor.

Chris...
Answered 06/02/2010 by: chrisgrim
Senior Purple Belt

Please log in to comment
0
Thanks Chris!

KBOX* has exposed a Sophos issue so we are very thankful for that. As you can see we have way too many versions all over the place. I could click each link and find all the computers at the bottom but I'm lazy. I will look into your idea instead.

*On a separate thing, will brand new people here not know what a KBOX is? What nickname do you use? "Dell KACE K1000 Management Appliance" is a mouthful. What does Marty call the "BOX" to new customers?

Attachment

Answered 06/02/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
I know using SQL isn't as easy as having something built-in to the interface, but you can easily use the following report template for the purpose you're looking for. All you have to do is change the software title (bolded).

SELECT NAME AS 'Machine Name' FROM MACHINE
WHERE NAME NOT IN (SELECT M.NAME FROM MACHINE M JOIN MACHINE_SOFTWARE_JT MSJ ON (MSJ.MACHINE_ID = M.ID)
JOIN SOFTWARE S ON (S.ID = MSJ.SOFTWARE_ID) WHERE S.DISPLAY_NAME like '%Software Title%')
ORDER BY NAME
Answered 06/03/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
I wish it were that easy, however, we have 29 different versions of Sophos Anti-Virus and the software title does not contain the version number. As indicated in Add/Remove Programs and KBOX the Display Version is 9.0.5 for current computers.

I don't know why I can't imbed a jpg into a post here any more but the file attached previously shows the details.
Answered 06/03/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0

SELECT NAME AS 'Machine Name' FROM MACHINE
WHERE NAME NOT IN (SELECT M.NAME FROM MACHINE M JOIN MACHINE_SOFTWARE_JT MSJ ON (MSJ.MACHINE_ID = M.ID)
JOIN SOFTWARE S ON (S.ID = MSJ.SOFTWARE_ID) WHERE S.DISPLAY_NAME like '%Software Title%' AND S.DISPLAY_VERSION = '9.0.5')
ORDER BY NAME


I could even set up a REGEX for you if you need to make sure they are above a specific version (like all systems running 9.0.0 and above Sophos Anti-Virus).
Answered 06/03/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thanks Andy for that report. It takes a while to generate with 9000 computers and 18000 software titles but it is still worth doing once in a while. It seems like too much of a burden to become a filter for a machine label though. Maybe Chris's method of a Custom Inventory Rule will be more efficient. The challenge is to find a registry key or file attribute to search on.
Answered 06/04/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
I use the following code to report on a particular version of software

SELECT DISPLAY_NAME, DISPLAY_VERSION, MACHINE.NAME, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, MACHINE.USER_NAME
FROM MACHINE
LEFT JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
LEFT JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
WHERE DISPLAY_NAME = 'Adobe Flash Player 10 ActiveX'
AND DISPLAY_Version = '10.0.45.2'
Answered 07/21/2010 by: cushy8
Yellow Belt

Please log in to comment
0
Thanks! This is working great for us. We still have some old Macs reporting earlier versions so added some more exclusions at the end for any version beginning with 7, 8, or 1:

SELECT DISPLAY_NAME, DISPLAY_VERSION, MACHINE.NAME, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, MACHINE.USER_NAME
FROM MACHINE
LEFT JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
LEFT JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
WHERE DISPLAY_NAME = 'Sophos Anti-Virus'
AND DISPLAY_VERSION NOT LIKE '9.5.0'

AND DISPLAY_VERSION NOT LiKE '7%'
AND DISPLAY_VERSION NOT LIKE '8%'
AND DISPLAY_VERSION NOT LIKE '1%'
Answered 07/22/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity