A few months ago I shared a tweet with a few quick links for learning about Kusto Query Language (KQL) and Azure Log Analytics.  Since that time Azure Sentinel (which sits of top of Azure Log Analytics) has been released to general availability (GA).  In this post I’ll build on that tweet and share a number of resources for starting out with Azure Sentinel / Azure Log Analytics and KQL.

Before you continue with this post I highly recommend reading MVP Tobias Zimmergren’s post on Monitoring Office 365 tenants with Azure Sentinel.

Monitoring Office 365 tenants with Azure Sentinel
https://zimmergren.net/use-azure-sentinel-to-digest-office-365-audit-logs/

Background

Most Microsoft cloud services emit logs for audit, operational, etc. purposes.  These logs are useful for gaining insights into who is using the service and how they are using it but sometimes it is not always easy to query these services.  You might be restricted to only a few thousands records at a time, a limited set of filters, or other constraints.  Azure Log Analytics and KQL make it possible to query a large number of records (in my experience millions to hundreds of millions) in a short time period (seconds in most cases instead of minutes or hours).

Kusto Query Language (KQL)

Over the years I’ve used T-SQL to query SQL Server when needed, but I am by no means an expert in the T-SQL language or concepts.  Having a basic understanding of T-SQL did make it easier for me to understand the entry level concepts of KQL such as filtering, ordering, grouping, and more.  Intermediate to advanced concepts like time based aggregations or self referential queries took a little more time to understand but my Data & AI PFE peer Ken Kilty provided a lot of good advice in this space.

Kusto Query Language overview
https://docs.microsoft.com/en-us/azure/kusto/query/

SQL to Kusto query translation
https://docs.microsoft.com/en-us/azure/kusto/query/sqlcheatsheet

Limiting Costs

Azure Sentinel (and by proxy Azure Log Analytics) is charged in 2 ways:

• Ingestion of data
  • Reserved capacity
  • -or-
  • Per GB
• Retention of data

Azure Sentinel pricing
https://azure.microsoft.com/en-us/pricing/details/azure-sentinel/

For all Office 365 data the ingestion of data is free.  Azure AD audit logs and sign-in logs will be charged according to the reserved capacity or pay-as-you-go per GB model.

Retention of data in an Azure Sentinel enabled workspace is free for the first 90 days.  Beyond the first 90 days pricing is per GB per month.

Ex. Storing Office 365 logs for 9 months, a customer would only be charged for (9 months – 3 free months) = 6 paid months.

Azure Monitor pricing
https://azure.microsoft.com/en-us/pricing/details/monitor/

If you are looking to test out the service “for free” it is possible to configure an Azure Sentinel enabled workspace to ingest Office 365 data and limit the retention of data to < 90 days.  Once you get comfortable with the data schema and writing queries you can increase the retention period.

Sample Queries

In the course of working with customers on monitoring their Office 365 environments I and my teammates have developed a number of KQL queries to find “interesting” data points.  I’ve shared these queries in the following GitHub repo.  Note that these sample queries are provided as-is with no warranty.  If you have any queries of your own that you would like to contribute feel free to submit a pull request (or open an issue) to the repo for review.

Office 365 and Azure AD sample KQL Queries
https://github.com/BrianTJackett/log-analytics-samples

Conclusion

Last year I had never heard of Log Analytics, Azure Sentinel, or KQL.  This year I am seeing it pop up in so many places inside and outside of Microsoft.  I am very eager to see where these technologies go and spread the word about them.  I truly see interesting problems that can be solved with enough data and the right query.  Hopefully this post will give you a nudge in the right direction to start (or continue) looking at these technologies.

-Frog Out