Search code examples
kql

Kusto make-series by month

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

enter image description here

enter image description here

Solution

  • 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

    enter image description here

    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