Dashboard for ingestion per Table last 7D buckets of 1D

%3CLINGO-SUB%20id%3D%22lingo-sub-2193129%22%20slang%3D%22en-US%22%3EDashboard%20for%20ingestion%20per%20Table%20last%20week%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2193129%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%2C%3C%2FP%3E%3CP%3Eim%20trying%20to%20create%20a%20table%20with%20the%20ingestion%20rate%20of%20the%20data%20per%20table%20for%20the%20last%207%20days%2C%20bucketing%20the%20data%20per%20day.%3C%2FP%3E%3CP%3Eunable%20to%20use%20the%20%22dayofweek%22%20operator%26nbsp%3B%3C%2FP%3E%3CP%3Ehave%20tried%20the%20following%20to%20verify%20the%20output%20but%20the%20data%20and%20days%20are%20not%20aligned%3A%3C%2FP%3E%3CP%3Elet%20dayOfWeek_list%20%3D%20datatable%20(dateNumber%3A%20string%2C%20dateName%3A%20string)%5B%221.00%3A00%3A00%22%2C%20%22Monday%22%2C%20%22%202.00%3A00%3A00%22%2C%20%22Tuesday%22%2C%20%223.00%3A00%3A00%22%2C%20%22Wednesday%22%2C%20%22%204.00%3A00%3A00%22%2C%20%22Thursday%22%2C%20%22%205.00%3A00%3A00%22%2C%20%22Friday%22%2C%20%22%206.00%3A00%3A00%22%2C%20%22Saturday%22%2C%20%2200%3A00%3A00%22%2C%20%22Sunday%22%5D%3B%3CBR%20%2F%3E%3CTABLENAME%3E%3CBR%20%2F%3E%7C%20project%20TimeGenerated%3CBR%20%2F%3E%7C%20extend%20dayNumber%20%3D%20tostring(dayofweek(TimeGenerated))%3CBR%20%2F%3E%7C%20summarize%20count()%20by%20dayNumber%3CBR%20%2F%3E%7C%20join%20kind%3Dfullouter%20(dayOfWeek_list)%20on%20%24left.dayNumber%20%3D%3D%20%24right.dateNumber%3C%2FTABLENAME%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20original%20query%20is%20as%20follows%3A%3C%2FP%3E%3CP%3Elet%20daystoSearch%20%3D%207d%3B%3CBR%20%2F%3Eunion%20withsource%3DTableName1%20*%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(daystoSearch))%20and%20TimeGenerated%20%26lt%3B%20startofday(now())%3CBR%20%2F%3E%7C%20extend%20dayNumber%20%3D%20tostring(dayofweek(TimeGenerated))%3CBR%20%2F%3E%7C%20summarize%20Entries%20%3D%20count()%2C%20Size%20%3D%20sum(_BilledSize)%2C%20estimate%20%3D%20sum(_BilledSize)%20by%20TableName1%2C%20dayNumber%3CBR%20%2F%3E%7C%20extend%20dayName%20%3D%20case(dayNumber%3D%3D%221.00%3A00%3A00%22%2C%22Monday%22%2C%3CBR%20%2F%3EdayNumber%3D%3D%22%202.00%3A00%3A00%22%2C%22Tuesday%22%2C%3CBR%20%2F%3EdayNumber%3D%3D%22%203.00%3A00%3A00%22%2C%22Wednesday%22%2C%3CBR%20%2F%3EdayNumber%3D%3D%22%204.00%3A00%3A00%22%2C%22Thursday%22%2C%3CBR%20%2F%3EdayNumber%3D%3D%22%205.00%3A00%3A00%22%2C%22Friday%22%2C%3CBR%20%2F%3EdayNumber%3D%3D%22%206.00%3A00%3A00%22%2C%22Saturday%22%2C%3CBR%20%2F%3EdayNumber%3D%3D%22%2000%3A00%3A00%22%2C%22Sunday%22%2C%22N%2FA%22)%3CBR%20%2F%3E%7C%20project%20%5B'Table%20Name'%5D%20%3D%20TableName1%2C%20%5B'Table%20Size'%5D%20%3D%20Size%2C%20%5B'Table%20Entries'%5D%20%3D%20Entries%2C%3CBR%20%2F%3E%5B'Size%20per%20Entry'%5D%20%3D%201.0%20*%20Size%20%2F%20Entries%2C%20%5B'GBingest'%5D%20%3D%20(estimate%2F(1024*1024*1024))%2C%20dayName%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2199040%22%20slang%3D%22en-US%22%3ERe%3A%20Dashboard%20for%20ingestion%20per%20Table%20last%207D%20buckets%20of%201D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2199040%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F501739%22%20target%3D%22_blank%22%3E%40OmriPinsker%3C%2FA%3E%26nbsp%3Bmaybe%20try%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Elet%20daystoSearch%20%3D%207d%3B%0Aunion%20withsource%3DTableName1%20*%0A%7C%20where%20TimeGenerated%20between%20(startofday(ago(daystoSearch))%20..%20startofday(now()))%0A%7C%20extend%20dayName%20%3D%20case(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'0.00%3A00%3A00'%2C%20%22Sunday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'1.00%3A00%3A00'%2C%20%22Monday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'2.00%3A00%3A00'%2C%20%22Tuesday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'3.00%3A00%3A00'%2C%20%22Wednesday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'4.00%3A00%3A00'%2C%20%22Thursday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'5.00%3A00%3A00'%2C%20%22Friday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(TimeGenerated)%20%3D%3D%20'6.00%3A00%3A00'%2C%20%22Saturday%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20strcat(%22error%3A%20%22%2C%20dayofweek(TimeGenerated))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%7C%20summarize%20Entries%20%3D%20count()%2C%20Size%20%3D%20sum(_BilledSize)%2C%20estimate%20%3D%20sum(_BilledSize)%20by%20TableName1%20%2C%20dayName%0A%7C%20project%20%5B'Table%20Name'%5D%20%3D%20TableName1%2C%20%5B'Table%20Size'%5D%20%3D%20Size%2C%20%5B'Table%20Entries'%5D%20%3D%20Entries%2C%0A%5B'Size%20per%20Entry'%5D%20%3D%201.0%20*%20Size%20%2F%20Entries%2C%20%5B'GBingest'%5D%20%3D%20(estimate%2F(1024*1024*1024))%2C%20dayName%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eother%20examples%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sentinel%2Fhow-to-align-your-analytics-with-time-windows-in-azure-sentinel%2Fba-p%2F1667574%22%20target%3D%22_blank%22%3EHow%20to%20align%20your%20Analytics%20with%20time%20windows%20in%20Azure%20Sentinel%20using%20KQL%20(Kusto%20Query%20Language)%20-%20Microsoft%20Tech%20Community%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2208534%22%20slang%3D%22en-US%22%3ERe%3A%20Dashboard%20for%20ingestion%20per%20Table%20last%207D%20buckets%20of%201D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2208534%22%20slang%3D%22en-US%22%3EThanks%20Clive%2C%3CBR%20%2F%3Ewhile%20trying%20to%20have%20it%20the%20other%20way%20around%20-%20it%20consumes%20too%20many%20resources%2C%20do%20you%20have%20a%20workaround%20for%20that%3F%3CBR%20%2F%3EDay%201%20Day%202%20Day%203%20........Day%207%3CBR%20%2F%3Etable%201%3CBR%20%2F%3Etable%202%3CBR%20%2F%3Etable%203%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi  ,

im trying to create a table with the ingestion rate of the data per table for the last 7 days, bucketing the data per day.

unable to use the "dayofweek" operator 

have tried the following to verify the output but the data and days are not aligned:

let dayOfWeek_list = datatable (dateNumber: string, dateName: string)["1.00:00:00", "Monday", "​2.00:00:00", "Tuesday", "3.00:00:00", "Wednesday", "​4.00:00:00", "Thursday", "​5.00:00:00", "Friday", "​6.00:00:00", "Saturday", "00:00:00", "Sunday"];
<TableName>
| project TimeGenerated
| extend dayNumber = tostring(dayofweek(TimeGenerated))
| summarize count() by dayNumber
| join kind=fullouter (dayOfWeek_list) on $left.dayNumber == $right.dateNumber

 

my original query is as follows:

let daystoSearch = 7d;
union withsource=TableName1 *
| where TimeGenerated > startofday(ago(daystoSearch)) and TimeGenerated < startofday(now())
| extend dayNumber = tostring(dayofweek(TimeGenerated))
| summarize Entries = count(), Size = sum(_BilledSize), estimate = sum(_BilledSize) by TableName1, dayNumber
| extend dayName = case(dayNumber=="1.00:00:00","Monday",
dayNumber=="​2.00:00:00","Tuesday",
dayNumber=="​3.00:00:00","Wednesday",
dayNumber=="​4.00:00:00","Thursday",
dayNumber=="​5.00:00:00","Friday",
dayNumber=="​6.00:00:00","Saturday",
dayNumber=="​00:00:00","Sunday","N/A")
| project ['Table Name'] = TableName1, ['Table Size'] = Size, ['Table Entries'] = Entries,
['Size per Entry'] = 1.0 * Size / Entries, ['GBingest'] = (estimate/(1024*1024*1024)), dayName

 

5 Replies

@OmriPinsker maybe try

let daystoSearch = 7d;
union withsource=TableName1 *
| where TimeGenerated between (startofday(ago(daystoSearch)) .. startofday(now()))
| extend dayName = case(
                        dayofweek(TimeGenerated) == '0.00:00:00', "Sunday",
                        dayofweek(TimeGenerated) == '1.00:00:00', "Monday",
                        dayofweek(TimeGenerated) == '2.00:00:00', "Tuesday",
                        dayofweek(TimeGenerated) == '3.00:00:00', "Wednesday",
                        dayofweek(TimeGenerated) == '4.00:00:00', "Thursday",
                        dayofweek(TimeGenerated) == '5.00:00:00', "Friday",
                        dayofweek(TimeGenerated) == '6.00:00:00', "Saturday",
                        strcat("error: ", dayofweek(TimeGenerated))
                       )
| summarize Entries = count(), Size = sum(_BilledSize), estimate = sum(_BilledSize) by TableName1 , dayName
| project ['Table Name'] = TableName1, ['Table Size'] = Size, ['Table Entries'] = Entries,
['Size per Entry'] = 1.0 * Size / Entries, ['GBingest'] = (estimate/(1024*1024*1024)), dayName

 

other examples: How to align your Analytics with time windows in Azure Sentinel using KQL (Kusto Query Language) - M...

Thanks Clive,
while trying to have it the other way around - it consumes too many resources, do you have a workaround for that?
Day 1 Day 2 Day 3 ........Day 7
table 1
table 2
table 3

@OmriPinsker 

Sorry I'm not sure I understand how you want this to look, do you just need the Table in the output? i.e 

let daystoSearch = 7d;
union withsource=TableName1 *
| where TimeGenerated between (startofday(ago(daystoSearch)) .. startofday(now()))
| extend dayName = case(
                        dayofweek(TimeGenerated) == '0.00:00:00', "Sunday",
                        dayofweek(TimeGenerated) == '1.00:00:00', "Monday",
                        dayofweek(TimeGenerated) == '2.00:00:00', "Tuesday",
                        dayofweek(TimeGenerated) == '3.00:00:00', "Wednesday",
                        dayofweek(TimeGenerated) == '4.00:00:00', "Thursday",
                        dayofweek(TimeGenerated) == '5.00:00:00', "Friday",
                        dayofweek(TimeGenerated) == '6.00:00:00', "Saturday",
                        strcat("error: ", dayofweek(TimeGenerated))
                       )
| summarize Entries = count(), Size = sum(_BilledSize), estimate = sum(_BilledSize) by TableName1 , dayName, Type
| project ['Table Name'] = TableName1, ['Table Size'] = Size, ['Table Entries'] = Entries,
['Size per Entry'] = 1.0 * Size / Entries, ['GBingest'] = (estimate/(1024*1024*1024)), dayName

 or are you asking for the Days as Columns?  e.g.

let daystoSearch = 7d;
union withsource=TableName1 *
| where TimeGenerated between (startofday(ago(daystoSearch)) .. startofday(now()))
| extend dayName = case(
                        dayofweek(TimeGenerated) == '0.00:00:00', "7. Sunday",
                        dayofweek(TimeGenerated) == '1.00:00:00', "1. Monday",
                        dayofweek(TimeGenerated) == '2.00:00:00', "2. Tuesday",
                        dayofweek(TimeGenerated) == '3.00:00:00', "3. Wednesday",
                        dayofweek(TimeGenerated) == '4.00:00:00', "4. Thursday",
                        dayofweek(TimeGenerated) == '5.00:00:00', "5. Friday",
                        dayofweek(TimeGenerated) == '6.00:00:00', "6. Saturday",
                        strcat("error: ", dayofweek(TimeGenerated))
                       )
| summarize Entries = count(), estimate = sum(_BilledSize)/(1024*1024*1024) by  dayName, Type
| evaluate pivot(dayName, sum(estimate), Type)
| order by Type asc

 

Screenshot 2021-03-15 083137.jpg

 

If I have this wrong, can you supply the KQL that "consumes resources" and a mock up of what  its supposed to look like?


| Table Name | Monday | Tuesday | ..
| AD               | 1GB (from estimate) | 2Gb (from estimate calc!) | etc...

didnt know the Pivot plugin, thank you
another enhancement- if i would like to add at the end of each column the total ingest rate per day, only option is to create an additional query and join them ?

@OmriPinsker 

 

Something like this should work (its a lot slower to run though!)

let daystoSearch = 7d;
let calcBilled = materialize (union withsource=TableName1 *
| where TimeGenerated between (startofday(ago(daystoSearch)) .. startofday(now()))
| project TimeGenerated, _BilledSize, Type
| extend dayName = case(
                        dayofweek(TimeGenerated) == '0.00:00:00', "7. Sunday",
                        dayofweek(TimeGenerated) == '1.00:00:00', "1. Monday",
                        dayofweek(TimeGenerated) == '2.00:00:00', "2. Tuesday",
                        dayofweek(TimeGenerated) == '3.00:00:00', "3. Wednesday",
                        dayofweek(TimeGenerated) == '4.00:00:00', "4. Thursday",
                        dayofweek(TimeGenerated) == '5.00:00:00', "5. Friday",
                        dayofweek(TimeGenerated) == '6.00:00:00', "6. Saturday",
                        strcat("error: ", dayofweek(TimeGenerated))
                       )
);
calcBilled
| summarize estimate = sum(_BilledSize)/(1024*1024*1024) by dayName, Type
| evaluate pivot(dayName, sum(estimate), Type)
| join 
(
calcBilled
| summarize count_ = count(), estimate_ = sum(_BilledSize), sizePerEntryBytes = 1.0 * sum(_BilledSize) / count() by  Type
) on Type
| project-away Type1
| order by Type asc

 
If speed is important, maybe use the Usage table instead?   On my data this is 10x faster (10secs compared to <1second to run)

let daystoSearch = 7d;
let calcBilled = materialize (Usage
| where TimeGenerated between (startofday(ago(daystoSearch)) .. startofday(now()))
| project TimeGenerated, Quantity, DataType   //Note: "quantity" is in MBytes not Bytes in the Usage Table!!
| extend dayName = case(
                        dayofweek(TimeGenerated) == '0.00:00:00', "7. Sunday",
                        dayofweek(TimeGenerated) == '1.00:00:00', "1. Monday",
                        dayofweek(TimeGenerated) == '2.00:00:00', "2. Tuesday",
                        dayofweek(TimeGenerated) == '3.00:00:00', "3. Wednesday",
                        dayofweek(TimeGenerated) == '4.00:00:00', "4. Thursday",
                        dayofweek(TimeGenerated) == '5.00:00:00', "5. Friday",
                        dayofweek(TimeGenerated) == '6.00:00:00', "6. Saturday",
                        strcat("error: ", dayofweek(TimeGenerated))
                       )
);
calcBilled
| summarize estimate = sum(Quantity) by DataType, dayName
| evaluate pivot(dayName, sum(estimate), DataType)
| join 
(
calcBilled
| summarize count_ = count(), estimate_ = sum(Quantity), sizePerMBytes = 1.0 * sum(Quantity) / count() by  DataType
) on DataType
| project-away DataType1
| order by DataType asc