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,
MACHINE.ID as TOPIC_ID
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?!
Looks like that's just the way it is :) Thanks Chuck!