In this guide, we'll build on some of the concepts covered in Your First KQL Query, and introduce some additional concepts that will be helpful in writing custom report queries.

For this query, we'll be answering the question "How many chats did each of our agents handle last month, and what was their average rating and service time per chat?"

What We're Building

First let's take a look at the query we'll be building:

let endDateTime = startofmonth(now());
let startDateTime = datetime_add("Month", -1, endDateTime);
LiveChats
| where InitiatedDateTime between (startDateTime .. endDateTime)
| join LiveChatAgents on LiveChatId
| join LiveChatSurveys on LiveChatId
| where MessageCount > 0
| summarize ChatsCount = count(), AverageRating = avg(ExperienceRating), 
    AverageServiceTime = avg(ServiceTime) by UserId, FirstName, LastName
| project AgentName = strcat(LastName, ", ", FirstName), ChatsCount, 
    round(AverageRating, 2), format_timespan(AverageServiceTime, "HH:mm:ss")

Here's what the query and results will look like in the app:

Variables

The first two lines of the query define startDateTime and endDateTime variables that will be used to constrain the results to last month. I've set the dropdown on the editor to 90 days as my overall query boundary, but will filter specifically for last month's chats using these two variables.

Variables are created using the let keyword. On the first line of the query, we calculate the endDateTime variable using a couple of datetime functions:

let endDateTime = startofmonth(now());

KQL offers several functions for working with datetimes. The now() function returns the current date and time in Coordinated Universal Time (UTC). The startofmonth() function takes a datetime and returns a datetime representing the start of the month. In the statement above we're setting a variable named endDateTime to the start of the current month. Note that since this is a datetime, it has a date and a time component, so our variable represents the beginning of the first second of the month.

On the second line we calculate the startDateTime variable:

let startDateTime = datetime_add("Month", -1, endDateTime);

Here we're using another KQL datetime function, datetime_add() to perform datetime math. In this case, we specify "Month" as the datetime part, -1 as the value to add, and endDateTime as the datetime to add it to. So, in plain English, we're setting startDateTime to the endDateTime minus one month.

📘

Variables help keep the query tidy

We didn't have to specify the startDateTime and endDateTime on separate lines at the start of the query, but doing so helps to keep the query clean and easier to read than if we had performed the datetime math inline where the variables are actually used.

KQL offers many other handy functions for working with datetimes. Refer to the Microsoft KQL documentation for information on other datetime functions.

The Main Query

With our variables defined, let's start building the main query:

let endDateTime = startofmonth(now());
let startDateTime = datetime_add("Month", -1, endDateTime);
LiveChats
| where InitiatedDateTime between (startDateTime .. endDateTime)
| join LiveChatAgents on LiveChatId
| join LiveChatSurveys on LiveChatId
| where MessageCount > 0

We start with our main table, LiveChats, filtered down to just last month's chats. We do this with a where statement that references the two variables we created:

| where InitiatedDateTime between (startDateTime .. endDateTime)

The between operator matches values that fall inside of the specified range, inclusive.

After filtering down the main table to just the chats we're interested in, we join on LiveChatAgents and LiveChatSurveys.

Finally we filter the results further by excluding any rows where the agent sent zero messages:

| where MessageCount > 0

Aggregation

If we run the query so far, we'd get a row for every chat chat that matches our criteria that includes all of the chat, agent and survey columns. But to answer the question posed at the beginning of the guide, we need chat counts and averages per agent, so we'll need to use aggregation.

Aggregation in KQL is accomplished using the summarize operator coupled with one or more aggregation functions:

| summarize ChatsCount = count(), AverageRating = avg(ExperienceRating), 
    AverageServiceTime = avg(ServiceTime) by UserId, FirstName, LastName

Here we're using two aggregation functions:

• count() - returns the number of records in a result set
• avg() - calculates the average of a given field across the group

These are just two of several aggregation functions available (including countif(), avgif(), min(), max(), etc...). The referenced Microsoft KQL documentation devotes an entire section to aggregation functions.

Note that we specify a name followed by an = and then the aggregate function. For example, ChatsCount = count(). This is called an alias, and it's essentially giving a friendly name to the result of the aggregation function. It's not required, but it makes the results more readable and easier to work with.

The last part of the summarize statement is were we specify what we're grouping by and any non-aggregate fields that should be included in the group. In this case, we want to group by agent and include their name, so we've grouped by UserId, FirstName, and LastName.

📘

Viewing results along the way

Remember that you can run the query at any point along way as you're building it to see your intermediate result set and to verify that there are no errors so far.

Projection With Formatting

If we run the query as it is, we get a result set that is pretty close to what we need, providing some aggregate data for our agents.

Let's clean it up just a bit, though. We don't need the UserId column, and it'd be nice to get the agent's name in the format "Last, First". Also, let's round the average rating, and format the average service time to exclude the fractional seconds.

To accomplish this, we add a project statement and utilize a few more KQL functions at our disposal.

| project AgentName = strcat(LastName, ",", FirstName), ChatsCount, 
    round(AverageRating, 2), format_timespan(AverageServiceTime, "HH:mm:ss")

To get the formatted agent name, we use the strcat() function to perform string concatenation (joining multiple text strings into one) of the LastName and FirstName fields with a , in between them.

We use the round() function to round the average ratings to two decimal places.

The format_timespan() function is useful for formatting the AverageServiceTime into simply hours, minutes, and whole seconds.

Wrapping Up

Our query is now complete.

let endDateTime = startofmonth(now());
let startDateTime = datetime_add("Month", -1, endDateTime);
LiveChats
| where InitiatedDateTime between (startDateTime .. endDateTime)
| join LiveChatAgents on LiveChatId
| join LiveChatSurveys on LiveChatId
| where MessageCount > 0
| summarize ChatsCount = count(), AverageRating = avg(ExperienceRating), 
    AverageServiceTime = avg(ServiceTime) by UserId, FirstName, LastName
| project AgentName = strcat(LastName, ", ", FirstName), ChatsCount, 
    round(AverageRating, 2), format_timespan(AverageServiceTime, "HH:mm:ss")

The results include last month's chat count, average rating, and average service time by agent.