This blog post demonstrates a hybrid end-to-end monitoring solution integrated with Microsoft Sentinel and Azure Monitor for ingesting streamed and batched logs from diverse sources, on-premises, or any cloud, within an enterprise ecosystem.

Architecture

Workflow

1. Combine features provided by Microsoft Sentinel and Azure Monitor with Azure Data Explorer to build a flexible and cost-optimized end-to-end monitoring solution. Below are some examples:
   1. Use Microsoft Sentinel as a SIEM and SOAR component in the overall monitoring solution where you can ingest security logs from firewalls, Defender for Cloud, and so on. SIEM is short for security information and event management, whereas SOAR is short for security orchestration, automation and response.
   1. Use Azure Monitor’s native capabilities for IT asset monitoring, dashboarding, and alerting so you can ingest logs from VMs, services, and so on.
   1. Use Azure Data Explorer for full flexibility and control in all aspects for all types of logs in the following scenarios:
      1. No out of the box features provided by Microsoft Sentinel and Azure Monitor SaaS solutions such as application trace logs.
      1. Greater flexibility for building quick and easy near-real-time analytics dashboards, granular role-based access control, time series analysis, pattern recognition, anomaly detection and forecasting, and machine learning. Azure Data Explorer is also well integrated with ML services such as Databricks and Azure Machine Learning. This integration allows you to build models using other tools and services and export ML models to Azure Data Explorer for scoring data.
      1. Longer data retention is required in cost effective manner.
      1. Centralized repository is required for different types of logs. Azure Data Explorer, as a unified big data analytics platform, allows you to build advanced analytics scenarios.
2. Query across different products without moving data using the Azure Data Explorer proxy feature to analyze data from Microsoft Sentinel, Azure Monitor, and Azure Data Explorer in a single query.
3. To ingest logs with low latency and high throughput from on-premises or any other cloud, use native Azure Data Explorer connectors such as Logstash, Azure Event Hub, or Kafka.
4. Alternatively, ingest data through Azure Storage (Blob or ADLS Gen2) using Apache Nifi, Fluentd, or Fluentbit connectors. Then use Azure Event Grid to trigger the ingestion pipeline to Azure Data Explorer.
5. You can also continuously export data to Azure Storage in compressed, partitioned parquet format and seamlessly query that data as detailed in the Continuous data export overview.

Components

• Azure Event Hub: Fully managed, real-time data ingestion service that’s simple, trusted, and scalable.
• Azure IoT Hub: Managed service to enable bi-directional communication between IoT devices and Azure.
• Kafka on HDInsight: Easy, cost-effective, enterprise-grade service for open source analytics with Apache Kafka.
• Azure Data Explorer: Fast, fully managed and highly scalable data analytics service for real-time analysis on large volumes of data streaming from applications, websites, IoT devices, and more.
• Azure Data Explorer Dashboards: Natively export Kusto queries that were explored in the Web UI to optimized dashboards.
• Microsoft Sentinel: Intelligent security analytics for your entire enterprise.
• Azure Monitor: Full observability into your applications, infrastructure, and network

The best part about all this is Microsoft Sentinel is built on Azure Monitor (Log Analytics) which in turn, is built on Azure Data Explorer.

That means that switching between these services is seamless. Which allows us to reuse Kusto query language queries and dashboards across these services.

Do you see now why I really love KQL and ADX??

#Yip.

This blog pos illustrates how Azure Data Explorer and Azure Synapse Analytics complement each other for near real-time analytics and modern data warehousing use cases.

This solution is already being used by Microsoft customers. For example, the Singapore-based ride-hailing company, Grab, implemented real-time analytics over a huge amount of data collected from their taxi and food delivery services as well as merchant partner apps. The team from Grab presented their solution at MS Ignite in this video (20:30 onwards). Using this pattern, Grab processed more than a trillion events per day.

This solution is optimized for the retail industry.

Dataflow

