🌴
The Amazing KQL
  • The Amazing KQL
  • 💠KQL Quick Guide
    • Useful Resources 🔦
    • My favorites 😍
      • search
      • take
      • where
      • summarize
        • arg_max()
        • count, countif
        • dcount, dcountif
        • take_any
      • distinct
      • case
      • project
        • project-reorder
        • project-away
        • project-rename
        • project-keep
      • sort by
      • extend
      • extract
        • extract_all
      • parse
      • stract
      • count
        • countif
      • mv-expand
      • dcount
        • dcountif
      • Create table
      • let
      • join
      • union
      • materialize
    • Need to practice more 🎯
      • toscalar
      • range
      • make-series
      • series_outliers
      • set_differenc
      • pack
      • summarize
        • make_bag
        • make_set, make_list
      • evaluate
        • pivot
        • bag_unpack
        • pack_all
      • mv-expand
      • set_difference
      • render
    • Need to learn later 🐢
      • scan
      • ExtractParseParse-kv-Tabular
      • decode
      • mv-apply
      • prev and next
      • row_cumsum
      • any
      • top-nested
      • Time Series
        • series_stats
        • series_fir
        • series_iir
        • series_fit_line
        • series_fit_2lines
      • Machine Learning
        • basket
        • autocluster
        • diffpatterns
        • reduce
  • 💻Microsoft Endpoint Manager
    • Device Inventory
      • Device OS version
      • Same AAD Device ID and Intune Device ID
Powered by GitBook
On this page
  1. KQL Quick Guide
  2. Need to learn later 🐢

ExtractParseParse-kv-Tabular

This is example from WPNinja Summit 2022 session "Throwing KQL like a shuriken". Presented by Gianni Castaldi and Alex Verboon

// Oldschool extract with regex
let Table = datatable(ParseMe:string)
[
'fruit="Apples", color="Orange", packing="Bottle"',
'fruit="Bananas", color="Red", packing="Crate"',
'fruit="Pears", color="Green", packing="Box"',
'fruit="Cherries", color="Yellow", packing="Envelope"',
'fruit="Oranges", color="Blue", packing="Tube"'
];
Table
| extend fruit = extract('fruit="(\\w+)',1,ParseMe)
    , color = extract('color="(\\w+)',1,ParseMe)
    , packing =  extract('packing="(\\w+)',1,ParseMe)

// But extract is slow and regex is hard
let Table = datatable(ParseMe:string)
[
'fruit="Apples", color="Orange", packing="Bottle"',
'fruit="Bananas", color="Red", packing="Crate"',
'fruit="Pears", color="Green", packing="Box"',
'fruit="Cherries", color="Yellow", packing="Envelope"',
'fruit="Oranges", color="Blue", packing="Tube"'
];
Table
| parse ParseMe with 'fruit="' fruit '", color="' color '", packing="' packing

// So parse needs to be in the same order
let Table = datatable(ParseMe:string)
[
'fruit="Apples", color="Orange", packing="Bottle"',
'fruit="Bananas", color="Red", packing="Crate"',
'fruit="Pears", color="Green", packing="Box"',
'fruit="Cherries", color="Yellow", packing="Envelope"',
'fruit="Oranges", color="Blue", packing="Tube"'
];
Table
| parse-kv ParseMe as (
    fruit:string
    , color:string
    , packing:string
) with (pair_delimiter=',', kv_delimiter='=', quote='"')

// Now what if we mixup the keys?
let Table = datatable(ParseMe:string)
[
'fruit="Apples", color="Orange", packing="Bottle"',
'fruit="Bananas", color="Red", packing="Crate"',
'fruit="Pears", color="Green", packing="Box"',
'fruit="Cherries", color="Yellow", packing="Envelope"',
'fruit="Oranges", color="Blue", packing="Tube"'
];
Table
| parse-kv ParseMe as (
    packing:string
    , color:string
    , fruit:string
) with (pair_delimiter=',', kv_delimiter='=', quote='"')

// Tabular Function
let TableParser = (Table:(ParseMe:string)) {
    Table
    | parse-kv ParseMe as (
        fruit:string
        , color:string
        , packing:string
    ) with (pair_delimiter=',', kv_delimiter='=', quote='"')
    | project-away ParseMe
};
// Table
let Table = datatable(ParseMe:string)
[
'fruit="Apples", color="Orange", packing="Bottle"',
'fruit="Bananas", color="Red", packing="Crate"',
'fruit="Pears", color="Green", packing="Box"',
'fruit="Cherries", color="Yellow", packing="Envelope"',
'fruit="Oranges", color="Blue", packing="Tube"'
];
TableParser(Table)
PreviousscanNextdecode

Last updated 2 years ago

💠