Need help with the NR insights query to create alert

i have the following records pushed to my custom event

{Transactionid :50,Status:Initiated},{Transactionid :50,Status:Validated},
{Transactionid :50,Status:Charged},{Transactionid :50,Status:Completed }

can you help me to generate query to make sure that every transaction id has four status so i can configure alert on this,
also getting duration from initiated status and completed

Hey @anaguib,

Would you be able to share a little more information about this custom event? i.e. the event name and attributes

You could FACET on duration to receive it for each of the statuses, but setting up a single alert condition for all four of these statuses would be problematic if they each had drastically different durations.


Hello ,
event name is “CompensationService”

“event”: {
“AgentEmail”: "",
“Amount”: 1.5,
“Brand”: “xxx”,
“CompensationTransactionId”: 198,
“Country”: “USA”,
“CustomerEmail”: "",
“CustomerGuid”: “0000000000000”,
“CustomerId”: 1111,
“CustomerMobile”: “xxxxxxx”,
“OrderId”: 1111,
“ReasonId”: 3,
“Status”: “Completed”,
“timestamp”: 1593380299887

so any CompensationTransactionId for example 198 should have 4 records with these status


i really want to alert me if one transaction has lower than 4 statuses

Hey @anaguib

A query like below would help you to show the total count of status messages per transaction ID… I am struggling to think of a way to alert on that though. Especially considering that seems to be quite infrequently sent data… I’m going to tag in @zahrasiddiqa who might be able to help with that part?

SELECT uniqueCount(Status) FROM CompensationService SINCE 3 DAYS AGO FACET CompensationTransactionId LIMIT 30 

1 Like

Hey @anaguib, Alert conditions work best with facet clause, if there is continuous data reporting.

When using FACET , if there is no data reporting, Alerts cannot see any valid facets; the empty results are treated as NULLS instead. This will lead the violations to become stuck, as the empty results returned from the query cannot be evaluated.

However, you should be able to workaround this issue by making one of the following changes to your alert.

  • Set your alert to have at least one facet case reporting data continuously- Because there will always be events reporting across different facets, Alerts will be able to evaluate the absence of data from facets and add in zeroes instead.

  • Create an alert with the query provided by @RyanVeitch - But you’d have to set the violation time limit on the alert condition so that the violations are not left stuck in open state.

  • Remove the FACET clause - By removing the FACET clause from your query, the condition will no longer be evaluating events based on what facets are present. Instead, it will add synthetic zeroes when there are no events to count. The trade-off for this is that the alert will not immediately tell you which CompensationTransactionId is alerting. You can always use the original query (with the FACET included) in Insights to find which CompensationTransactionId is alerting after receiving the alert notification.

More information on this is documented here:

1 Like