/build/static/layout/Breadcrumb_cap_w.png

Adjusting the Smart Label SQL

Not really a question, but not enough for a blog.

I've noticed while making a machine smart label based on software criteria, that any statement that uses the NOT parameter (!=, does NOT contain, does NOT end with) was giving me a list of everything!

I opened it all up in MYSQL and noticed there was a whole 'extra' WHERE statement section that was doing it's own select 1 statement.  This statement was grabbing EVERYTHING in software in relation to the machine, then passing on version information for that first ID'd software the PC has.

So if I wanted to find PC's with APPRISE that had a version that was NOT 7.3.1604 then I had to remove that whole extra WHERE SELECT 1 statement and knock it back to basics.  Example:

 SELECT MACHINE.NAME AS SYSTEM_NAME, 
SOFTWARE.DISPLAY_NAME as SOFTWARE_NAME,
SOFTWARE.DISPLAY_VERSION as VERSION,
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 (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID)
where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
and (SOFTWARE.DISPLAY_NAME = 'SOFTWARE NAME'
AND SOFTWARE.DISPLAY_VERSION <> 'SOFTWARE VERSION')
and SYSTEM_NAME <> 'EXEMPT PC FROM LABEL'
GROUP BY MACHINE.ID

(I also added the SOFTWARE_NAME and VERSION columns in order to even see the issue).

I write this in the question section because I'm curious is people have seen this for other smart label types (that maybe I should prep for).  The above example can be used if anyone wants to create a smart label for the version of a software.

Also:  Did i make my life harder?  I'm convinced that KACE must've written their wizard SQL this way for a reason, am I doing something wrong?!

 

 


1 Comment   [ + ] Show comment
  • I agree that the way the wizard generates SQL code is weird and I have re-written several smart labels myself. This particular case where you are looking for a specific version of software generally has to be re-written in order for the rule to really work correctly. - chucksteel 10 years ago
    • alright, thank you sir.

      Was hoping this was the case, as that extra select statement from the wizard throws it all out of whack and I immediately blame myself before I blame the appliance :P - Wildwolfay 10 years ago

Answers (1)

Answer Summary:
Looks like that's just the way it is :) Thanks Chuck!
Posted by: Wildwolfay 10 years ago
Red Belt
0

Looks like that's just the way it is :)

 

Thanks Chuck!

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