List machines without certain software titles
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.
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
Answers (8)
Please log in to answer
Posted by:
chrisgrim
14 years ago
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...
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...
Posted by:
RichB
14 years ago
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?
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?
Posted by:
airwolf
14 years ago
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
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
Posted by:
RichB
14 years ago
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.
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.
Posted by:
airwolf
14 years ago
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).
Posted by:
RichB
14 years ago
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.
Posted by:
cushy8
14 years ago
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'
SELECT DISPLAY_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'
Posted by:
RichB
14 years ago
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%'
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%'
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.