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]
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):