Forum Discussion

Copper Contributor

Mar 08, 2021

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

Copper Contributor

Mar 14, 2021

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

CliveWatson

Former Employee

Mar 15, 2021

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

OmriPinsker

Copper Contributor

Mar 15, 2021

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 ?

CliveWatson

Former Employee

Mar 15, 2021

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