Forum Discussion

OmriPinsker's avatar
OmriPinsker
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 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

 

  • CliveWatson's avatar
    CliveWatson
    Icon for Microsoft rankMicrosoft
    Mar 10, 2021

    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

    • OmriPinsker's avatar
      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's avatar
        CliveWatson
        Icon for Microsoft rankMicrosoft
        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

         

         

        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...

Resources