Using the Kusto Query Language

 

Introduction to KQL Command

The following table contains an overview of the commands, functions, and operators:

Type	Name	Description
Tabular Operators	print	Prints results of a query.
	search	Searches for specific data throughout the logs.
	where	Filters table to a subset of rows that satisfy a comparison.
	take/limit	Returns up to the specified number of rows.
	count( )	Returns the number of records in the input record set.
	summarize	Produces a table that summarizes the content of the selected columns.
	extend	Creates calculated columns and appends them to the result set.
	project	Selects the columns to include.
	distinct	Produces a table with the distinct combination of the provided columns of the input table.
	sort/order	Sorts the rows of the input table into order by one or more columns.
	join	Merges the rows of two tables to form a new table by matching the values of the specified column(s) from each table.
	union	Takes two or more tables and returns the rows of all of them.
	render	Displays the output in a chart, including pie, bar, and line charts, among others.
Query Statements	let	Creates variables.
Scalar Functions	ago ( )	Subtracts the given timespan from the current UTC clock time.
	bin ( )	Rounds values down to an integer of a given size.
String Operators	operators	Not a command but a discussion of operators to use with the where command.

 

 Tabular Operators

It can produce data in a mixture of tables and rows where each tabular operator can pass its results from one command to another with the help of the pipe delimiter.

• The print command

It can easily print the results of a simple query and is useful to determine the way in which commands works and about the expected output. 

• The search command

To use this command you have to look at the table in the log which will return all the columns as well as rows of the table to the maximum level of your application's capability.

• The where command

Whenever you only want to search a subset of the rows in a particular table, you can use where operator which can easily filter the rows that are returned according to the situation.

• The take/limit command

If you want a random sample of a data to be returned to get an idea about the data before going for a larger data, you can use take command which will return a specified number of rows, but there is no guarantee about which row will be returned to get a better sampling. limit is just another name for take command and these are very useful to define your queries.

• The count command

If you want to know the number of rows your query will return, you can use this command. It will take less time as compared to a basic search command, however, the time taken to perform a query may vary, but still it will be faster to simply get a count of the number of rows than to look at all the rows.

• The summarize command

Whenever you require the total values for a specific grouping of rows, you can use this command.

• The extend command

This command permits you to create new columns from the existing ones, or other data, like hardcoded values and is very useful to output the information that may exist in multiple columns but you want it to be shown in a single column.

• The project command

This command can specifically determine what columns to show if you don't want to look through all the columns that a query normally shows. It can also be useful for cleaning up the results by removing those columns you don't care about.

• The distinct command

If you want to look for only one among the multiple instances of the same value returned to you, this command can be used which can return only the first instance of a collection of the specified column(s) and ignores all the rest. This command is very helpful in building up of your query.   

• The sort/order command

While running a query, by default, the rows will always be returned in the same order that they were previously saved into the table you were querying, but, if you require these values in some particular order like alphabetical or based on time value, you can use this command to sort the output into any order you need.

• The join command 

This command can merge the columns from two different tables into one and it will be needed to get information from multiple tables or the same table with the help of different parameters.

• The union command

It can combine two or more tables into one. While join can have all the columns of the matching rows in one row, union can show one row for each row in each of the tables. Hence, if table 1 has 10 rows and table 2 has 12 rows then, then the union table will have 22 rows. 

• The render command

This command is used to present your data output in a graphical format instead of a tabular one as sometimes it is much easier to look at a time chart or a bar chart rather than a list of strings and numbers. This command will make it easier to represent your results in a graphical way.  

Query Statement

It can make tables that are useful in other parts of the query and must end in semicolon (;). It can return the entire tables that are all returned by the query. 

• The let statement

It can help you to make a new variable to be used in later computations including another table if required.

Scalar Functions

They take a value and manipulate it to return a different value. They are very helpful to convert data types, looking at only part of the variable, and performing mathematical computations.

• The ago() function

It can subtract a particular time space from the current UTC time.

• The bin() function

It can take a value, round down as required, and then keep it into a virtual bin of a specified size and it can be generally used with summarize by so that you can group the values into more manageable sizes, for example, taking time taken by an activity during a day and then group it into the 24 hours a day.  

String Operators

String as well as numeric operators can be used in contrast of a where clause. You will have to do a case-insensitive comparison like ABC =~ abc to make it true instead of ABC = abc which is false and it is a good practice to do a case-insensitive comparison.