How do I get my Smart Label SQL to pick machines who have been up for 10 or more days, but does not have a specific label attached?
In the end, this label is attached to an alert to notify the user to reboot the machine, because Kace has reported its uptime as being more than 10 days; this part works. However, I want it to exclude all servers and machines that I put into a label called "Exempt From Needs Reboot." The machines I want excluded have both "Needs Reboot" and "Exempt From Needs Reboot." What am I missing?
SELECT
*,
MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID as TOPIC_ID
FROM
MACHINE
WHERE
((left(UPTIME, locate(',', UPTIME) -1) > 10))
AND (OS_NAME not like '%Server%')
AND ((not exists (
select 1 from
LABEL,
MACHINE_LABEL_JT
where
MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
AND LABEL.TYPE <> 'hidden'
and LABEL.NAME = 'Exempt From Needs Reboot'
)))
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
I think you want LABEL.NAME != 'Exempt From Needs Reboot'
You can also do something like this:
SELECT
*,
MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID as TOPIC_ID
FROM
MACHINE
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
WHERE
((left(UPTIME, locate(',', UPTIME) -1) > 10))
AND (OS_NAME not like '%Server%')
AND LABEL.NAME != "Exempt From Needs Reboot"
Comments:
-
The one I posted did work, but the reason I posted is because the following is the original and it wasn't working properly. Is it because I didn't have the * at the beginning? Is that first part required to state select everything, and the following in the select part selects from what is there? I thought they were identical, besides the * not being there; I just changed the format to look like traditional SQL layout. Thank you for the help. I didn't realize it was working until today.
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE ((left(UPTIME, locate(',', UPTIME) -1) > 10)) AND (OS_NAME not like '%Server%') AND ((not exists (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'Exempt From Needs Reboot')) ) - davidjblountTSC 8 years ago