1. Raw structured, semi-structured, and unstructured (free text) data such as any type of logs, business events, and user activities can be ingested into Azure Data Explorer from various sources.
2. Ingest data into Azure Data Explorer with low-latency and high throughput using its connectors for Azure Data Factory, Azure Event Hub, Azure IoT Hub, Kafka, and so on. Alternatively, ingest data through Azure Storage (Blob or ADLS Gen2), which uses Azure Event Grid and triggers the ingestion pipeline to Azure Data Explorer. You can also continuously export data to Azure Storage in compressed, partitioned parquet format and seamlessly query that data as detailed in the Continuous data export overview.
3. Export pre-aggregated data from Azure Data Explorer to Azure Storage, and then ingest the data into Synapse Analytics to build data models and reports.
4. Use Azure Data Explorer’s native capabilities to process, aggregate, and analyze data. To get insights at a lightning speed, build near real-time analytics dashboards using Azure Data Explorer dashboards, Power BI, Grafana, or other tools. Use Azure Synapse Analytics to build a modern data warehouse and combine it with the Azure Data Explorer data to generate BI reports on curated and aggregated data models.
5. Azure Data Explorer provides native advanced analytics capabilities for time series analysis, pattern recognition, anomaly detection and forecasting, and machine learning. Azure Data Explorer is also well integrated with ML services such as Databricks and Azure Machine Learning. This integration allows you to build models using other tools and services and export ML models to Azure Data Explorer for scoring data.

Components

• Azure Event Hub: Fully managed, real-time data ingestion service that’s simple, trusted, and scalable.
• Azure IoT Hub: Managed service to enable bi-directional communication between IoT devices and Azure.
• Kafka on HDInsight: Easy, cost-effective, enterprise-grade service for open source analytics with Apache Kafka.
• Azure Data Explorer: Fast, fully managed and highly scalable data analytics service for real-time analysis on large volumes of data streaming from applications, websites, IoT devices, and more.
• Azure Data Explorer Dashboards: Natively export Kusto queries that were explored in the Web UI to optimized dashboards.
• Azure Synapse Analytics: Analytics service that brings together enterprise data warehousing and Big Data analytics.

If you are looking to do anything with Big Data I highly recommend you look at Azure Data Explorer or ADX

#Yip.

Azure Data Explorer is a PaaS offering from Azure providing an end-to-end solution for data exploration.

https://azure.microsoft.com/en-us/blog/individually-great-collectively-unmatched-announcing-updates-to-3-great-azure-data-services/

Here is a quick introduction of the features:

https://azure.microsoft.com/en-us/services/data-explorer/#features

This service from Azure was developed to provide end-to-end data exploration services to help the businesses get quick insights, and make critical business decisions. It can be used for streaming data as well to identify patterns, statistics, anomalies, outliers, and even to diagnose issues.

ADX is a fully managed data analytics service for near real-time analysis on large volumes of data streaming (i.e. log and telemetry data) from such sources as applications, websites, or IoT devices.  ADX makes it simple to ingest this data and enables you to perform complex ad-hoc queries on the data in seconds – ADX has speeds of up to 200MB/sec per node (up to 1,000 nodes) and queries across a billion records take less than a second (!!).  A typical use case is when you are generating terabytes of data from which you need to understand quickly what that data is telling you, as opposed to a traditional database that takes longer to get value out of the data because of the effort to collect the data and place it in the database before you can start to explore it. This allows us to iterate our queries really, really quickly.

Azure Data Explorer has a very fast indexing and a very powerful query language to work with the data. You guessed it – that language is Kusto Query Language!!.

Azure Data Explorer aka ADX, is a fast, highly scalable and fully managed data analytics service for log, telemetry and streaming data. This data exploration service enables you to pull together, store and analyze diverse data. You can query terabytes of data in a few seconds and it allows fast ad-hoc queries over the varied data.

ADX is a PaaS offering from Azure, which is capable of performing analysis on large volumes of data from heterogeneous sources, like – Custom Applications, IoT Devices, Diagnostic Logs, and other streaming data sources as well. This data can be structured, unstructured, or free text.

