Need Help with SQL/regex -- Report gives different result than Smart Labels
I barely comprehend SQL and regex, but searching and tinkering all week I've come up with this SQL for a report on my K1:
selectMACHINE.NAME AS SYSTEM_NAME,
MACHINE.IP,
LAST_INVENTORY,
S.DISPLAY_VERSION AS Firefox_Version
from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
(OS_NAME like 'Microsoft Windows 10%')
AND (S.DISPLAY_NAME like '%mozilla firefox%')
AND ((MACHINE.NAME like 'westk-%') OR (MACHINE.NAME rlike '(ACU19)|(ACU2)%'))
/* If FF version < 86.6 */
/* AND (S.DISPLAY_VERSION NOT rlike '^((8[6-9])\.[6-9])|^((9.)\.)') */
/* If FF version < 96.0 */
AND (S.DISPLAY_VERSION NOT rlike '^(9[6-9])\.');
The report seems to produce the results I expect; here's a snippet of the report:
| # | System Name | IP | Last Inventory | Firefox Version |
|---|---|---|---|---|
| 1 | ACU19259 | 192.168.1.14 | 10/13/2021 09:20:49 | 60.0.2 |
| 2 | ACU19222 | 10.16.197.81 | 10/19/2021 07:09:56 | 93.0 |
| 3 | ACU19223 | 10.16.224.241 | 03/26/2022 07:37:09 |
60.0.2 |
But notice that line #3. When I run an inventory on that machine, expecting to see my "Needs Newer Firefox" Smart Label to appear:
Labels (13)the Smart Label is not applied. The code for the Smart Label is a straight copy/paste from the report, with no modification.
QUESTION: Why is the report getting the machine, but the Smart Label is not?
COMMENT: i'm pretty sure the SQL WHERE statement could be much more robust, perhaps doing something like "WHERE (in a version number of the format x.y.z, x < a AND y >= c AND I don't care about z)", which would be easy for me in language like Rust or Python or C, but I don't get SQL so well just yet, and regex has always been hard for me, but I figure that's more of a question for the SQL and regex forums, rather than for the KACE forums.
Thanks for any help you can throw my way!
--
Kent
Answers (3)
You need to have the MACHINE.ID as the top item in your SELECT Statement otherwise KACE will not know which machines to add to the label.
select
MACHINE.ID,
MACHINE.NAME AS SYSTEM_NAME,
MACHINE.IP,
LAST_INVENTORY,
S.DISPLAY_VERSION AS Firefox_Version
from MACHINE
select
MACHINE.ID,
MACHINE.NAME AS SYSTEM_NAME,
MACHINE.IP,
LAST_INVENTORY,
S.DISPLAY_VERSION AS Firefox_Version
from MACHINE
(the above change made to both the Smart Label SQL and the Report SQL)
That sounded so promising, but made no difference.
Top Answer
I believe I've found the solution; I removed from the Smart Label definition the semi-colon at the end of the SQL statement. Apparently the reports can handle a terminating semi-colon, but the Smart Labels can not.