Basic Queries

Overview:

The following section goes over the basic queries that can be run within KQL.

Data Retrieval

  • Selecting Columns

    • Use the project operator to select specific columns from a table.

    • Example with security logs:

      AzureActivity
| project TimeGenerated, Computer, EventID, AccountName, LogonType

  • Filtering Rows with where Clause

    • The where clause filters rows based on a condition.

    • Example to filter security logs for logon events:

      SecurityEvent
| where EventID == 4624

    • Multiple conditions can be combined using logical operators (and, or, not).

    • Example to filter for successful logons by a specific user:

      SecurityEvent
| where EventID == 4624 and AccountName == "jdoe"

Sorting and Limiting Data

  • Using sort by

    • The sort by operator sorts the results by one or more columns.

    • Example to sort security logs by time:

      SecurityEvent
| sort by TimeGenerated desc

  • Limiting Results with top and take

    • The top operator returns the top N rows based on a specific column.

    • Example to get the top 10 most recent security events:

      SecurityEvent
| top 10 by TimeGenerated desc

    • The take operator returns the first N rows from the result set.

    • Example to take the first 5 rows from the security event log:

      SecurityEvent
| take 5

Aggregating Data

  • Using summarize for Aggregations

    • The summarize operator is used for data aggregation, such as calculating sums, averages, counts, etc.

    • Example to count the number of events per computer:

      SecurityEvent
| summarize EventCount = count() by Computer

    • Aggregations can be grouped by one or more columns.

    • Example to count the number of logon events per account:

      SecurityEvent
| where EventID == 4624
| summarize LogonCount = count() by AccountName

  • Common Aggregation Functions

    • sum(): Calculates the sum of values.

      SecurityEvent
| summarize TotalEvents = sum(EventCount)

    • count(): Counts the number of rows.

      SecurityEvent
| summarize TotalEvents = count()

    • avg(): Calculates the average of values.

      SecurityEvent
| summarize AvgEventDuration = avg(Duration)

    • min(), max(): Finds the minimum and maximum values.

      SecurityEvent
| summarize MinTime = min(TimeGenerated), MaxTime = max(TimeGenerated)

Last updated