Producing a single row matrix using KQL

Question

This is a matrix from a job I have in Databricks. I have cropped out the task names (which usually sits on the left side of the matrix. The green dots show when a task ran successful. I want to recreate this using KQL, however instead of having all of the lines simply have one row where if all of the tasks run successfully on a given day the dot will appear green and if 1 or more fails the dot appears red. I have four columns of data. Time generated, ActionName (whether is passed or failed), table name, and jobId (also would be ideal to change the jobId to something easier to read 'job1'). I have records for each table for many months but only need a 2 week matrix. Many thanks if anyone can help.

user_6336479 · Answer

// Generation of a data sample. Not part of the solution.
let t = materialize (print table_id = range(1,10), dt = range(startofday(ago(20d)), now(), 1d) | mv-expand table_id | mv-expand dt | extend TimeGenerated = todatetime(dt), TableName = strcat("table_",table_id), ActionName  = dynamic(["Passed","Failed"])[iff(rand()<0.9,0,1)] | where rand() < 0.9);
// Solution starts here.
t
| where TimeGenerated >= startofday(ago(14d))
| extend TimeGenerated = format_datetime (TimeGenerated, 'yyyy-MM-dd')
| summarize result_symbol = iff(countif (ActionName == 'Failed') > 0, make_string(128997), make_string(129001)) by TimeGenerated
| evaluate pivot(TimeGenerated, any(result_symbol))

2022-04-14	2022-04-15	2022-04-16	2022-04-17	2022-04-18	2022-04-19	2022-04-20	2022-04-21	2022-04-22	2022-04-23	2022-04-24	2022-04-25	2022-04-26	2022-04-27	2022-04-28

Fiddle

Producing a single row matrix using KQL

1 Answers