KQL, or “Kusto Query Language” is used to process queries in large datasets all across Azure. You can set up your own Azure Data Explorer cluster and use KQL there, but in this article we will focus on using it in querying and analyzing logs in Log Analytics Workspace.
Creating Log Analytics workspace for future log querying with KQL
Go to “Work Analytics Workspaces” in Azure Portal and click “Create”:
Fill the name, resource group and region. Then proceed to “Review + Create”.
After the configuration is validated, click “Create” to request the creation of your Log Analytics workspace.
Once it is finished, you can proceed to the workspace in Azure Portal and go to “Logs”. This is where you will be writing your queries. But for now – there is no data to query, as we didn’t set up any logging channel into that log analytics workspace.
Write Azure AD logs to Log Analytics Workspace
To fill the Log Analytics workspace with data we will configure Azure Active Directory to write logs to it. To write signin logs to the workspace we would need a Azure AD Premium P2 licence, which we got through a free trial. You can get your free 30 days trial of Premium P2 license too. In case you do not have a P2 license and for some reason want to avoid a free trial – you can proceed with learning without signin logs. Simply work on other tables instead.
Go to Azure Portal -> Azure Active Directory -> Diagnostic Settings.
click “add diagnostic setting”:
Check all the boxes for log types, select to send to a Log Analytics Workspace, provide a name of the setting and click “Save”:
You can generate some sign-ins to have some logs for querying via KQL. And start having fun.
Run your first Kusto Query Language Query
Proceed to your Log Analytics Workspaces, and select blade “Logs”. Then – you will see available tables with data that you can query. In the below screenshot there was a table available “AADNonInteractiveUserSignInLogs”. To run your first successful KQL query – simply type the name of a table and click “Run”:
You will receive results of your query. You can expand by clicking the “>” sign to find more fields.
AADNonInteractiveUserSignInLogs was populated the first as it collected logs from a session you were already logged in. Once you or other users perform full sign-ins – the table SigninLogs will get populated. We will proceed with querying also “SigninLogs”, as it is the most commonly queried table.
KQL Commands and pipes
Kusto contains a large number of built in commands like “where” or “count”. Usage of those commands is very similar to pipe-lining commands in Bash or Powershell. You can run any query and pipe it to a next command. You can chain those pipelines many times. Output of previous command lands as an input for the command after the pipe.
SigninLogs | where SourceSystem == 'Azure AD' | count
Kusto Take command
When querying Azure Active Directory logs stored in log analytics by using a kusto query, you can use the “take” command to limit the number of retrieved rows. The first rows that enter the “take” command will be present in the output. You decide how many rows you want to “take” by adding an argument after the command. The below command retrieves 3 records.
SigninLogs | take 3
Kusto Sort command
You can use KQL “Sort” command to choose the order of of retrieved records. Pick a field you want to sort by and use the below examples as a template for your queries
SigninLogs | sort by TimeGenerated desc
SigninLogs | sort by ProcessingTimeInMs asc
Kusto Where command
Use the KQL “where” to limit the results to only records matching a given condition.
SigninLogs | where UserPrincipalName contains "test"
In place of “contains” you can use other common Kusto operators, like:
- “==” for matching exact value
- “contains”, “endswith” and “startswith” for matching just part of the string value
- “in” for matching one of the values. Example:
SigninLogs | where UserPrincipalName in ("John.A@example.com", "Paul.B@example.com")
Kusto And / Or for multiple conditions
You can validate multiple conditions using “And” or “Or” keywords.
SigninLogs | where OperationName == "Sign-in activity" and Location == "US"
SigninLogs | where Location == "US" or IPAddress == "5.120.120.120"
Kusto logs from last X days
You can specify the condition for TimeGenerated property to retrieve logs only from a specified period of time. Use the “Ago” function to dynamically insert a date during the query initiation. The below query retrieves Azure AD logs from last 2 days:
SigninLogs | where TimeGenerated > ago(2d)
Kusto project command
Use the KQL “project” command to display specific columns in the result set. Simply pipe the query to the “project” command and list the properties separated with a coma.
SigninLogs | project Location, AuthenticationRequirement
Kusto Extend command
Use KQL Extend command to keep all the original columns but add a next one defined by you. In the below command we retrieve SignIns of a given user, what apps did he authenticate to, but also – with the extend command – we generate a new column telling us how much time passed since that sign-in.
First we use “where” to limit logs to only one user. Then we use extend to add a “time_left_since” column. We define it as a difference between now and the date of log generation. We push the output to a “project” command to show us only the columns of time_left_since and used apps.
SigninLogs | where UserPrincipalName == "John.D@example.com" | extend time_left_since = now()-TimeGenerated | project time_left_since, AppDisplayName, ClientAppUsed
As shown above, John.D@example.com logged on to Azure Portal using his browser exactly 8 hours and 25 minutes ago.
Kusto Summarize – Kusto Query Language group by replacement
You might be used to “group by” selectors from SQL. In KQL for this purpose you will use a “Summarize” command. The below command will find find signin logs from users and group by the UserPrincipalName
SigninLogs | summarize count() by UserPrincipalName