azuregroup-bygrafanaazure-application-insightskql

In KQL (Kusto Query Language) can I group by a column (Time) and sum all the other columns containing integer?


I want to produce a table that fits the required format for Time Series data visualization in Grafana Cloud. (more details : https://grafana.com/docs/grafana/latest/panels-visualizations/visualizations/time-series)

customEvents
| where name == "send_editionCO_service" and timestamp > datetime("2024-10-25, 8:00:00.000")
| evaluate bag_unpack(customDimensions)
| extend formsData = parse_json(Forms)
| mv-expand formsData
| extend FormCode = formsData.FormLibelle,
         FormLibelle = formsData.FormCode
| extend Time = bin(timestamp, 1h)
| summarize nbForms = count() by Time, EtatCode, EtatLibelle
| project Time, EtatCode, EtatLibelle, nbForms
| order by Time
| extend index = row_number()
| order by EtatCode, index
| extend total=row_cumsum(nbForms, EtatCode != prev(EtatCode))
| evaluate pivot(EtatLibelle, sum(total))
| project-away EtatCode, nbForms, index;

I run this column and get this result. Result table I want to group by Time and make a sum on all of the other columns. The output would be a table with only the 4 dates and the n other columns summed. --> [Time] / [Column 1] / [Column 2] / [Column 3]


Solution

  • In KQL (Kusto Query Language) can I group by a column (Time) and sum all the other columns containing integer?

    Yes, you can do that using below KQL Query:

    If you want for 1 hr:

    RithTable
    | summarize 
        Colname1 = sum(Colname1), 
        Colname2 = sum(Colname2), 
        ................,
        ................, 
        Colnamex = sum(Colnamex)
    by bin(Time, 1h)
    

    For 1 day, change to bin(Time, 1d).

    Output( based on your input):

    enter image description here

    Fiddle.