LokqlDX

2 Jan 2025 : Latest version is 1.1.0 - use the .version command to check what you are running.

LokqlDX is a simple data explorer that can read CSV and Parquet files, issue queries on them, and render the results or save them back out to file. A CLI equivalent (lokql) is available for scripting and automation.

Quick start

Prebuilt-binaries are available from the Releases section.

The Query/command editor accepts both KQL queries and lokql commands which are signalled with a '.' prefix. Commands/queries must be separated with a blank line but can be run (or re-run) in any order by moving the cursor into them and pressing SHIFT-ENTER.

The .load command can be used to load a CSV or parquet file. The path can be fully qualified or relative to the local workspace data context.

If a query is run, then data is shown in both the output window (as text) and the datagrid view. If the query contains a valid render command then it will be rendered in the chart view.

Data can be saved using the .save command.

Workspaces

A workspace contains the contents of the command window as well any settings. Note that reloading a workspace does NOT automatically re-run the commands in the command window.

Workspaces allow related files to be placed together via the kusto.datapath setting; when loading and saving files, if the provided path is not fully rooted then it will be treated as relative to this value. For example, .set kusto.datapath "c:\mydata;c:\common" will mean that if .load data.csv is executed, the folders mydata and common will be searched.

By default, when creating a new workspace, kusto.datapath will be set to the folder that contains the workspace file.

Commands

Commands are prefixed with '.' and allow various operations from within the command editor. To run a command place the cursor on the line that contains it and press SHIFT-ENTER.

• .help lists all commands recognised by the application

• .load loads data files into tables. The type of file is inferred from the suffix of the file and by default the table is named after the filename. If a table of the same name already exists, the load will be skipped unless the -f flag is used.

• .save saves the last query result to a file. The type of file is inferred from the suffix of the file.

• .listtables lists all tables in the context

• .formats lists supported file formats for the load/save commands

• .mat pushes the last query result back into the context as a new table.

• .synonym creates an alias for a table

• .set sets a variable in the current workspace

• .settings lists all settings for the current workspace

settings

Workspace settings are used to express persistent preferences for lokql operations. Note that setting names are case-insensitive. Setting values may be interpreted as strings, numbers or boolean flags. For boolean flags, acceptable values are true,false,yes,no,on,off,1,0. For example .set csv.trimCells no

• kusto.datapath specifies a set of folders that will be searched when loading data files. A semi-colon is used to separated multiple entries. The first folder in the list is used when saving files.

• csv.trimcells causes the values of cells in a CSV/TSV file to have leading/trailing whitespace removed when inserted into a table. The default values is true.

• csv.inferColumnTypes causes lokqldx to attempt to infer the "type" of columns based on the contents of the cells. This is necessary because CSV files are "just text". Type-inference is usually desirable but can go wrong when dealing with columns that look like numbers but which are intended to be treated as text. (Phone numbers or long numeric serial numbers are a good example). Setting .set csv.infercolumntypes off will result in all columns being loaded as text, in which case you'll need to use the KQL casting operations for those that you want to treat as different types.

Rendering

LokqlDX supports the KQL render command via the VegaGenerator library. Vega is a javascript based charting library and charts will currently only render if you have internet connectivity.

The following chart types are supported:

• columnchart
• barchart
• linechart
• piechart
• areachart
• stackedareachart
• scatterchart
• ladderchart

Support for the with (...) syntax is limited but with (title= '..') is supported.

In general, charts will be rendered "as expected" but if you're not getting the results you want, you may wish to use a project operator prior to rendering to ensure that columns are interpreted in the expected order.

In general columns are expected to be provided in the following order:

• X axis
• Y axis
• Series (color automatically assigned)
• Shape (automatically assigned)

For laddercharts the order is X1,X2,Series

Charts are rendered with a button that allows them to be saved or opened in the online Vega-editor for further customisation..

Macros

lokqldx supports macros which allow for easier repetition of similar queries. A macro can be defined using a block of code similar to

.define countby table column

$table 
| summarize count() by $column

.end

This block must be executed to define the macro (the easiest way to do this is to include it in a script file then .run the script).

Macros can be executed using the .m command

.m countby mydata Name

Application Insights

Lokqldx supports issuing KQL queries to Application insights logs using the .appinsights command ...more to follow

ChatGpt Copilot

Lokqldx has an experimental copilot feature that allows you to use a ChatGPT assistant to perform natural-language querying.

Setup

You must have a chatgpt API key.
Set the copilot variable in your project then load some data..

Then switch to the "copilot" tab and enter a query such as show me the number of devices built by year as a piechart in the lower (cyan) section

With luck, chatgpt will generate the appropriate kql and lokqldx with present the result!