Data within ADX is organized in relational tables within the database, which has a strongly typed schema. It can scale quickly depending upon the ingested data volume and query load.

ADX works on the principle of isolation between Compute and Storage using volatile SSD storage as a cache and persistent storage in Azure Blob Storage. It is a fully managed ‘Platform as a Service (PaaS)’ that lets users focus only on their data and queries.

Azure Data Explorer can be used with other Azure offerings to create end-to-end solutions for various use cases.

Let’s have a look at some of those use cases for ADX.

#Yip.

This blog post is about another use of KQL that will definitely help your organisation and make you very popular.
First of all check out this video here which can help you learn stuff about KQL:

https://www.microsoft.com/en-us/videoplayer/embed/RWRwfJ?postJsllMsg=true

I have been talking about KQL and using it with Log Analytics and also Application Insights.

We use Log analytics for all our infrastructure things and combine with Azure Monitor to “do” things with our captured data:

But just as equally important is Advanced Hunting – where we can use KQL and start looking at what security events are occurring within our Azure Subscription.

https://security.microsoft.com/v2/advanced-hunting

(NOTE: you do need an Azure subscription to run this)

Advanced hunting is based on the Kusto query language. We can use Kusto operators and statements to construct queries that locate information in a specialized schema.

Here is an example that we can run:

// Finds PowerShell execution events that could involve a download union DeviceProcessEvents, DeviceNetworkEvents
| where Timestamp > ago(7d)
// Pivoting on PowerShell processes
| where FileName in~ ("powershell.exe", "powershell_ise.exe")
// Suspicious commands
| where ProcessCommandLine has_any
("WebClient",
"DownloadFile",
"DownloadData",
"DownloadString",
"WebRequest",
"Shellcode",
"http",
"https")
| project Timestamp, DeviceName, InitiatingProcessFileName, InitiatingProcessCommandLine, FileName, ProcessCommandLine, RemoteIP, RemoteUrl, RemotePort, RemoteIPType
| top 100 by Timestamp

Let’s break this query down a bit

A short comment has been added to the beginning of the query to describe what it is for. This comment helps if you later decide to save the query and share it with others in your organization.

// Finds PowerShell execution events that could involve a download

The query itself will typically start with a table name followed by several elements that start with a pipe (|). In this example, we start by creating a union of two tables, DeviceProcessEvents and DeviceNetworkEvents, and add piped elements as needed.

union DeviceProcessEvents, DeviceNetworkEvents

Set the time range

The first piped element is a time filter scoped to the previous seven days. Limiting the time range helps ensure that queries perform well, return manageable results, and don’t time out.

| where Timestamp > ago(7d)

Check specific processes

The time range is immediately followed by a search for process file names representing the PowerShell application.

// Pivoting on PowerShell processes
| where FileName in~ ("powershell.exe", "powershell_ise.exe")

Search for specific command strings

Afterwards, the query looks for strings in command lines that are typically used to download files using PowerShell.

// Suspicious commands
| where ProcessCommandLine has_any("WebClient",
    "DownloadFile",
    "DownloadData",
    "DownloadString",
    "WebRequest",
    "Shellcode",
    "http",
    "https")

Customize result columns and length

Now that your query clearly identifies the data you want to locate, you can define what the results look like. project returns specific columns, and top limits the number of results. These operators help ensure the results are well-formatted and reasonably large and easy to process.

| project Timestamp, DeviceName, InitiatingProcessFileName, InitiatingProcessCommandLine, 
FileName, ProcessCommandLine, RemoteIP, RemoteUrl, RemotePort, RemoteIPType
| top 100 by Timestamp

Select Run query to see the results.

This truly an amazing usage of KQL in my opinion!!

You can read more about Advanced hunting  (Formerly known as Advanced Threat Protection) here:

https://docs.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-query-language?view=o365-worldwide

This blog post is about how to quickly learn KQL.

