Kusto make-series by month

Question

I'm really struggling to figure out how to use the Kusto make-series function but output the results by month. The current example below is set to 1d (i.e. 1 day).

I understand that month and year is not a valid operator for timespan, so looking for a way around this.

let startDateTime = datetime(2022-04-13T08:25:51.000Z);
let endDateTime = datetime(2022-04-19T21:25:51.876Z);
let m = materialize(traces | where timestamp > startDateTime and timestamp < endDateTime
| extend Message=parse_json(message)
| extend LogLevel = Message.LogLevel,
LogEventCategory = Message.LogEventCategory,
LogEventType = Message.LogEventType,
LogEventSource = Message.LogEventSource,
LogData = Message.LogData,
LogUserId = Message.LogUserId,
LogUsername = Message.LogUsername,
LogForename = Message.LogForename,
LogSurname = Message.LogSurname,
LogCountry = Message.LogCountry,
LogRegionName = Message.LogRegionName,
LogCity = Message.LogCity,
LogZip = Message.LogZip,
LogLatitude = Message.LogLatitude,
LogLongitude = Message.LogLongitude,
LogIsp = Message.LogIsp,
LogIpAddress = Message.LogIpAddress,
LogMobile = Message.LogMobile);
m | where Message.LogLevel == 'Information' | where Message.LogEventCategory == 'WebApp-CLIENT'
| make-series counter=count() default=0 on timestamp in range(startDateTime, endDateTime, 1d); // Need to define 1month, NOT 1d

Answer 1

can you use the summarize operator instead of make-series? that would allow you to count by startofmonth(datetime_column_name)

for example:

click to run

range dt from ago(365d) to now() step 1d
| extend month = startofmonth(dt)
// the following line skips a few months, for the purpose of the example
| where month !in(datetime(2022-03-01), datetime(2022-01-01), datetime(2021-10-01), datetime(2021-09-01), datetime(2021-08-01))
| summarize count() by month
| render columnchart

alternatively, if that doesn't meet your scenario - you can create the list of all months between the minimum & maximum values of your datetime column, and perform an outer join between that and the summarize above

for example (this outputs the exact same column chart as shown above):

let T = range dt from ago(365d) to now() step 1d;
let min_max = toscalar(T | summarize pack_array(min(dt), max(dt)));
let min = todatetime(min_max[0]);
let max = todatetime(min_max[1]);
T
| extend month = startofmonth(dt)
// the following line skips a few months, for the purpose of the example
| where month !in(datetime(2022-03-01), datetime(2022-01-01), datetime(2021-10-01), datetime(2021-09-01), datetime(2021-08-01))
| summarize count() by month
| join kind = rightouter (
    range dt from min to max step 20d
    | summarize by month = startofmonth(dt)
) on month
| project month = coalesce(month, month1), count_ = coalesce(count_, 0)
| render columnchart  

Answer 2

I had already accepted an answer on this but later came up with another alternative which I've tested with the following intervals:

startofyear startofmonth startofweek startofday

Just one side note, startofweek assmes Sunday with int value 0, based on some feedback from comments below i've updated the code with 'startofweek(s-1d)+1d', the example below reflects this with the commented out notes so you can see where i placed this:

let startDateTime = datetime(2022-01-1T08:00:00.000Z);
let endDateTime = datetime(2022-04-21T08:00:00.000Z);
let m = materialize(traces | where timestamp > startDateTime and timestamp < endDateTime
| extend Message=parse_json(message)
| extend LogLevel = Message.LogLevel,
LogEventCategory = Message.LogEventCategory,
LogEventType = Message.LogEventType,
LogEventSource = Message.LogEventSource,
LogData = Message.LogData,
LogUserId = Message.LogUserId,
LogUsername = Message.LogUsername,
LogForename = Message.LogForename,
LogSurname = Message.LogSurname,
LogCountry = Message.LogCountry,
LogRegionName = Message.LogRegionName,
LogCity = Message.LogCity,
LogZip = Message.LogZip,
LogLatitude = Message.LogLatitude,
LogLongitude = Message.LogLongitude,
LogIsp = Message.LogIsp,
LogIpAddress = Message.LogIpAddress,
LogMobile = Message.LogMobile);
m | where Message.LogLevel == 'Information' | where Message.LogEventCategory == 'WebApp-CLIENT'
| summarize Count=count() by timestamp=startofweek(timestamp-1d)+1d // so week starts on Monday
| join kind=rightouter (
        range s from startDateTime to endDateTime step 1d 
        | summarize by timestamp = startofweek(s-1d)+1d // so week starts on Monday
    ) on timestamp
    | project timestamp=coalesce(timestamp, timestamp1), Count = coalesce(Count, 0)

Next example using startofmonth

let startDateTime = datetime(2022-03-20T20:00:00.000Z);
let endDateTime = datetime(2022-04-20T21:00:00.000Z);
let m = materialize(traces | where timestamp > startDateTime and timestamp < endDateTime
| extend Message=parse_json(message)
| extend LogLevel = Message.LogLevel,
LogEventCategory = Message.LogEventCategory,
LogEventType = Message.LogEventType,
LogEventSource = Message.LogEventSource,
LogData = Message.LogData,
LogUserId = Message.LogUserId,
LogUsername = Message.LogUsername,
LogForename = Message.LogForename,
LogSurname = Message.LogSurname,
LogCountry = Message.LogCountry,
LogRegionName = Message.LogRegionName,
LogCity = Message.LogCity,
LogZip = Message.LogZip,
LogLatitude = Message.LogLatitude,
LogLongitude = Message.LogLongitude,
LogIsp = Message.LogIsp,
LogIpAddress = Message.LogIpAddress,
LogMobile = Message.LogMobile);
m | where Message.LogLevel == 'Information' | where Message.LogEventCategory == 'WebApp-CLIENT'
| summarize Count=count() by timestamp=startofmonth(timestamp)
| join kind=rightouter (
        range s from startDateTime to endDateTime step 1d 
        | summarize by timestamp = startofmonth(s)
    ) on timestamp
    | project timestamp=coalesce(timestamp, timestamp1), Count = coalesce(Count, 0)

From the help and answers given by others, it seems there's various different ways in answering the question. I'm not claiming to be an expert by any means and there may be better solutions, but from my own testing above, I seem to getting the desired results now. Thanks to everyone for their input.

Answer 3

I just realized that there is an additional way.

1. Create make-series with step of 1d, but for the on clause, instead of using dt (the datetime field in my example) use startofmonth(dt).

   This will have the same effect as adding | extend dt = startofmonth(dt) before the "standard" make-series -
   The summarization of the data will be done for the 1st of every month and every other day will have no data (see image at the bottom).

2. Use mv-apply to filter out from the series all days except for the 1st day of every month.

   Theoretically we can skip the use of make_list and just leave the series exploded, but then we need to do some other manipulations, including defining the xcolumn in the render operator.

---

// Generation of a data sample. Not part of the solution.
let t = materialize(range i from 1 to 1000 step 1 | extend dt = ago(365d*rand()) | where getmonth(dt) !in (3,6,9,10,11));
// The solution starts here.
t
| make-series count() on dt = startofmonth(dt) step 1d
| mv-apply count_, dt to typeof(datetime) on (where dt == startofmonth(dt) | summarize make_list(count_), make_list(dt))
| render timechart 

Fiddle

---

This is how it looks without the mv-apply part (on a different random data sample).
Please note that all the data is aggregated for the 1st days of the months.