Configure alerting using infinity & tabular data

[sanjayastn]

Hi All,

I’m have a managed grafana instance(no prometheus,loki etc)
I have a datasource created from infinity which connects to Business Central application api and gets me a series of jobs queues. In a table view data will be shown in 2 columns like below

Actual data in the value column(just a single entry),

[{"@odata.etag":"W/\"jakgfkjdlahflifsuieuriWFKELFJ=\"","Description":"Test Job","Earliest_Start_Date_Time":"2025-05-09T00:00:00Z","Ending_Time":"00:00:00","ID":"645g29blk-06tf-49h2-ba78-35656540et03v","Job_Queue_Code":"Test","Minutes_between_Runs":5,"Object_Caption_to_Run":"Test your job","Object_ID_to_Run":5555,"Object_Type_to_Run":"xxxxx","Parameter_String":"","Recurring_Job":true,"Run_on_Fridays":true,"Run_on_Mondays":true,"Run_on_Saturdays":true,"Run_on_Sundays":true,"Run_on_Thursdays":true,"Run_on_Tuesdays":true,"Run_on_Wednesdays":true,"Scheduled":true,"Starting_Time":"02:00:00","Status":"Ready","Timeout":"P0DT6H0M0.0S","User_ID":"test","User_Session_Started":"0001-01-01T00:00:00Z"}]

I need creating alerts to identify the jobs that have the Status==‘Error’, to do that I have to set the selector as ‘value’ and then use a filter option

then I will get something like below where I can alert on,

But the problem here is the rows are been duplicated for some reason (Only in alerting, no duplicates observed in explore and in dashboard creation views). So I have to put a summarize option ‘count(ID)’ where it will aggregate and only give me a count but I cannot provide any details in the alert that is generated.

I can only query data using the type ‘JSON’ and I tried using UQL but gives no data in the alerting view but this query works in the explore view,

parse-json
| project "value"
| where "Status" == 'Ready'

My Questions are,

1. Can I get the data without duplicates in alerting?
2. How can I alert with more details in the alert?
3. I want to perform more queries on data to create alerts, eg: Get the jobs that are in Ready state but have not executed today(To check if the job has not run today)

Your help will be appreciated, thank you

[yesoreyeram]

UQL doesn’t support backend operations such as alerting, public dashboards, sql expressions and query caching. You have to use Backend parser which is based on jsonata style syntax to get this working with alerts.

BTW, you can only alert on numeric values. Say for example, You can’t alert on list of all the failed jobs. But you can alert count of failed jobs greater than 0

Play example with filter and jsonata where I am getting 19 rows.

The same query in the alerts tab

[sanjayastn]

Hi @yesoreyeram, thank you for the reply.
Understood, yes this can make an alert, but then how can I get the details of failed jobs in the alert notification? Is it possible to do?

[yesoreyeram]

Right.. Did further analysis and found a solution for your case..

First of all, Alert will work only on numeric data or timeseries data. Alerting not possible with tabular data.

Regarding duplicate data: In the example I shared earlier, there are 19 rows returned where there are 4 numeric fields in each row. Alerting trying to convert this tabular data into numeric data as it found 4 numeric fields. For 4 numeric field * 19 rows, alerting returns 76 rows. So this appears to be duplicate but it is not. All the string fields goes like labels.

So the solution is to convert all the fields into string fields and have only one numeric field. JSONata example can be found here

Updated play example can be seen here.

URL & JSONata

URL: https://services.odata.org/v4/(S(h1u2l5j3jswbcmbbapn02xrf))/Northwind/Northwind.svc/Orders

JSONata:

$map($.value[ShipCountry='France'],function($item, $item_index){
    $merge(
        [
            $map($keys($item),function($key){{
                $key: $type($lookup($item,$key)) != "string" ? $string($lookup($item,$key)) : $lookup($item,$key)
            }}),
            {"__index": $item_index + 1}
        ]
    )
})

Query in explore

Configuring Alerts

Alert state

Alert email with details

[sanjayastn]

Ohh wow! This is the exact solution I was looking for. Thanks you very much