Kusto supports a subset of the SQL language. See the list of SQL known issues for the full list of unsupported features.

The primary language to interact with the Kusto Engine is KQL (Kusto Query Language). To make the transition and learning experience easier, you can use Kusto to translate SQL queries to KQL. Send an SQL query to Kusto, prefixing it with the verb ‘EXPLAIN’.

So let’s write some SQL here:

EXPLAIN
SELECT COUNT_BIG(*) as C FROM StormEvents

and we get:

StormEvents
| summarize C=count()
| project C

Reproduced from here:

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/sqlcheatsheet#sql-to-kusto-cheat-sheet-1

SQL to Kusto cheat sheet

The table below shows sample queries in SQL and their KQL equivalents.

The part I love the most is being able to use EXPLAIN to translate what I’ve written in for 20+ years into KQL.

Nice one Microsoft!!

#Yip.

Basically when we are writing KQL we are asking the following questions:

Does it exist?

Where does it exist?

Why does it exist?

What shall we do with the information?

We will come back to these as we go along – but we use these questions to construct our KQL query.

Here is a simple enough search query:

SecurityEvent
| where TimeGenerated > ago (3h)
| where EventID == 4782
| summarize count() by Account
| project Account, PasswordsCracked = count_

(Note you can run this query here: https://aka.ms/LADemo )

Steps to create this query:

1. Identify the table we want to run our query against. So here we are running against the Security Event table. The SecurityEvent table contains security events collected from windows machines by Microsoft Defender for Cloud or Microsoft Sentinel.
2. We make use of the pipe | character which is above the enter/return key (return if you are on a Mac) and we use shift to get it. It is sued to separate commands issued to the query engine. In the example above each command is on its own line.
   A KQL query can be all one single line. As a recommendation, I prefer each command on its own line. It is neater and more organized which makes it easier to troubleshoot when a query fails (as mine typically do from time to time!!)
3. Just as we would with other SQL languages we want to filter things. If we just ran this – we would get all security events in the past 24 hours…

4. The next step in our workflow is to provide data aggregation. What do we want to do with this filtered data? In the example, we want to create a count of the Accounts (usernames) that produced event ID of 4782 in the time greater than 3 hours ago.

5. Lastly we want to present the data, we use Project for this. We tell the engine we only want to display 2 columns in our results Account and PasswordsCracked.

You’ll see that we go zero results – so let’s change our query and look for logoff events – EventID of 4634

SecurityEvent
| where TimeGenerated > ago (3h)
| where EventID == 4634
| summarize count() by Account
| project Account, LogOffEvents = count_

What do we get now?

The ‘desc’ in the query in the Order Data step is what produces this ordering. If we wanted ascending order we’d use ‘asc’.

So what did we do here?

It searched our stored security events in the SecurityEvent table for all Accounts that had a successful login in the last 3 hours and we chose to display only the Account and number of log off events per Account in numerical order with the highest at the top.

So far I’ve introduced some new operators and things – but what is a really quick way to learn KQL?

Let’s look at the best cheat sheet ever…

#Yip.

In Part1 we talked about what a query is.

The most common query we will write is a tabular expression statement which is what people usually have in mind when we talk about queries. This statement usually appears last in the statement list, and both its input and its output consists of tables or tabular data sets.

NOTE: Any two statements must be separated by a semicolon ;

We use a data flow model for the tabular expression statement. A tabular expression statement is generally composed of tabular data sources such as the tables we will query, tabular data operators such as filters and projections, and optional rendering operators.

The composition is represented by the pipe character (|), giving the statement a very regular form that visually represents the flow of tabular data from left to right. Each operator accepts a tabular data set “from the pipe”, and other inputs including more tabular data sets from the body of the operator, then emits a tabular data set to the next operator that follows.

So our query will look something like this:

Source | Operator1 | Operator2 | RenderInstruction

• Source – tabular data sources such as Azure Data Explorer tables
• Operator – tabular data operators such as filters and projections
• RenderInstruction – rendering operators or instructions

Example

In the following slightly more complex example, the join operator is used to combine records from two input data sets, one that is a filter on the Logs table, and another that is a filter on the Events table.

Logs 
| where Timestamp > ago(1d) 
| join 
(
    Events 
    | where continent == 'Europe'
) on RequestId 

So for now let’s use tabluar expressions and look at how we construct information out of our data..

Or in other words the workflow in constructing our query..

#Yip.

In my pervious post I wrote about KQL queries that we will write – we also could use some free some samples that Microsoft put up for us to try things out:

https://dataexplorer.azure.com/clusters/help/databases/Samples

But what is a query? Kudos to Microsoft for putting some great definitions up on their site which I’ve used here.

NOTE:

At times you will see reference to Azure Data Explorer and Azure Monitor.
My first experience with KQL was with Azure Monitor – but I will talk to Azure Data Explorer later in this series. Back to queries…

A Kusto query is a read-only request to process data and return results. The request is stated in plain text, using a data-flow model that is easy to read, author, and automate. Kusto queries are made of one or more query statements.

A query consists of one or more query statements, delimited by a semicolon (;).

At least one of these query statements must be a tabular expression statement. The tabular expression statement generates one or more tabular results. Any two statements must be separated by a semicolon. When the query has more than one tabular expression statement, the query has a batch of tabular expression statements, and the tabular results generated by these statements are all returned by the query.

Two types of query statements:

• Statements that are primarily used by users (user query statements),
• Statements that have been designed to support scenarios in which mid-tier applications take user queries and send a modified version of them to Kusto (application query statements).

Some query statements are useful in both scenarios.

here are three kinds of user query statements:

• A tabular expression statement
• A let statement
• A set statement

All query statements are separated by a ; (semicolon), and only affect the query at hand.

To be honest most of the time I write tabular expressions – let’s have a closer look at this…

This blog post will detail what KQL is all about…

KQL was developed to take advantage of the power of the cloud through clustering and compute. Using this capability, KQL is designed as a well-performing tool to help surface critical data quickly. This a big part of why it works so well and outshines many other query languages like it. KQL was built for the cloud and to be used against large data sets.

Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL’s: databases, tables, and columns.

The query we write in KQL is a read-only request to process data and return results. The request is stated in plain text, using a data-flow model that is easy to read, author, and automate. Kusto queries are made of one or more query statements.

Here is an example query:

StormEvents
| where StartTime between (datetime(2007-11-01) .. datetime(2007-12-01))
| where State == "FLORIDA"
| count

I will show you below how to run this code and also any code that I put in these blog posts – for FREE!!

Where did I first discover Kusto Query Language?
In Azure using Log Analytics – as I was collecting diagnostic data and needed to analyse it:

Where can you discover and try out Kusto Query Language?
Well Microsoft have some free demo sites you can use:

•Log Analytics  https://aka.ms/LADemo

•Application Insights  https://aka.ms/AIAnalyticsDemo

But it gets better….
Go here to run the code above and get the result of “28”

https://dataexplorer.azure.com/clusters/help/databases/Samples

You don’t need to pay anything just have an account registered with Microsoft.

It’s that easy…

#Yip.

I use KQL on an hourly basis….

But for a query language – why call it Kusto..?

Where is a funny tidbit of information:

You have probably heard something sounds like Kusto before…?
I know I had – when I was a young kid.

https://www.imdb.com/title/tt0192937/?WT.mc_id=m365-0000-rotrent

KQL is named after Jacques Cousteau. Even today, you can find evidence of this in the Azure Monitor Docs. If you go to the datatable operator page right now, you’ll still find a reference to him in an example that lists his date of birth, the date he entered the naval academy, when he published his first book entitled “The Silent World: A Story of Undersea Discovery and Adventure,” and the date when he passed.

So the puns about deep diving into data, exploring the depths of data to find and present information – basically we are going to be explorers in a sea of data…

#Yip.