Building KQL queries

Edit online

The Kusto Query Language (KQL) is a query language that you can use to query the Next Gen Log Management data lake. KQL allows you to build a variety of different type of queries from simple search queries to more complex aggregated queries.

Procedure

Go to Data Explorer > Federated Search, and click the Advanced builder tab.
Click the KQL tab.

In the query text field, enter your query. For example, the following query returns all events from the last 5 minutes, up to a maximum 10000 events.

events    
    | project original_time, data_source_name, name, user_id, low_level_categories,
        src_ip, src_port, dst_ip, dst_port, payload
    //--- Search for the last 5 minutes of data
    | where original_time > ago(5m)    
    //--- USER Criteria Here    
    | take 10000

Each filter prefixed by the pipe character (|) is an instance of an operator, with some parameters.

The following list contains some common KQL operators that you can use in queries.

KQL operator	Description
order	Sorts results into order by one or more columns.
project	Returns only a subset of columns specified. For example, project original_time, name, and payload.
summarize	Arranges the results into groups that have the same values following the `by` expressions.
take	Returns only the specified number of rows specified ( take 5).
where	Filters results to the subset of rows as specified in the arguments.

Results

If there is a syntax error in your query, Run query is disabled until you resolve the error. Each syntax error is highlighted with a red underline, a tooltip with syntax error details, and contextual helper with syntax fix suggestions.

When a query is run, an 'active-query' card is added. Each query expires 4 days after it is created. Expiry time is shown on the card.

Example

Returns 100 rows from the event table.

events
| take 100

Returns 10 event categories that have the most events.

events_all
| summarize Count=count() by qid_event_category
| order by Count desc
| take 10

Returns 10 data sources that have the most event count.

events
| project data_source_name, original_time
| where data_source_name != ''
| summarize Events=count() by data_source_name
| order by Events desc
| take 10

Returns the data sources over last hour.

events
| project original_time, data_source_name
| where original_time > ago(1h)  
| summarize Count=count() by Time=bin(original_time, 5m), data_source_name  
| order by Time, data_source_name desc

Returns events for the last 5 minutes up to a maximum of 10000.

events    
    | project original_time, data_source_name, name, user_id, low_level_categories,
        src_ip, src_port, dst_ip, dst_port, payload
    //--- Search for the last 5 minutes of data
    | where original_time > ago(5m)    
    //--- USER Criteria Here    
    | take 10000

Returns events for the last hour up to a maximum of 10000

events    
    | project original_time, data_source_name, name, user_id, low_level_categories,
        src_ip, src_port, dst_ip, dst_port, payload
    //--- Search for the last hour of data
    | where original_time > ago(5m)    
    //--- USER Criteria Here    
    | take 10000

Search for events for the last 24 hours that have Source IP address 127.0.0.1.

events    
    | project original_time, data_source_name, name, user_id, low_level_categories,
        src_ip, src_port, dst_ip, dst_port, payload
    //--- Search for IP = 127.0.0.1 over the last 24 hour of data
    | where original_time > ago(24h) and src_ip = ‘127.0.0.1’  
    //--- USER Criteria Here    
    | take 100

Search for events for the last 24 hours that do not have Source IP address 127.0.0.1.

events    
    | project original_time, data_source_name, name, user_id, low_level_categories,
        src_ip, src_port, dst_ip, dst_port, payload
    //--- Search for IP not equal to 127.0.0.1 over the last 24 hour of data
    | where original_time > ago(24h) and src_ip != ‘127.0.0.1’  
    //--- USER Criteria Here    
    | take 100

Search for events for the last hour where payload has a match on svchost.

events    
    | project original_time, data_source_name, name, user_id, low_level_categories,
        src_ip, src_port, dst_ip, dst_port, payload
    //--- Search for payload that has a match on ‘svchost’ over last hour
    | where original_time > ago(1h) and payload has ‘svchost’  
    //--- USER Criteria Here    
    | take 100