/build/static/layout/Breadcrumb_cap_w.png

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:

select
  MACHINE.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 NameIPLast InventoryFirefox 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)
  
Active Real Machines
ApplicationControlDevices
F/S - Primary All
F/S - Primary Windows
F/S - Windows
F/S/S - All CRP Computers
F/S/S - All CRP Computers - Windows
K2-Imaged Windows
Laptop
Sophos Cloud Installed - Windows
Sophos Cloud Installed - Windows (F/S)
Subnet10dot Wireless

Win - RDP Disabled


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



0 Comments   [ + ] Show comments

Answers (3)

Answer Summary:
Posted by: Hobbsy 1 month ago
Red Belt
0

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


Posted by: kentwest 1 month ago
Second Degree Brown Belt
0

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.

wdLYWKJ9u2QWAAAAABJRU5ErkJggg==

ANrfaf78jJsWAAAAAElFTkSuQmCC

Posted by: kentwest 1 month ago
Second Degree Brown Belt
0

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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