Insights Management Commands

Insights management commands allow users express more control over or to gather additional information on their data system. The commands fall into two primary categories:

1. Management - These commands allow users to view more information about queries, commands and retention as well as alter retention
2. Data Control - These commands allow users to create and drop custom tables, ingest local and cloud data and purge data from their DB

Some commands aren't available in evaluation mode.

Category	Commands
Management	.show queries .show running queries .show commands-and-queries .show table policy retention .alter table policy retention
Data Control	.create table .drop table .set .append .set-or-append .set-or-replace .ingest into table .purge table

Management

.show queries
Returns a list of currently executing queries by the user, or by another user, or by all users.
Usage: .show queries
Example: .show queries | where StartedOn > ago(1d)
This shows all queries that executed in the last day

.show running queries
Shows all currently executing queries
Usage: .show running queries
Example: .show running queries
This shows all currently running queries.

.show database policy caching
Shows the current caching policy on the database
Usage: .show database DatabaseName policy caching
Example: .show database myDatabase policy caching
This shows the current caching policy for the specified database.

.show table policy caching
Shows the current caching policy on the table
Usage: .show database DatabaseName.TableName policy caching
Example: .show database myDatabase.myTable policy caching
This shows the current caching policy for the specified table.

.show commands-and-queries
Returns a table with admin commands and queries that have reached a final state. These commands and queries are available to query for 30 days.
Usage: .show commands-and-queries
Example: .show commands-and-queries | where StartedOn > ago(1d) | where State != "Completed"
This Shows all commands and queries that failed in the last day

.show table policy retention
Shows tables' effective retention policy taking cluster and database rules into account
Usage: .show table(s) (<table_name> [, ...]) policy retention
Example: .show table ['events.all'] policy retention
This shows the current retention policy for the "events.all" table.

.alter table policy retention (restricted to accounts on consumption pricing model)
Change the current retention policy on table(s) to <retention_policy>
Usage: .alter tables (<table_name> [, ...]) policy retention <retention_policy>
Example: .alter table ['events.all'] policy retention softdelete = 90d
This sets data in the "events.all" table to be removed from the table after 90 days.

Data Control

.create table (restricted to performance level 2 and above)
Creates a new empty table. The command must run in context of a specific database. If the table already exists the command will return success.
Usage: .create table TableName ([columnName:columnType], ...)
Example: .create table ['custom.logs'] (Level:string, Timestamp:datetime, Id:string, Message:string)
This creates a new table called "custom.logs" with four columns. IMPORTANT Custom tables must begin with "custom.".

.drop table (restricted to performance level 2 and above)
Drops the specified table. Note: This can't be undone.
Usage: .drop table TableName [ifexists]
Example: .drop table ['custom.logs']
This drops the table named "custom.logs".

.set (restricted to performance level 2 and above)
Creates a table with results of Query Or Command
Usage: .set TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand
Example: .set [‘custom.recentEvents’] <| [‘events.all’] | where Timestamp > now() – time(1h)
This creates a table "custom.recentEvents" containing the results of the above query

.append (restricted to performance level 2 and above)
Appends an existing table with results of QueryOrCommand
Usage: .append TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand
Example: .append [‘custom.recentEvents’] <| [‘events.all’] | where Timestamp > now() – time(1h)
This adds to existing table "custom.recentEvents" with the results of the above query

.set-or-append (restricted to performance level 2 and above)
Create or appends to a table with results of QueryOrCommand
Usage: .set-or-append TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand
Example: .set-or-append [‘custom.weekEvents’] <| [‘events.all’] | where Timestamp > now() – time(7d)
This appends data from the above query to table "custom.weekEvents". If the table doesn't exist, create it.

.set-or-replace (restricted to performance level 2 and above)
Replaces the data of the table if it exists (drops the existing data shards), or creates the target table if doesn't already exist. The table schema is preserved unless one of extend_schema or recreate_schema ingestion property is set to true. If the schema is modified, this happens before the actual data ingestion in its own transaction, so a failure to ingest the data doesn't mean the schema wasn't modified.
Usage: .set-or-replace TableName [with (PropertyName = PropertyValue [, ...])] <| QueryOrCommand
Example: .set-or-replace [‘custom.dayEvents’] <| [‘events.all’] | where Timestamp > now() – time(1d)
This replaces the data in table "custom.dayEvents" with the above query.

.ingest into (restricted to performance level 2 and above)
Ingests data into a table by "pulling" the data from one or more cloud storage artifacts.
Usage: .ingest into table TableName SourceDataLocator [with ( IngestionPropertyName = IngestionPropertyValue [, ...] )]
Example: .ingest into table [‘custom.myData’] (h’<your url here>’) with(ignoreFirstRecord=true)
This pushes data into table "custom.myData" from your cloud storage listed in the url.

.purge table (restricted to performance level 2 and above)
Permanently deletes data in table from the database
Usage: .purge table [TableName] in database [DatabaseName] allrecords with (noregrets='true')
Example: .purge table [‘custom.toPurge’] in database MyDatabase allrecords
This purges all data from table "custom.toPurge", permanently deleting it from your data system.