CODEWITHSUNDEEP
matrixkqlkusto-explorer
order66
order66

Reputation: 35

Creating a matrix using KQL

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.

A example of the matrix mentioned above

Upvotes: 1

Views: 396

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44971

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 🟩 🟥 🟩 🟩 🟥 🟥 🟥 🟥 🟥 🟥 🟥 🟩 🟥 🟩 🟥 🟥 🟥 🟥 🟥

Fiddle

Upvotes: 1

Related Questions