For our first query, we'll answer the question "What chats did we have in the past 30 days where the agent sent more than 10 messages?" In the result set we'd like to see widget and topic names, the agent's name, and the number of agent messages and the amount of time the agent spent in the chat.

We'll build this query step-by-step, and talk about key concepts as they come up along the way.

Table Selection

First, we need to identify the table that we'll be querying. Most of our data will be coming from the LiveChats table, so that's where we'll start.

So our initial query is simply the table name, and it looks like this:

LiveChats

📘

Finding the data you need

At first it may be a bit daunting to figure out where to find the data that you need. Pop open the Schema sidebar on the custom reports screen while writing your query for a quick reference of the available tables and fields, or open the MedChat Data Model for a more detailed description of the MedChat data model.

Familiarity with the available tables will come with practice. Feel free to play around in the query editor and see what data is available.

If we run the query, it returns all of the rows from the table for our selected timeframe. Every column for each row returned is included in the result set.

📘

KQL is case sensitive

Note that KQL is case-sensitive, so if you were to try to run the above query as livechats instead of LiveChats, it would return an error.

The editor will help you with casing as you write your query, suggesting properly-cased keywords and table names to use and pointing out any errors.

Filtering Data

Next, we need to filter our data. The screen defaults to returning 7 days' worth of data. I'm going to change mine to 30 days. This sets a time-based outer boundary on the amount of data that can be queried, but we can add additional criteria in the query itself using the where keyword.

The where keyword filters the rows in the current result set to those that match the specified criteria. We want to find all LiveChats records where the agent sent more than 10 messages, so our query with a where filter now looks like this:

LiveChats
| where AgentMessageCount > 10

📘

The pipe operator

You may have noticed the | character at the beginning of the second line in our query. This is called the pipe operator, and it passes (or pipes) the data from one command through to the next. It also makes the query easier to read and understand by adding structure.

Think of your query as a set of sequential operations chained together with |'s, each one manipulating the data and passing it to the next statement, until it's in its final form and ready to be displayed.

Comparisons

In the example, we're checking for AgentMessageCount > 10, but we can do other simple comparisons as well.

Other Criteria

Other KQL functions can be used when specifying criteria in a where statement.

These are just a few of the functions available for use when writing a where statement. Refer to the Microsoft KQL documentation for information on these and other KQL functions.

Combining Criteria

Single criteria in a where statement can be combined to make more complex criteria using common Boolean operators, such as and and or.

For example:

| where WidgetName startswith 'VSP' and AgentMessageCount > 10

Criteria can be grouped to specify order of evaluation using parentheses.

Data Types

Each column in the MedChat data model will be one of a handful of data types. A column's data type determines what kinds of operations can be performed on that field. For example, datetime functions can be used with fields of type datetime, string functions can be used on strings, and so on.

The data types used in the MedChat data model include bool, datetime, guid, int, real, string, and timespan.

The data type for each field in the model is listed on both the Schema panel on the custom reports screen and the MedChat Data Model.

There's quite a bit more that could be said about building out your where statement, but that's enough for now. Let's move on to joining tables.

Joining Tables

In some cases, all of the data you need for your query will be in a single table. In others, you may need to join between two or more tables.

In our example, the LiveChats table is the main table we're querying against, but we'll also need the agent's name, which is in the LiveChatAgents table.

For this we'll perform a join.

LiveChats
| where AgentMessageCount > 10
| join LiveChatAgents on LiveChatId

Here we're joining between the result set produced by the first two lines and the LiveChatAgents table. When performing a join, it's necessary to specify the column that will be used to join rows from the first result set to the second. The common field between LiveChats and LiveChatAgents is LiveChatId.

A join in KQL is similar to a SQL join. There are different types of joins that can be performed (inner, leftouter, fullouter, etc...), but that's outside the scope of this guide.

📘

MedChat naming conventions

The tables in the MedChat data model are mostly grouped by subject and have names that start with the name of the subject. For example, all of the Live Chat tables are prefixed with LiveChat.

In most tables, there will be a key identifier that is named after the table (for example LiveChatId). Often, it's the key identifier from the parent table that is used when joining from one table to another.

Rarely will you have to join across subject areas.

Projection

Our current query returns all columns from both LiveChats and LiveChatAgents tables. Let's limit the result set to just the fields that we need. Specifying the columns to include in a result set is called projection, and we use the project keyword to accomplish this:

LiveChats
| where AgentMessageCount > 10
| join LiveChatAgents on LiveChatId
| project InitiatedDateTime, WidgetName, TopicName, 
    FirstName, LastName, AgentMessageCount, ServiceTime

Go ahead and run the query to see what we've got. The result set should now have only the columns we specified in the project statement.

📘

Breaking statements into multiple lines

When writing the project statement, we could've kept it all on one line. However, since it was getting to be a bit long, we broke it up into two lines, indenting the second line, for better readability.

Breaking a statement up into multiple lines does not change the meaning of the statement. Notice that the second line did not start with a |, so it was still considered all one statement, but it was much easier to read.

Ordering Data

We're almost done. To wrap up, lets order the results first by chat initiated time, and then by agent name, using the order by keyword:

LiveChats
| where AgentMessageCount > 10
| join LiveChatAgents on LiveChatId
| project InitiatedDateTime, WidgetName, TopicName, 
    FirstName, LastName, AgentMessageCount, ServiceTime
| order by InitiatedDateTime asc

When ordering data we can use the asc and desc keywords after the column names to specify whether it should sort on that column in ascending or descending order. The default sort direction in KQL is descending order.

Wrapping Up

Congratulations! You've written your first KQL query. In the next section, we'll build upon what we've learned here and see other ways that you can query your MedChat data.