Using NRQL to detect unacknowledged alerts against open violations

#1

We’ve created a script to alert our staff via SMS when we have a violation that’s been open for more than 15 minutes. We do this by making the following NRQL query:

SELECT timestamp, accountViolationId, label.Name, conditionName, violationUpdateType FROM InfrastructureEvent where violationUpdateType is not null SINCE 7 days AGO limit 1000

We then look at each accountViolationId to see if there are any existing violationUpdateType = ‘opened’ without a corresponding ‘closed’. This is working well.

I’d like to suppress this SMS notifiction once an open violation has had its incident acknowledged but the InfrastructureEvent table does not seem to have any information regarding the state of any alerts issued against violations. Is there another table I can join to get this info? If not, how would you suggest I detect when an alert for an open violation has been acknowledged?

#2

Hi @dougbright, I think you could use something like the solution described here - Relic Solution: 2 Steps to Creating a great Alerts Dashboard

Would that work for you?

#3

Hi,

Thanks for the reply. I’m afraid I’m not sure how the content in that link would help me determine whether an alert is in the acknowledged or unacknowledged state using NRQL.

Ideally there’s a simple lookup where I can see the acknowledged/unacknowledged status of an alert using its accountViolationId.

Thanks,
Doug

#4

Hi @dougbright, You could perhaps use a query like

SELECT latest(timestamp), latest(`current_state`), latest(details),latest(owner), latest(condition_name) FROM Alerts  facet incident_id where `current_state` in ( 'acknowledged') limit 100
#5

That definitely seems promising but even though we had alerts today your query returns nothing. I simplified it down to

SELECT * FROM Alerts since 7 days ago

which is also returning nothing. I’ve attached a screenshot of our alerts from this morning. What am I missing here?

Thanks,
Doug

Screenshot%202021-01-19%20122710
Screenshot 2021-01-19 122710.jpg1627×279 38.4 KB

#6

Hi @dougbright, To be able to use the Alerts event type, you will have to follow the steps described here -

You can also refer this post - List of manually closed incidents? for some upcoming features.