Cannot generate alert

I need an alert which could create an alert if I cannot received messages include completed successfully during 7:30 to 9:30 am every day. Please find out my query.

SELECT count(*) from Log where hostname like ‘svccb2000001pr.nbndc.local’ where message like ‘%DLY_PROVBWTRK_PR.DLY_PROVISIONING_BW_TRACKING_PROD%’ and message like ‘%completed successfully%’ where hourOf(timestamp) > ‘07:00:00’ and hourOf(timestamp) < ‘10:00:00’ with timezone ‘Australia/Sydney’

Thank you very much!

Kind regards,

Hi, @yutingzhao: In which account ID are you executing this query?

Hi Philwber, account ID is 2325552, please find the link https://onenr.io/0VwgWBvrpwJ

For some reason it doesn‘t like WHERE hourOf(timestamp) < '10:00', but this seems to work:

SELECT count(*) 
FROM Log 
WHERE hostname LIKE 'svccb2000001pr.nbndc.local' 
  AND message LIKE '%DLY_PROVBWTRK_PR.DLY_PROVISIONING_BW_TRACKING_PROD%' 
  AND message LIKE '%completed successfully%' 
  AND hourOf(timestamp) IN ('7:00', '8:00', '9:00') 
WITH TIMEZONE 'Australia/Sydney'

You should also configure the condition to close incidents on loss of signal. Please see this post for more information: https://discuss.newrelic.com/t/relic-solution-how-can-i-figure-out-when-to-use-gap-filling-and-loss-of-signal/120401

1 Like

If you set this alert condition to violate when the query returns 0 and the message does not appear until, say, 9:00, it will start violating at 7:00. I think you need to use this workaround to save the count since 1 day ago as a custom event, and query that event: https://discuss.newrelic.com/t/relic-solution-extending-the-functionality-of-nrql-alert-conditions-beyond-a-single-minute/75441.

2 Likes

Hi Philweber,

Thank you very much for your suggestions! I will check those links and modify alert. If there does not work, I will let you know.

1 Like