Skip to content

Instantly share code, notes, and snippets.

@RalfWenzel

RalfWenzel/kql-readme.md

Last active November 3, 2024 22:29
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save RalfWenzel/05c810e140e29194352f7fe775690d26 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save RalfWenzel/05c810e140e29194352f7fe775690d26 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
kql-readme.md

KQL Coding guidelines

This document does not reflect any official standard, it is intended for team-internal use but may others help also.

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