Skip to content

Instantly share code, notes, and snippets.

@azurekid

azurekid/kql-coding-standards.md

Last active November 22, 2022 13:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save azurekid/b1459a1f8cc49aee68fe005301d145bc to your computer and use it in GitHub Desktop.
Save azurekid/b1459a1f8cc49aee68fe005301d145bc to your computer and use it in GitHub Desktop.
Download ZIP
KQL Coding Standards
Raw
kql-coding-standards.md

KQL Coding standards

This document helps to create clean and readable KQL code for parsing and detection rules.
All views are my own based on writing lots of code in PowerShell and other languages.
This is a living document that helps to create a common baseline.

Allign your code

  • Place a spaces before and after the '=' character for readability.
  • allign the code using instead of spaces. Keep the '=' character and default values alligned.

bad example

let parser=(
    starttime:datetime=datetime(null)
    , endtime:datetime=datetime(null)
    , eventresult:string='*'
    , disabled:bool=false)
{

good example

let parser = (
    starttime:datetime                  = datetime(null)
    , endtime:datetime                  = datetime(null) 
    , srcipaddr_has_any_prefix:dynamic  = dynamic([]) 
    , dstipaddr_has_any_prefix:dynamic  = dynamic([]) 
    , ipaddr_has_any_prefix:dynamic     = dynamic([]) 
    , dstportnumber:int                 = int(null)
    , hostname_has_any:dynamic          = dynamic([]) 
    , dvcaction:dynamic                 = dynamic([]) 
    , eventresult:string                = '*'
    , disabled:bool                     = false
)
{
  • Don't go crazy on indention of code. This doesn't help when reading the code in the kusto editor window.

bad example

let AuthProductName=(starttime:datetime=datetime(null)
                    , endtime:datetime=datetime(null)
                    , targetusername_has:string="*"
                    , disabled:bool=false){

good example

let AuthProductName = (
    starttime:datetime          = datetime(null)
    , endtime:datetime          = datetime(null)
    , targetusername_has:string = "*"
    , disabled:bool             = false
)
{

Avoid one-liners

bad example

CommonSecurityLog | where not(disabled) | where TimeGenerated <= ago(5m)

good example

CommonSecurityLog
| where not(disabled) 
| where TimeGenerated <= ago(5m)

bad example

| parse-kv DeviceCustomString3 as (Host:string, Referer:string, ['User-Agent']:string, Accept:string, ['Content-Type']:string, ['X-Forwarded-For']:string) with (pair_delimiter=@'\r\n', kv_delimiter=': ')

good example

| parse-kv DeviceCustomString3 as (
    Host:string
    , Referer:string
    , ['User-Agent']:string
    , Accept:string
    , ['Content-Type']:string 
    , ['X-Forwarded-For']:string
) 
with (
    pair_delimiter=@'\r\n'
    , kv_delimiter=': '
)

Start new line after a function

To give clarity on where something is happening in de code split the lines between the operator and the data.

  • note: this is not the case for the | where operator OR when executed on a column.

bad example

| project-rename Src = SourceIP
, Dst = DestinationIP
, HttpRequestMethod = RequestMethod
, HttpReferrer = Referer
, HttpContentFormat = Accept
, HttpContentType = ['Content-Type']
, UserAgent = ['User-Agent']
, HttpRequestXff = ['X-Forwarded-For']
good example
| project-rename 
    Src                  = SourceIP
    , Dst                = DestinationIP
    , HttpRequestMethod  = RequestMethod
    , HttpReferrer       = Referer
    , HttpContentFormat  = Accept
    , HttpContentType    = ['Content-Type']
    , UserAgent          = ['User-Agent']
    , HttpRequestXff     = ['X-Forwarded-For']

Avoid Trailing spaces

Extra spaces result in future edits where the only change is a space being added or removed

bad example

| project-rename..
    TargetDvcHostname = Computer
    , EventOriginalUid=EventOriginId..........
    , EventOriginalType=EventID..
| extend  EventCount=int(1)
        , EventSchemaVersion='0.1.0'
        , ActorUserIdType='SID'
        , TargetUserIdType='SID'
        , EventVendor='Microsoft'..
        , EventStartTime =TimeGenerated...
        , EventEndTime=TimeGenerated...

good example

| project-rename 
    TargetDvcHostname   = Computer
    , EventOriginalUid  = EventOriginId
    , EventOriginalType = EventID
| extend  
    EventCount           = int(1)
    , EventSchemaVersion = var_schemaVersion
    , ActorUserIdType    = 'SID'
    , TargetUserIdType   = 'SID'
    , EventVendor        = var_eventVendor
    , EventStartTime     = TimeGenerated
    , EventEndTime       = TimeGenerated

Spaces around special characters

White-space is (mostly) irrelevant to KQL, but its proper use is key to writing easily readable code.
Use a single space after commas and semicolons, and around pairs of curly braces.
Every braceable statement should also have the opening brace on the end of a line, and the closing brace at the beginning of a line.

bad example

let SeverityLookup = datatable(var_LogSeverity:string, EventSeverity:string)
[ "low", "Low"
 ,"medium", "Medium"
 ,"high", "High"
 ,"", "Informational"];

let ActionLookup = datatable(DvcOriginalAction:string, DvcAction:string)
[ "drop", "Drop"
, "forward", "Allow"
, "source-reset", "Reset Source"
, "dest-reset", "Reset Destination"
, "source-dest-reset", "Reset"
, "proxy", "Allow"
, "challenge", "Reset"
, "quarantine", "Reset"
, "drop-and-quarantine", "Drop"
, "allow", "Allow"];

good example

let SeverityLookup = datatable (var_LogSeverity:string, EventSeverity:string) [ 
    "low", "Low"
    , "medium", "Medium"
    , "high", "High"
    , "", "Informational"
];

let ActionLookup = datatable (DvcOriginalAction:string, DvcAction:string) [
    "drop", "Drop"
    , "forward", "Allow"
    , "source-reset", "Reset Source"
    , "dest-reset", "Reset Destination"
    , "source-dest-reset", "Reset"
    , "proxy", "Allow"
    , "challenge", "Reset"
    , "quarantine", "Reset"
    , "drop-and-quarantine", "Drop"
    , "allow", "Allow"
];

Inline comments

  • Large comment blocks don't improve the readability of the code and also brings allignment issues.

bad example

    | lookup SeverityLookup on LogSeverity
    // ************************ 
    //      <Constants> 
    // ************************
    | extend
        EventSchemaVersion = '0.1.1'
      , EventSchema = "Authentication"
      , EventCount = 1
      , TargetUsernameType = "Simple"
    // ************************ 
    //      <Aliases> 
    // ************************
    | extend
        EventOriginalUid = tostring(ExternalID)
      , TargetDomain = DstDomain
      , TargetDomainType = DstDomainType
      , User = TargetUsername
      , UserType = TargetUsernameType
      , Dst = TargetIpAddr
    | extend
        AdditionalFields = pack(

good example

| lookup SeverityLookup on LogSeverity
  // extending with constant values
| extend
    EventSchemaVersion   = '0.1.1'
    , EventSchema        = 'Authentication'
    , EventCount         = 1
    , TargetUsernameType = "Simple"
  // adding aliases
| extend
    EventOriginalUid     = tostring(ExternalID)
    , TargetDomain       = DstDomain
    , TargetDomainType   = DstDomainType
    , User               = TargetUsername
    , UserType           = TargetUsernameType
    , Dst                = TargetIpAddr
| extend
    AdditionalFields = pack(

Line breaks

Use line breaks between funtions and let variable declarations.
Try to keep code that is related close to create a logical flow when reading the code.

It does help to add a linebreak between let statements that are not related.

bad example

let LogonEvents=dynamic([4624,4625]);
let LogoffEvents=dynamic([4634,4647]);
let LogonTypes=datatable(LogonType:int, EventSubType:string)[
    2, 'Interactive',
    3, 'Network',
    4, 'Batch',
    5, 'Service',
    7, 'Unlock'];
let LogonStatus=datatable 
    (EventStatus:string,EventOriginalResultDetails:string, EventResultDetails:string)[
    '0x80090325', 'SEC_E_UNTRUSTED_ROOT','Other',
    '0xc0000064', 'STATUS_NO_SUCH_USER','No such user or password',
    '0xc000006f', 'STATUS_INVALID_LOGON_HOURS','Logon violates policy',
    '0xc0000070', 'STATUS_INVALID_WORKSTATION','Logon violates policy',

good example

let LogonEvents  = dynamic([4624,4625]);
let LogoffEvents = dynamic([4634,4647]);

let LogonTypes=datatable(LogonType:int, EventSubType:string) [
      2, 'Interactive'
    , 3, 'Network'
    , 4, 'Batch'
    , 5, 'Service'
    , 7, 'Unlock'
];

let LogonStatus=datatable(EventStatus:string,EventOriginalResultDetails:string, EventResultDetails:string) [
    , '0x80090325', 'SEC_E_UNTRUSTED_ROOT','Other'
    , '0xc0000064', 'STATUS_NO_SUCH_USER','No such user or password'
    , '0xc000006f', 'STATUS_INVALID_LOGON_HOURS','Logon violates policy'
    , '0xc0000070', 'STATUS_INVALID_WORKSTATION','Logon violates policy'
];

bad example

let FQDN_regex =  @'^([_a-zA-Z0-9][_a-zA-Z0-9-]{0,62}\.)+[a-zA-Z]{2,63}$'; // -- based on https://stackoverflow.com/questions/11809631/fully-qualified-domain-name-validation without lookahead.
  let DNS_domain_regex = @'^([_a-zA-Z0-9][_a-zA-Z0-9-]{0,62}\.)*[a-zA-Z]{2,63}$'; // -- Allow underscores in domain names, used by Microsoft DNS server
  let domain_regex = @'^([_a-zA-Z0-9][_a-zA-Z0-9-]{0,62}\.)*[a-zA-Z]{2,63}$';
  let Hostname_regex = @'^[a-zA-Z0-9-]{1,61}$';
  let MD5_regex = @'[a-zA-Z0-0]{32}';
  let SHA1_regex = @'[a-zA-Z0-0]{40}';
  let SHA256_regex = @'[a-zA-Z0-0]{64}';
  let SHA512_regex = @'[a-zA-Z0-0]{128}';
  let IPprotocol = materialize (externaldata (code: string, value: string)
    [@"https://www.iana.org/assignments/protocol-numbers/protocol-numbers-1.csv"] with (format="csv", IgnoreFirstRecord=true) | project value);
  let DnsQueryTypeName = materialize (externaldata (value: string)
    [@"https://www.iana.org/assignments/dns-parameters/dns-parameters-4.csv"] with (format="csv", IgnoreFirstRecord=true));
  let DnsResponseCodeName = materialize (externaldata (code: string, value: string)
    [@"https://www.iana.org/assignments/dns-parameters/dns-parameters-6.csv"] with (format="csv", IgnoreFirstRecord=true) | project toupper(value));
  //let DnsQueryClassName = materialize (externaldata (dec: string, dex: string, value: string)
    //[@"https://www.iana.org/assignments/dns-parameters/dns-parameters-2.csv"] with (format="csv", IgnoreFirstRecord=true) | project value);
  //let schemas_in_data = materialize (T | summarize schemas = make_set(EventSchema)); 
  let ASimFields = materialize(externaldata (ColumnName: string, ColumnType: string, Class: string, Schema: string, LogicalType:string, ListOfValues: string, AliasedField: string)
        [@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/ASIM/dev/ASimTester/ASimTester.csv"] with (format="csv", IgnoreFirstRecord=true) | project-rename dict_schema = Schema);

good example

let MACaddr_regex     = @'^[a-fA-F0-9]{2}(:[a-fA-F0-9]{2}){5}$';
let FQDN_regex        = @'^([_a-zA-Z0-9][_a-zA-Z0-9-]{0,62}\.)+[a-zA-Z]{2,63}$';
let DNS_domain_regex  = @'^([_a-zA-Z0-9][_a-zA-Z0-9-]{0,62}\.)*[a-zA-Z]{2,63}$'; 
let domain_regex      = @'^([_a-zA-Z0-9][_a-zA-Z0-9-]{0,62}\.)*[a-zA-Z]{2,63}$';
let Hostname_regex    = @'^[a-zA-Z0-9-]{1,61}$';
let MD5_regex         = @'[a-zA-Z0-0]{32}';
let SHA1_regex        = @'[a-zA-Z0-0]{40}';
let SHA256_regex      = @'[a-zA-Z0-0]{64}';
let SHA512_regex      = @'[a-zA-Z0-0]{128}';

// let schemas_in_data = materialize (T | summarize schemas = make_set(EventSchema));
let IPprotocol = materialize (externaldata (code: string, value: string) [ 
        @"https://www.iana.org/assignments/protocol-numbers/protocol-numbers-1.csv"
    ] 
    with (
        format = "csv"
        , IgnoreFirstRecord = true
    )
| project 
    value
);

// let DnsQueryClassName = materialize (externaldata (dec: string, dex: string, value: string)
let DnsQueryTypeName = materialize (
    externaldata (value: string) [
        @"https://www.iana.org/assignments/dns-parameters/dns-parameters-4.csv"
    ] 
    with (
        format = "csv"
        , IgnoreFirstRecord = true
    )
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment