Powershell Integration - NeilMacMullen/kusto-loco GitHub Wiki

The pskql dll contains a Powershell cmdlet which allows objects to be piped into a KustoLoco table and queried. The results are emitted as objects to support further pipelining

Requirements

  • PowerShell v7.5+ - refer to the Microsoft installation documentation for your OS. You may receive an error similar to the following if this is out of date:
Could not load file or assembly 'System.Management.Automation, Version=7.x..., Culture=neutral, PublicKeyToken=...' or one of its dependencies. The system cannot find the file specified.

Installation

  • Download and unzip the latest release
  • Navigate to the pskql folder
  • Run Import-Module .\pskql.dll
  • If you place the import-module command in your profile, you may need to also add an Export-ModuleMember -Cmdlet * step.

It is also possible to run the module on Linux/WSL. In this case you must use the version in the pskql-linux folder. For example:

  • import-module /mnt/c/tools/lokql-linux/pskql.dll

Basic Queries

If no query is supplied the object members are listed

ls | edit-kql

image

ls | edit-kql "project Name,Length | order by Length | take 3"

image

The summarize operator is used to aggregate data*

ls | edit-kql "where Extension != '' | summarize sum(Length) by Extension"

image

The bin function can be used to count the number of files access across each week

ls | edit-kql "summarize count() by bin(LastAccessTime,7d)"

image

Results can be rendered in a browser (requires internet connectivity)

ls | Edit-Kql "project Name,Length | order by Length | take 10 | render piechart"

image

Advanced examples

The -noqueryprefix flag is used to indicate the query is not implicitly prefixed with "data | " and can be used for more complex operations or where you want to define local functions.

Categorise files by size

ls | edit-kql   -noqueryprefix "let sz = (s:long)  {case (isnull(s),'-',s < 1000,'s',s<1000000,'m','l')} ; data | project Name,Length,Size=sz(Length)"

image

Create some folders named for the last 10 days

edit-kql -noqueryprefix "range N from 1d to 10d step 1d | extend D=now()-N | project T=format_datetime(D,'yyyy-MM-dd')" -NoQueryPrefix $true | % {New-Item $_.T -Type Directory }

Caveats

  • Many more complex powershell types contain a heirarchy of properties. edit-kql is unable to preserve this heirarchy and main also fail to map all properties in an object.
  • Rendering is performed by writing HTML to a file in the TEMP folder and then invoking the application associated with the .html extension. This is normally the browser. The charting library requires internet connectivity to download JS files and will not work in offline mode.