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?!

 

 

Answer Summary:
Looks like that's just the way it is :) Thanks Chuck!
Cancel
1 Comment   [ + ] Show Comment

Comments

  • 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.
    • 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
Please log in to comment

Answers

0

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

 

Thanks Chuck!

Answered 09/16/2013 by: Wildwolfay
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity