Powershell Integration

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

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

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

The summarize operator is used to aggregate data*

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

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)"

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

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

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)"

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.