KACE K1000 Smart Label - Smart Label for Latest (or Max) Software Version
I am trying to find a way to create a Smart Label for use with my KACE K1000 that would automatically apply to the latest (or Max) version of software when it is detected. That way I can create another Smart Label for any computer with the software installed, but that is missing the latest version.
I am admittedly not a SQL person, and that looks like it is going to be the only way to create something like this. I know that SQL has the MAX() variable, but I do not see anything similar in the Smart Label Wizard options.
As an example, let's say that I want to find all versions of Adobe Reader detected by my K1000. The SQL statement should looks something like this:
SELECT DISPLAY_NAME, PUBLISHER, SOFTWARE.ID as TOPIC_ID FROM SOFTWARE WHERE ((DISPLAY_NAME like '%Adobe%') AND (DISPLAY_NAME like '%Reader%'))
The question is, how do I then filter the results further, only showing the latest version number or the max value that is shown for DISPLAY_VERSION?
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
chucksteel
6 years ago
See my answer to this question:
That helps to explain some of the issue with version numbers, but unfortunately, the inet_aton trick won't work for Acrobat Reader because the version numbers aren't in a format that will help. Fortunately, Reader version numbers above version 14 do appear to have a consistent 2.3.5 pattern (two digits dot three digits dot five digits). Using the substring_index function we can parse those out to create machine sortable numbers, like this:
SELECT DISPLAY_NAME, DISPLAY_VERSION,
SUBSTRING_INDEX(DISPLAY_VERSION, ".", 1) AS PART1,
SUBSTRING_INDEX(SUBSTRING_INDEX(DISPLAY_VERSION, ".", 2), ".", -1) AS PART2,
SUBSTRING_INDEX(DISPLAY_VERSION, ".", -1) AS PART3
FROM ORG1.SOFTWARE WHERE DISPLAY_NAME like "%Acrobat%Reader%"
HAVING PART1 > 15
ORDER BY PART1 DESC, PART2 DESC, PART3 DESC
Now, we can add a limit to this statement to return just the highest version:
SELECT DISPLAY_NAME, DISPLAY_VERSION,
SUBSTRING_INDEX(DISPLAY_VERSION, ".", 1) AS PART1,
SUBSTRING_INDEX(SUBSTRING_INDEX(DISPLAY_VERSION, ".", 2), ".", -1) AS PART2,
SUBSTRING_INDEX(DISPLAY_VERSION, ".", -1) AS PART3
FROM ORG1.SOFTWARE WHERE DISPLAY_NAME like "%Acrobat%Reader%"
HAVING PART1 > 15
ORDER BY PART1 DESC, PART2 DESC, PART3 DESC
LIMIT 1
Now you can create a smart label to identify machines that don't have that maximum version installed.
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE
JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
JOIN SOFTWARE on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and
DISPLAY_NAME like "%Acrobat%Reader%"
and DISPLAY_VERSION != (SELECT DISPLAY_VERSION
FROM ORG1.SOFTWARE WHERE DISPLAY_NAME like "%Acrobat%Reader%"
HAVING SUBSTRING_INDEX(DISPLAY_VERSION, ".", 1) > 15
ORDER BY SUBSTRING_INDEX(DISPLAY_VERSION, ".", 1) DESC, SUBSTRING_INDEX(SUBSTRING_INDEX(DISPLAY_VERSION, ".", 2), ".", -1)
DESC, SUBSTRING_INDEX(DISPLAY_VERSION, ".", -1) DESC
LIMIT 1)
Note that the query to find the maximum version has been modified because the sub select statement can only return one column.
I hope that helps.
Posted by:
worzie
6 years ago
Adobe is an issue for us too. Because their versions for their products tend to match one another, Reader and Acrobat will be the same version, Flash ActiveX and NPAPI are the same version. So when you are checking for a specific version, it will match the other product and pass the smart label test.
So for years I have been changing the software title to include the version number and make in simpler to track in KACE.
"regedit /s ActiveX64.reg"
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{AC76BA86-7AD7-1033-7B44-AC0F074E4100}]
"DisplayName"="Adobe Acrobat Reader DC - 19.008.20074"
Comments:
-
Thanks Worzie. I will definitely keep that info in the back of my mind. However, it still doesn't get me down the path that I am trying to go.
In order to avoid the issue you were mentioning,
I included two separate Display_Name likes. This filters out the chance of applying the smart label to Acrobat (at least in all testing I have done so far).
KACE seems smart enough to know through patching what the latest version is, and currently with the Display_Version being a numeric value, I would assume (most likely incorrectly) that I should be able to pull the highest value from the list of software that matches the Display_Name criteria. - CodyFLee 6 years ago-
Ah yes, well I can't speak for patching. I manage our updates to 3rd party apps manually. - worzie 6 years ago
