How to get e-mail alerts when the amp connection is lost for more than 10 minutes on machines associated to the server lable?
How to get e-mail alerts when the amp connection is lost for more than 10 minutes on machines associated to the server lable?
Where exactly do you insert this statement to implement this alert?
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
GillySpy
11 years ago
Email alerts can only run as often as 15 minutes. So you might be off by as much as 15+10 minutes., but this is how you would do it.
select
M.NAME, M.ID, AMP.DISCONNECT_TIME, CLIENT_CONNECTED
from
MACHINE M
join
MACHINE_LABEL_JT ML ON ML.MACHINE_ID = M.ID
join
LABEL L ON L.ID = ML.LABEL_ID
join
KBSYS.SMMP_CONNECTION AMP ON AMP.KUID = M.KUID
where
CLIENT_CONNECTED = 0
and AMP.DISCONNECT_TIME < date_sub(now(), interval 10 minute)
and L.NAME in ('labelx')
order by AMP.DISCONNECT_TIME
You'll have to add in whatever criteria makes this a server-only request. I've added in a label join to make it easy if you have them all in a label (which I recommend).
EDIT: in rewriting this for the post i mixed up the date logic. fixed now. "for any machine that is disconnected AND has a disconnect time that is older than 10 minutes ago" is what the where clause says now
Comments:
-
Thanks GillySpy, I'm putting this one into effect immediately. - GeekSoldier 11 years ago
-
Where exactly do you insert this statement to implement this alert? - BaumB83 11 years ago
-
Thanks GillySpy, do you know of any way if a machine loses its amp connection it only send 1 email rather than every time the schedule runs if its still down when the next email alert runs? - danpope 11 years ago
-
the alerts run every 15 minutes so to make sure it only runs the first time then add in some logic so that it doesn't run if it's more than 1 interval out
where
CLIENT_CONNECTED = 0
and AMP.DISCONNECT_TIME < date_sub(now(), interval 10 minute)
and AMP.DISCONNECT_TIME > date_sub(now(), interval 30 minute)
and floor(minute(A)/15 ) - floor( minute(B)/15 ) in (0, 1, -1, 3)
and L.NAME in ('labelx') - GillySpy 11 years ago
-
Hi GillySpy sorry sql isn't a strong point of mine the below is what works but keeps alerting every 15 minutes where would I add your latest suggestion?
select M.NAME, SC.CONNECT_TIME, SC.DISCONNECT_TIME, LAST_IP
from MACHINE M
JOIN MACHINE_LABEL_JT ML ON ML.MACHINE_ID=M.ID
JOIN LABEL L ON L.ID=ML.LABEL_ID
LEFT JOIN KBSYS.SMMP_CONNECTION SC ON M.KUID=SC.KUID
WHERE
L.NAME='M_ServerStatus'
and (CLIENT_CONNECTED=0 or SC.KUID IS NULL) - danpope 11 years ago-
i think if you add this to the end:
and SC.DISCONNECT_TIME < date_sub(now(), interval 10 minute)
and SC.DISCONNECT_TIME > date_sub(now(), interval 30 minute)
and floor(minute(SC.DISCONNECT_TIME)/15 ) - floor( minute(now())/15 ) in (0, 1, -1, 3)
Note: there are better (clearer and more succinct) ways to write it but i'm trying to be compatible with older kboxen mysql versions - GillySpy 11 years ago
