Search Operator
search “example”
Search in (SecurityEvent,SecurityAlert) “example”
Where Operator
SecurityEvent
| where TimeGenerated > ago(1d)
SecurityEvent
| where TimeGenerated > ago(1h) and EventID == “4624”
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4624
| where AccountType =~ “user”
SecurityEvent | where EventID in (4624, 4625)
let statement
bind names to expressions
let timeOffset = 7d;
let discardEventId = 4688;
SecurityEvent
| where TimeGenerated > ago(timeOffset*2) and TimeGenerated < ago(timeOffset)
| where EventID != discardEventId
ago()
function
takes the current Date and Time and subtract the value provided
extend operator
create calculated columns and append the new columns to the result set
extend StartDir = substring(ProcessName,0, string_size(ProcessName)-string_size(Process))
order by operator
Sort the rows of the input table by one or more columns, The default order for a column is descending
order by StartDir desc, Process asc
project operators
control what columns to include, add, remove, or rename in the result set of a statement
project - Select the columns to include, rename or drop, and insert new computed columns.
project-away - Select what columns from the input to exclude from the output.
project-keep - Select what columns from the input to keep in the output.
project-rename - Select the columns to rename in the resulting output.
project-reorder - Set the column order in the resulting output.
summarize operator
You use the summarize operator with other operators like the count operator. The count operator with its variations creates a new column with the calculated result for the specified fields.
count(), countif() - Returns a count of the records per summarization group
dcount(), dcountif() - Returns an estimate for the number of distinct values taken by a scalar expression in the summary group.
avg(), avgif() - Calculates the average of Expr across the group.
max(), maxif() - Returns the maximum value across the group.
min(), minif() - Returns the minimum value across the group.
percentile() - Returns an estimate for the specified nearest-rank percentile of the population defined by Expr. The accuracy depends on the density of population in the region of the percentile.
stdev(), stdevif() - Calculates the standard deviation of Expr across the group, considering the group as a sample.
sum(), sumif() - Calculates the sum of Expr across the group.
variance(), varianceif() - Calculates the variance of Expr across the group, considering the group as a sample.
dcount function
The following example returns a count of unique IP Addresses.
summarize dcount(IpAddress)
summarize operator to filter data
The arg_max() and arg_min() functions filter out top and bottom rows respectively.
summarize arg_max(TimeGenerated,*) by Computer
summarize operator to prepare data
The make_ functions return a dynamic (JSON) array based on the specific function’s purpose.
make_list() - returns a dynamic (JSON) array
make_set() - similar to list function, but it returns only the unique entries
render operator
generates a visualization of the query results
Supported visuals are:
areachart
barchart
columnchart
piechart
scatterchart
timechart
Ex:
SecurityEvent
| summarize count() by Account
| render barchart
bin() function
rounds values down to an integer multiple of the given bin size. Used frequently in combination with summarize by ….
SecurityEvent
| summarize count() by bin(TimeGenerated, 1d)
| render timechart
union operator
multi table statement
takes two or more tables and returns the rows of all of them
join operator
multi table statements
merges the rows of two tables to form a new table by matching the specified columns’ values from each table.
LeftTable | join [JoinParameters] ( RightTable ) on Attributes
kind=leftanti, kind=leftantisemi - Returns all the records from the left side that don’t have matches from the right
kind=rightanti, kind=rightantisemi - Returns all the records from the right side that don’t have matches from the left.
kind unspecified, kind=innerunique - Only one row from the left side is matched for each value of the on key. The output contains a row for each match of this row with rows from the right
kind=leftsemi - Returns all the records from the left side that have matches from the right.
kind=rightsemi - Returns all the records from the right side that have matches from the left.
kind=inner - Contains a row in the output for every combination of matching rows from left and right.
kind=leftouter (or kind=rightouter or kind=fullouter) - Contains a row for every row on the left and right, even if it has no match. The unmatched output cells contain nulls.
When you’re using the join operator, how do you specify fields from each table?
$left.columname and $right.columnname
extract
extracting data from unstructered string field
extend Account_Name = extract(@”^(.\)?([^@])(@.*)?$”, 2, tolower(Account))
Parse
extracting data from unstructered string field
evaluates a string expression and parses its value into one or more calculated columns. The computed columns have nulls for unsuccessfully parsed strings.
Syntax
T | parse [kind=regex [flags=regex_flags] |simple|relaxed] Expression with * (StringConstant ColumnName [: ColumnType]) *
Dynamic Fields
Dynamic fields within the log analytics table contain Key-Value pairs like
{“eventCategory”:”Autoscale”,”eventName”:”GetOperationStatusResult”,”operationId”:”xxxxxxxx-6a53-4aed-bab4-575642a10226”,”eventProperties”:”{"OldInstancesCount":6,"NewInstancesCount":5}”,”eventDataId”:” xxxxxxxx -efe3-43c2-8c86-cd84f70039d3”,”eventSubmissionTimestamp”:”2020-11-30T04:06:17.0503722Z”,”resource”:”ch-appfevmss-pri”,”resourceGroup”:”CH-RETAILRG-PRI”,”resourceProviderValue”:”MICROSOFT.COMPUTE”,”subscriptionId”:” xxxxxxxx -7fde-4caf-8629-41dc15e3b352”,”activityStatusValue”:”Succeeded”}
To access strings within use dot notation ( DeviceDetail.operatingstem)
JSON Extract data from structured string data
parse-json() or todynamic() - Interprets a string as a JSON value and returns the value as dynamic. Use either of these functions to refer to a field: JsonField.Key or JsonField[“Key”]
mv-expand - is applied on a dynamic-typed array or property bag column so that each value in the collection gets a separate row. All the other columns in an expanded row are duplicated. mv_expand is the easiest way to process JSON arrays.
mv-apply - Applies a subquery to each record and returns the union of the results of all subqueries. Apply a query to each value in an array.
externaldata operator
returns a table whose schema is defined in the query itself
Syntax
externaldata ( ColumnName : ColumnType [, …] )
[ StorageConnectionString [, …] ]
[with ( PropertyName = PropertyValue [, …] )]
Arguments
ColumnName, ColumnType: The arguments define the schema of the table. The syntax is the same as the syntax used when defining a table in. create table.
StorageConnectionString: Storage connection strings that describe the storage artifacts holding the data to return.
PropertyName, PropertyValue, …: More properties that describe how to interpret the data retrieved from storage, as listed under ingestion properties.
Parsers
are functions that define a virtual table with already parsed unstructured strings fields such as Syslog data.
In the Logs window, you create a query, select the Save button, enter the Name, and select Save As Function from the drop-down. In this case, if we name the function “PrivLogins”, I can then access the table using the name PrivLogins.
SecurityEvent
| where EventID == 4672 and AccountType == ‘User’
=
PrivLogins
Which KQL statement should you use to parse external data into a virtual table?
externaldata
Common operators
count. Returns the count of rows in the table.
take. Returns up to the specified number of rows of data.
project. Selects a subset of columns.
sort. Sorts the rows of the input table into order by one or more columns.
top. Returns the first N records sorted by the specified columns.
extend. Computes derived columns.
summarize. Aggregates groups of rows.
render. Renders results as a graphical output.
