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. I have three columns of data. Time generated, ActionName (whether is passed or failed), and table name. I have records for each table for many months but only need a 2 week matrix. Many thanks if anyone can help.
1 Answers
Kind of
// 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"])[toint(rand(2))] | where rand() < 0.9);
// Solution starts here.
t
| extend TimeGenerated = format_datetime (TimeGenerated, 'yyyy-MM-dd')
| evaluate pivot(TimeGenerated, any(iff(ActionName == "Passed", make_string(129001), make_string(128997))), TableName)
| TableName | 2022-04-01 | 2022-04-02 | 2022-04-03 | 2022-04-04 | 2022-04-05 | 2022-04-06 | 2022-04-07 | 2022-04-08 | 2022-04-09 | 2022-04-10 | 2022-04-11 | 2022-04-12 | 2022-04-13 | 2022-04-14 | 2022-04-15 | 2022-04-16 | 2022-04-17 | 2022-04-18 | 2022-04-19 | 2022-04-20 | 2022-04-21 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| table_1 | |||||||||||||||||||||
| table_2 | |||||||||||||||||||||
| table_3 | |||||||||||||||||||||
| table_4 | |||||||||||||||||||||
| table_5 | |||||||||||||||||||||
| table_6 | |||||||||||||||||||||
| table_7 | |||||||||||||||||||||
| table_8 | |||||||||||||||||||||
| table_9 | |||||||||||||||||||||
| table_10 |
Related