Forum Discussion
Dashboard for ingestion per Table last 7D buckets of 1D
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...
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) - Microsoft Tech Community
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
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
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)), dayNameor 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 ascIf 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 ?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