Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community

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

Copper 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