Forum Discussion
OmriPinsker
Mar 08, 2021Copper Contributor
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...
CliveWatson
Mar 10, 2021Former Employee
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
- OmriPinskerMar 14, 2021Copper ContributorThanks 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- CliveWatsonMar 15, 2021Former Employee
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...- OmriPinskerMar 15, 2021Copper Contributordidnt 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 ?