Create Queries for Azure Sentinel Using Kusto Query Language

By Ashwin Venugopal -

 


The Kusto Query Language (KQL) Statement Structure

A KQL query is a read-only request to process the data and return results which is stated in plain text, using the data-flow model designed to make the syntax easy to read, write, and automate; while consisting of a sequence of query statements in which at least one of them is a tabular expression statement that produces data arranged in a table-like mesh of columns as well as rows. 

The tabular expression statement's syntax has tabular data flow from one tabular query operator to another, starting with the data source and then flowing through a set of data transformation operators bound together through the use of pipe (|) delimiter. 

Note- it is essential to understand how the results flow through the pipe "|". Everything on the left of the pipe is processed then sent to the right of the pipe.

Access the Log Analytics demo environment

If you have an account to log into Azure, then Microsoft can provide you with an access to an environment to practice writing KQL statements. No charges are required to your Azure account to access this environment and you can execute the KQL statements in this module in the demo environment. The Query window has three primary sections:
  • The left area is a reference list of the tables in the environment.
  • The middle top area is the Query editor.
  • The bottom area is the Query Results.
Before running a query, adjust the time range to scope the data. To change the result columns displayed, select the Columns box, and choose the required columns.

Use Let statement

Let statements can bind names to expressions and for the rest of the scope, where the let statement appears, the name refers to its bound value. They improves the modularity and reuse since they allow you to break a potentially complex expression into multiple parts as well as the creation of user-defined functions and views. The views are the expressions whose results looks like a new table.

Use the Search operator

The search operator provides a multi-table/multi-column search experience. Although it is easy to use, but it is inefficient compared to the where operator. However, even after this, you can use search to find data when unsure which table or column to filter. 

Use the Where operator

The where operator filters a table to the subset of the rows that satisfy a predicate.

Use the Extend operator

It creates calculated columns and append the new columns to the result set.

Use the Order By operator

It sorts the rows of the input table by one or more columns by utilizing the operator's order it allows you to utilize any column or multiple columns by using a comma separator each of which can be either ascending or descending.

Use the Project operator

The project operators controls what columns to include, add, remove, or rename in the result set of a statement. There are multiple types of project operators described as follows:

Operator

Description

project

Selects the columns to include, rename, or drop and insert new computed columns.

project-away

Selects what column from the input to exclude from the output.

Project-keep

Selects what column from the input to keep in the output.

project-rename

Selects what columns to rename in the resulting output.

Project-reorder

Sets the column order in the resulting output.

 


 





Comments

Post a Comment

Popular posts from this blog

Query, Visualize, & Monitor Data in Azure Sentinel

By Ashwin Venugopal -
Image
  Azure Sentinel Workbooks Several ready for use templates are provided by the Azure Sentinel that can be used to create your own workbook and then modify them as needed for Contoso. Most of the data connectors it uses to ingest data come with their own workbooks, but better insight can be obtained by simply looking into the data that is being ingested by using tables and visualizations, including bar and pie charts. You can also make your own workbook easily by using this data from the beginning instead of using the predefined templates. Workbook Page You can easily access the workbook page from the Azure Sentinel from the navigation pane which consists of the: Workbook header- You can add a new workbook and review the saved workbooks as well as templates that are available on the workbook page. Templates section- You can access existing workbook templates on the Templates tab. You can save some of the workbooks for quick access and they will appear on the My Workbooks tab.  From the
Read more

Planning for Implementing SAP Solutions on Azure (Part 2 of 5)

By Ashwin Venugopal -
Image
  To read part 1 please click  here To read part 3 please click  here To read part 4 please click  here To read part 5 please click  here Load Balancing The load balancer uses probe ports to determine as well as route the traffic to an active DBMS node, but of there's any failover then the most common SAP application architecture can automatically reconnect against the private virtual IP address without the need for the SAP application to reconfigure it, while the load balancer redirect the traffic against the private virtual IP address without any need for the SAP application to reconfigure. The loaf balancer also offers an option of DirectServerReturn which if configured, can help in directing the traffic from the DBMS VM to the SAP application layer directly to the application layer without routing through the load balancer. But, if it isn't configured, then the return traffic to the SAP application layer is routed through the load balancer. You can also use Azure Load Balan
Read more

Work with String Data Using KQL Statements

By Ashwin Venugopal -
Image
  Extract Data from Unstructured String Fields Unstructured string fields often contains the Security log data and requires parsing to extract data. There are multiple ways of pulling information from string fields in KQL and the two primary operators used are extract and parse.  Extract It gets a match for a regular expression from a text string. You can convert the extracted substring to the indicated type.  Arguments regex- A regular expression. captureGroup- A positive int constant indicating the capture group to extract. 0 stands for the entire match, 1 for the value matched by the first '('parenthesis')' in the regular expression, 2 or more for subsequent parenthesis.  text- A string to search. typeLiteral- An optional type literal. If provided, the extracted substring is converted to this type.  Returns If regex finds a match in text- the substring matched against the indicated capture group captureGroup, optionally converted to typeLiteral. If there's no mat
Read more