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?

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

5

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

Answered 04/18/2013 by: GillySpy
Seventh Degree Black Belt

  • Thanks GillySpy, I'm putting this one into effect immediately.
  • Where exactly do you insert this statement to implement this alert?
  • 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?
    • 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')
  • 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)
    • 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
Please log in to comment
Answer this question or Comment on this question for clarity

Share