/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


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?

10/24/2016 1075 views
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

Comments


All Answers

0
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"

Answered 10/25/2016 by: chucksteel
Red Belt

  • 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')) )