/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: chucksteel 7 years ago
Red Belt
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"


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 7 years ago

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