LokqlDx ‐ commands - NeilMacMullen/kusto-loco GitHub Wiki
Note that all commands are prefixed with a period - eg .load.
load, ld loads a data file. Supported formats are
csv, tsv, json, parquet and text.
The table name defaults to the file name.
If the path is not rooted, the file is
searched for in path set by kusto.datapath
If the table already exists, it will not be
reloaded unless the -f option is used.
When loading text files, a single column
named 'Line' is created.
Examples:
.load c:\temp\data.csv
.load d.parquet data2
save, sv save results to file.
Supported formats are
csv,tsv,parquet.json,text
If the path is not rooted, the file is stored
in kusto.datapath
If the name of the result is not specified,
the most recent result is saved.
Examples:
.save c:\temp\data.csv #saves the most
recent result to a csv file
.save d.parquet abc #saves a named
result called 'abc' to a parquet file
set sets a value in the settings table
Settings are used to control the behavior of
the engine and can be used as internal
variables
Use the .settings command to show the current
value of all settings
Use the .knownsettings command to show the
list of 'engine' settings
Examples:
.set csv.separator ; #Tell csv loader to
use semi-colon separated columns
.set col LongName #Allow queries to
reference 'LongName' using $col
settings shows all entries in the settings table
knownsettings lists all setting definitions known to the
engine
Examples:
.knownsettings #show all known
settings
.knownsettings csv #show known settings
that related to csv processing
push names and stores the previous result so that
it can be used later without rerunning the
query
Examples:
.push result1
... run some other queries....
.save data.csv result1 #save the earlier
result to a file
pull retrieves a stored result and displays it
results shows the list of stored results
addtable, csvdata loads data from formatted inline text and
adds a table
The first line is assumed to be the header
row. Separators are inferred from
the first row and items are trimmed by
default
Examples:
.addtable ages
Name | Age
Alice | 30
Bob | 28
The table name is optional and defaults to
'data' if not provided.
listtables, ls, alltables, at Lists all available tables
materialize, materialise, mat saves a result back into the context as a
table
Examples:
.materialize t #
materializes the last result as a table
called 't'
.materialize t storedResult #
materializes a stored result as a table
called 't'
synonym, syn, alias Allows a table to be referred to using an
alternate name
Examples:
.synonym ['awkward table name'] t
#allows the awkward table to be referred
to as 't'
fileFormats, fmts list supported file formats for save/load
appinsights, ai Runs a query against application insights
The resourceId is the full resourceId of the
application insights instance which can be
obtained
from the JSON View of the Insights resource
in the Azure portal.
The timespan is a duration string which can
be in the format of '7d', '30d', '1h' etc.
If not specified, the default is 24 hours.
The TenantId is the tenant-id of the
subscription that contains the application
insights instance.
This is optional but may be required if you
have multiple tenants or if the application
insights
instance is in a different tenant than the
one you are currently logged into.
The tenant id can be specified as a prefix to
the resourceId separated with a colon.
Examples:
.set appservice /subscriptions/12a....
.appinsights $appservice 7d
traces | where message contains 'error'
.appinsights $appservice 30d
exceptions
| summarize count() by outerMessage
| render piechart
.set tenantid "ae...."
.set svc "$tenantid:$appservice"
.appinsights $svc 1d
traces | order by timestamp | take 100
adx Runs a query against adx
The connection-string is the full connection
string to the ADX cluster, which can
be found in the Azure portal.
The database is the name of the database.
The database and connection-string can be
combined into a single string
in the format database@connection-string,
e.g.
mydb@https://myadx.cluster.kusto.windows.net
Examples:
.set cluster https://help.kusto.windows.net/
.set database SampleLogs
.set path $database@$cluster
.adx $path
RawSysLogs
| extend Cpu=tostring(tags.cpu_id)
| summarize N=count() by Day =
bin(timestamp,1d),Cpu
| render linechart
"
startreport starts building a report
Currently only the pptx format is supported
The template argument must point to a valid
pptx file which will be used as a template
Examples:
.startreport pptx
c:\reports\weeklytemplate.pptx
.addtoreport text *name-of-title-element*
"my report"
.addtoreport image *name-of-image-element*
storedresult1
.finishreport c:\reports\thisweek.pptx
addtoreport add results to the active report
- Type must be one of image,table, text, or
literal
- Element must be the name of an element in
the template file. If the element
is not found, a new element may be added at
the end of the report
- ResultName is the name of a stored result,
'_' to indicate the most recent result, or
left blank to use a result of the same name
as the template element.
- For elements of type 'text', only cell
(0,0) is used to generate the text.
- Type 'literal' is the same as 'text' except
that the ResultName is treated as a literal
string.
Examples:
.addtoreport image chart1 exceptionResult
.addtoreport literal title "this is the
title of my report"
finishreport finish a report by saving it out as a file
echo writes the supplied text to the output
window. This can be useful when executing a
series of
long-running operations.
Examples:
echo "starting long-running query"
pivotColumnsToRows pivots columns into rows
The pivotColumnsToRows command is useful for
transforming data that has multiple columns
that would be better expressed as a single
column with a type value.
For example, this table
Year | Sea | Land | Air
2020 | 100 | 200 | 300
might be better expressed as
Year | Mode | Distance
2020 | Sea | 100
2020 | Land | 200
2020 | Air | 300
.pivotColumnsToRows --Columns Sea Land Air
--ValuesInto Distance --ColumnNamesInto Mode
pivotRowsToColumns pivots values in a column into new column
names
The pivotRowsToColumns command is useful for
transforming data that is split across
multiple rows into a single row.
For example, this table
Year | Mode | Distance
2020 | Sea | 100
2020 | Land | 200
2020 | Air | 300
can be collapsed into
Year | Sea | Land | Air
2020 | 100 | 200 | 300
using pivotRowsToColumns --as --columnsFrom
Mode --valueFrom Distance
Usage:
.pivotRowsToColumns [result-name] --as
new_table_name --columnsFrom Mode
--ValueFrom Distance
setscalar Sets the value in cell 0,0 of the named
result as a value in the settings table
Examples:
print format_datetime(now(),'yyyy-MM-dd')
.setscalar file
.save $(file).csv earlier_result
loadexcel loads multiple tables from an excel
spreadsheet.
Tables are named after the worksheet that
contains them, with an optional prefix.
render, ren render last results as html and opens with
browser
exit, quit Exit application
display, d change the output format
run, script, r run a script
savequery, sq save the previous query to a file so you can
reuse it
query, q run a multi-line query from a file
define, def Defines a Macro
macro, m runs a macro
sleep Sleep for a number of seconds (default is 1)
Primarily used for testing but could be used
for a basic slide-show
copilot start/run copilot
help Display more information on a specific
command.
version Display version information.