I have some rows of data being actively logged to Application Insights. I would like to take the number of rows logged from the beginning of today till now and compare that to the average for the same time span in the last 7 days.
Lets assume that it is 2PM right now. That means I would like to take the number of all the rows which have timestamp inside the range from the beginning of a day of the 7 days ago included till the beginning of the day today not included. The filter it down to only rows which have timestamp before 2PM including and then divide the number by 7.
I just thought if there is an easy way to do it in Kusto?
Here is what I did so far:
| where startofday(ago(7d)) <= timestamp and timestamp < startofday(now())
| where startofday(timestamp) <= timestamp and timestamp <= (startofday(timestamp) + (now() - startofday(now())))
| summarize AverageValue = count()/7 by Color
Lets assume that it is 2PM right now. That means I would like to take the number of all the rows which have timestamp inside the range from the beginning of a day of the 7 days ago included till the beginning of the day today not included. The filter it down to only rows which have timestamp before 2PM including and then divide the number by 7.
If I understood your description correctly, you could try something like this:
T
| where timestamp between(startofday(ago(7d))..startofday(now())
| where datetime_part("Hour", timestamp) <= datetime_part("Hour", now())
| summarize result = count() / 7