Skip to main content

Azure Data Explorer

This Integration is part of the Azure Data Explorer Pack.#

Supported versions

Supported Cortex XSOAR versions: 6.0.0 and later.

Use the Azure Data Explorer integration to collect and analyze data inside Azure Data Explorer clusters, and to manage search queries. This integration was integrated and tested with version V1 of AzureDataExplorer.

Configure Azure Data Explorer on Cortex XSOAR#

  1. Navigate to Settings > Integrations > Servers & Services.

  2. Search for Azure Data Explorer.

  3. Click Add instance to create and configure a new integration instance.

    ParameterDescriptionRequired
    Cluster URL (e.g. https://help.kusto.windows.net)True
    Application IDTrue
    Client Activity PrefixA customized prefix of the client activity identifier for the query execution. For example, for a prefix value of 'XSOAR-DataExplorer', the client activity ID will be in the format of: 'XSOAR-DataExplorer;<UUID>'.True
    Trust any certificate (not secure)False
    Use system proxy settingsFalse
  4. Click Test to validate the URLs, token, and connection.

Commands#

You can execute these commands from the Cortex XSOAR CLI, as part of an automation, or in a playbook. After you successfully execute a command, a DBot message appears in the War Room with the command details.

azure-data-explorer-search-query-execute#


Execute a Kusto Query Language (KQL) query against the given database inside a cluster. The Kusto query is a read-only request to process data and return results. To learn more about KQL go to https://docs.microsoft.com/en-us/azure/kusto/query/.

Base Command#

azure-data-explorer-search-query-execute

Input#

Argument NameDescriptionRequired
queryKusto Query Language (KQL) search query to execute on given database.Required
database_nameThe name of the database to execute the query on.Required
timeoutThe timeout for the execution of the search query on the server side. The timeout is a float number in minutes that ranges from 0 to 60. Default is 5.Optional

Context Output#

PathTypeDescription
AzureDataExplorer.SearchQueryResults.QueryStringThe executed query on the given database.
AzureDataExplorer.SearchQueryResults.ClientActivityIDStringThe Client Activity ID. A unique identifier of the executed query.
AzureDataExplorer.SearchQueryResults.PrimaryResultsUnknownThe results of the query execution.
AzureDataExplorer.SearchQueryResults.DatabaseStringThe database against which the query will be executed.

Command Example#

!azure-data-explorer-search-query-execute database_name=Samples query="StormEvents| limit 1"

Context Example#

{
"AzureDataExplorer": {
"SearchQueryResults": {
"ClientActivityID": "XSOAR-DataExplorer;759d43a9-cdc1-4882-8f8b-3e8d8a703f1e",
"Database": "Samples",
"PrimaryResults": [
{
"BeginLat": 28.0393,
"BeginLocation": "MELBOURNE BEACH",
"BeginLon": -80.6048,
"DamageCrops": 0,
"DamageProperty": 0,
"DeathsDirect": 0,
"DeathsIndirect": 0,
"EndLat": 28.0393,
"EndLocation": "MELBOURNE BEACH",
"EndLon": -80.6048,
"EndTime": "2007-09-29T08:11:00",
"EpisodeId": 11091,
"EpisodeNarrative": "Showers and thunderstorms lingering along the coast produced waterspouts in Brevard County.",
"EventId": 61032,
"EventNarrative": "A waterspout formed in the Atlantic southeast of Melbourne Beach and briefly moved toward shore.",
"EventType": "Waterspout",
"InjuriesDirect": 0,
"InjuriesIndirect": 0,
"Source": "Trained Spotter",
"StartTime": "2007-09-29T08:11:00",
"State": "ATLANTIC SOUTH",
"StormSummary": {
"Details": {
"Description": "A waterspout formed in the Atlantic southeast of Melbourne Beach and briefly moved toward shore.",
"Location": "ATLANTIC SOUTH"
},
"EndTime": "2007-09-29T08:11:00.0000000Z",
"StartTime": "2007-09-29T08:11:00.0000000Z",
"TotalDamages": 0
}
}
],
"Query": "StormEvents| limit 1"
}
}
}

Human Readable Output#

Results of executing search query with client activity ID: XSOAR-DataExplorer;759d43a9-cdc1-4882-8f8b-3e8d8a703f1e#

Begin LatBegin LocationBegin LonDamage CropsDamage PropertyDeaths DirectDeaths IndirectEnd LatEnd LocationEnd LonEnd TimeEpisode IdEpisode NarrativeEvent IdEvent NarrativeEvent TypeInjuries DirectInjuries IndirectSourceStart TimeStateStorm Summary
28.0393MELBOURNE BEACH-80.6048000028.0393MELBOURNE BEACH-80.60482007-09-29T08:11:0011091Showers and thunderstorms lingering along the coast produced waterspouts in Brevard County.61032A waterspout formed in the Atlantic southeast of Melbourne Beach and briefly moved toward shore.Waterspout00Trained Spotter2007-09-29T08:11:00ATLANTIC SOUTHTotalDamages: 0
StartTime: 2007-09-29T08:11:00.0000000Z
EndTime: 2007-09-29T08:11:00.0000000Z
Details: {"Description": "A waterspout formed in the Atlantic southeast of Melbourne Beach and briefly moved toward shore.", "Location": "ATLANTIC SOUTH"}

azure-data-explorer-search-query-list#


List search queries that have reached a final state in the given database. A database admin or database monitor can see any command that was invoked on their database. Other users can only see queries that they themselves invoked.

Base Command#

azure-data-explorer-search-query-list

Input#

Argument NameDescriptionRequired
database_nameThe name of the database from which to list the completed search queries. .Required
client_activity_idThe client activity ID property of the search query. Use this value to get a specific search query.Optional
limitThe maximum number of completed queries to return. Default is 50.Optional
pageThe page number from which to start a search. Default is 1.Optional
page_sizeThe maximum number of completed queries to return per page. If this argument is not provided, an automatic pagination will be made according to the limit argument.Optional

Context Output#

PathTypeDescription
AzureDataExplorer.SearchQuery.ClientActivityIdStringThe client activity ID. A unique identifier of the query execution.
AzureDataExplorer.SearchQuery.TextStringThe search query text.
AzureDataExplorer.SearchQuery.DatabaseStringThe name of the database that the search query is run on.
AzureDataExplorer.SearchQuery.StartedOnDateThe query execution start time in UTC.
AzureDataExplorer.SearchQuery.LastUpdatedOnDateThe last update time of the query.
AzureDataExplorer.SearchQuery.DurationDateThe search query runtime.
AzureDataExplorer.SearchQuery.StateStringThe search query state.
AzureDataExplorer.SearchQuery.RootActivityIdStringThe root activity ID.
AzureDataExplorer.SearchQuery.UserStringThe user who performed the query.
AzureDataExplorer.SearchQuery.FailureReasonStringThe reason for query failure.
AzureDataExplorer.SearchQuery.TotalCpuStringThe total CPU clock time (User mode + Kernel mode) consumed by this query.
AzureDataExplorer.SearchQuery.CacheStatistics.Memory.HitsNumberThe number of cache hits.
AzureDataExplorer.SearchQuery.CacheStatistics.Memory.MissesNumberThe number of cache misses.
AzureDataExplorer.SearchQuery.CacheStatistics.Disk.HitsNumberThe number of disk hits.
AzureDataExplorer.SearchQuery.CacheStatistics.Disk.MissesNumberThe number of disk misses.
AzureDataExplorer.SearchQuery.CacheStatistics.Shards.Hot.HitBytesNumberThe amount of data (in bytes) which was found in the hot data cache of the table's extents, during the search query execution.
AzureDataExplorer.SearchQuery.CacheStatistics.Shards.Hot.MissBytesNumberThe amount of data (in bytes) which was not found in the hot data cache of the table's extents, during the search query execution.
AzureDataExplorer.SearchQuery.CacheStatistics.Shards.Hot.RetrieveBytesNumberThe amount of data (in bytes) that was retrieved from hot data cache of the table's extents, during the search query execution.
AzureDataExplorer.SearchQuery.CacheStatistics.Shards.Cold.HitBytesNumberThe amount of data (in bytes) which was found in the cold data cache of the table's extents, during the search query execution.
AzureDataExplorer.SearchQuery.CacheStatistics.Shards.Cold.MissBytesNumberThe amount of data (in bytes) which was not found in the cold data cache of the table's extents, during the search query execution.
AzureDataExplorer.SearchQuery.CacheStatistics.Shards.Cold.RetrieveBytesNumberThe amount of data (in bytes) that was retrieved from cold data cache during the search query execution.
AzureDataExplorer.SearchQuery.CacheStatistics.Shards.BypassBytesNumberThe amount of data (in bytes) that was bypassed (reloaded) in the cache of the table's extents during the search query execution.
AzureDataExplorer.SearchQuery.ApplicationStringThe application name that invoked the command.
AzureDataExplorer.SearchQuery.MemoryPeakNumberThe peak memory usage of the query execution.
AzureDataExplorer.SearchQuery.ScannedExtentsStatistics.MinDataScannedTimeDateThe minimum data scan time.
AzureDataExplorer.SearchQuery.ScannedExtentsStatistics.MaxDataScannedTimeDateThe maximum data scan time.
AzureDataExplorer.SearchQuery.ScannedExtentsStatistics.TotalExtentsCountNumberThe total number of extents which were used during the query execution.
AzureDataExplorer.SearchQuery.ScannedExtentsStatistics.ScannedExtentsCountNumberThe number of extents which were scanned during the query execution.
AzureDataExplorer.SearchQuery.ScannedExtentsStatistics.TotalRowsCountNumberThe total row count of extents which were used during the query execution.
AzureDataExplorer.SearchQuery.ScannedExtentsStatistics.ScannedRowsCountNumberThe number of scanned rows of an extent during query execution.
AzureDataExplorer.SearchQuery.PrincipalStringThe principal that invoked the query.
AzureDataExplorer.SearchQuery.ClientRequestProperties.SecurityTokenPresentBooleanWhether the security token is present in the request or not.
AzureDataExplorer.SearchQuery.ClientRequestProperties.AuthorizationSchemeStringThe authorization scheme.
AzureDataExplorer.SearchQuery.ClientRequestProperties.RequestHostNameStringThe hostname of the request.
AzureDataExplorer.SearchQuery.ClientRequestProperties.LocalClusterNameStringThe cluster name.
AzureDataExplorer.SearchQuery.ClientRequestProperties.OriginClusterNameStringThe origin cluster name.
AzureDataExplorer.SearchQuery.ClientRequestProperties.Options.api_versionStringThe API version.
AzureDataExplorer.SearchQuery.ClientRequestProperties.Options.request_readonlyBooleanWhether the request is read-only or not.
AzureDataExplorer.SearchQuery.ClientRequestProperties.Options.servertimeoutNumberThe server timeout value.
AzureDataExplorer.SearchQuery.ClientRequestProperties.Options.servertimeoutoriginStringThe server timeout origin.
AzureDataExplorer.SearchQuery.ClientRequestProperties.Options.query_datascopeNumberThe query datascope.
AzureDataExplorer.SearchQuery.ClientRequestProperties.Options.query_fanout_nodes_percentNumberThe percentage of the query nodes in the cluster to use per subquery distribution operation.
AzureDataExplorer.SearchQuery.ClientRequestProperties.Options.query_fanout_threads_percentNumberThe percentage of CPUs the cluster will assign on each node.
AzureDataExplorer.SearchQuery.ClientRequestProperties.Options.maxmemoryconsumptionperiteratorNumberThe maximum amount of memory that a single query plan result set iterator can hold.
AzureDataExplorer.SearchQuery.ClientRequestProperties.Options.max_memory_consumption_per_query_per_nodeNumberThe maximum amount of memory that can be used on a single node for a specific query.
AzureDataExplorer.SearchQuery.ClientRequestProperties.Options.truncationmaxsizeNumberThe maximum overall data size returned by the query, in bytes.
AzureDataExplorer.SearchQuery.ClientRequestProperties.Options.truncationmaxrecordsNumberThe maximum number of records returned by the query.
AzureDataExplorer.SearchQuery.ResultSetStatistics.TableCountNumberThe number of tables that were retrieved following search query execution.
AzureDataExplorer.SearchQuery.ResultSetStatistics.TablesStatistics.RowCountNumberThe row count of the table retrieved following search query execution.
AzureDataExplorer.SearchQuery.ResultSetStatistics.TablesStatistics.TableSizeNumberThe total size in bytes of the table retrieved following search query execution.
AzureDataExplorer.SearchQuery.WorkloadGroupStringThe workload group which the query was assigned to. The query is executed using the policies assigned to the workload group. There are two pre-defined workload groups (internal and default) and up to 10 custom workload groups which may be defined at the cluster level.

Command Example#

!azure-data-explorer-search-query-list database_name=Samples limit=1

Context Example#

{
"AzureDataExplorer": {
"SearchQuery": {
"Application": "KusWeb",
"CacheStatistics": {
"Disk": {
"Hits": 0,
"Misses": 0
},
"Memory": {
"Hits": 0,
"Misses": 0
},
"Shards": {
"BypassBytes": 0,
"Cold": {
"HitBytes": 0,
"MissBytes": 0,
"RetrieveBytes": 0
},
"Hot": {
"HitBytes": 0,
"MissBytes": 0,
"RetrieveBytes": 0
}
}
},
"ClientActivityId": "KustoWebV2;f1be2c7e-f810-437b-a1f8-f8bbbedf238d",
"ClientRequestProperties": {
"AuthorizationScheme": "Bearer",
"LocalClusterName": "https://help.kusto.windows.net/",
"Options": {
"api_version": "v2",
"max_memory_consumption_per_query_per_node": 2000000000,
"maxmemoryconsumptionperiterator": 5368709120,
"query_datascope": 1,
"query_fanout_nodes_percent": 100,
"query_fanout_threads_percent": 100,
"query_language": "csl",
"queryconsistency": "strongconsistency",
"request_app_name": "KusWeb",
"request_readonly": true,
"request_readonly_hardline": false,
"servertimeout": 600000000,
"truncationmaxrecords": 500000,
"truncationmaxsize": 67108864
},
"OriginClusterName": "https://help.kusto.windows.net/",
"RequestHostName": "https://help.kusto.windows.net:443/",
"SecurityTokenPresent": true
},
"Database": "Samples",
"Duration": "0:00:00",
"FailureReason": "[none]",
"LastUpdatedOn": "2021-11-24T15:15:27",
"MemoryPeak": 0,
"Principal": "aaduser=xxx-xxxx-xxxx",
"ResultSetStatistics": {
"TableCount": 2,
"TablesStatistics": [
{
"RowCount": 0,
"TableSize": 0
},
{
"RowCount": 2,
"TableSize": 1244
}
]
},
"RootActivityId": "2b9e0ec8-f6b0-407e-90b6-68eba3777564",
"ScannedExtentsStatistics": {
"MaxDataScannedTime": null,
"MinDataScannedTime": null,
"ScannedExtentsCount": 0,
"ScannedRowsCount": 0,
"TotalExtentsCount": 0,
"TotalRowsCount": 0
},
"StartedOn": "2021-11-24T15:15:27",
"State": "Completed",
"Text": "set notruncation;\nCovid19",
"TotalCpu": "0:00:00",
"User": "dataExplorer@qmasterslabgmail.onmicrosoft.com",
"WorkloadGroup": "default"
}
}
}

Human Readable Output#

List of Completed Search Queries#

Showing 0 to 1 records out of 134.

Client Activity IdUserTextDatabaseStarted OnLast Updated OnState
KustoWebV2;f1be2c7e-f810-437b-a1f8-f8bbbedf238ddataExplorer@qmasterslabgmail.onmicrosoft.comset notruncation;
Covid19
Samples2021-11-24T15:15:272021-11-24T15:15:27Completed

azure-data-explorer-running-search-query-list#


List currently executing search queries in the given database. A database admin or database monitor can see any search query that was invoked on their database. Other users can only see search queries that they themselves invoked.

Base Command#

azure-data-explorer-running-search-query-list

Input#

Argument NameDescriptionRequired
database_nameThe database name.Required
client_activity_idThe client activity ID property of the search query. Use this to get a specific running search query.Optional
limitThe maximum number of running queries to return. Default is 50.Optional
pageThe page number from which to start a search. Default is 1.Optional
page_sizeThe maximum number of running queries to return per page. If this argument is not provided, an automatic pagination will be made according to the limit argument.Optional

Context Output#

PathTypeDescription
AzureDataExplorer.RunningSearchQuery.ClientActivityIdStringThe client activity ID. A unique identifier of the query execution.
AzureDataExplorer.RunningSearchQuery.TextStringThe search query text.
AzureDataExplorer.RunningSearchQuery.DatabaseStringThe name of the database that the search query is run on.
AzureDataExplorer.RunningSearchQuery.StartedOnDateThe query execution start time in UTC.
AzureDataExplorer.RunningSearchQuery.LastUpdatedOnStringThe last update time of the query.
AzureDataExplorer.RunningSearchQuery.DurationDateThe search query runtime duration.
AzureDataExplorer.RunningSearchQuery.StateStringThe search query state.
AzureDataExplorer.RunningSearchQuery.RootActivityIdStringThe root activity ID.
AzureDataExplorer.RunningSearchQuery.UserStringThe user who performed the query.
AzureDataExplorer.RunningSearchQuery.FailureReasonStringThe reason for query failure.
AzureDataExplorer.RunningSearchQuery.TotalCpuStringThe total CPU clock time (User mode + Kernel mode) consumed by this query.
AzureDataExplorer.RunningSearchQuery.CacheStatisticsUnknownThe cache statistics.
AzureDataExplorer.RunningSearchQuery.ApplicationStringThe application name that invoked the command.
AzureDataExplorer.RunningSearchQuery.MemoryPeakNumberThe peak memory usage of the running query execution.
AzureDataExplorer.RunningSearchQuery.ScannedExtentsStatisticsUnknownThe scanned extent count.
AzureDataExplorer.RunningSearchQuery.PrincipalStringThe principal that invoked the query.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.SecurityTokenPresentBooleanWhether the security token is present in the request or not.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.AuthorizationSchemeStringThe authorization scheme.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.RequestHostNameStringThe hostname of the request.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.LocalClusterNameStringThe cluster name.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.OriginClusterNameStringThe origin cluster name.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.Options.api_versionStringThe API version.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.Options.request_readonlyBooleanWhether the request is read-only or not.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.Options.servertimeoutNumberThe server timeout value.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.Options.servertimeoutoriginStringThe server timeout origin.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.Options.query_datascopeNumberThe query datascope.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.Options.query_fanout_nodes_percentNumberThe percentage of the query nodes in the cluster to use per subquery distribution operation.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.Options.query_fanout_threads_percentNumberThe percentage of CPUs the cluster will assign on each node.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.Options.maxmemoryconsumptionperiteratorNumberThe maximum amount of memory that a single query plan result set iterator can hold.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.Options.max_memory_consumption_per_query_per_nodeNumberThe maximum amount of memory that can be used on a single node for a specific query.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.Options.truncationmaxsizeNumberThe maximum overall data size returned by the query, in bytes.
AzureDataExplorer.RunningSearchQuery.ClientRequestProperties.Options.truncationmaxrecordsNumberThe maximum number of records returned by the query.
AzureDataExplorer.RunningSearchQuery.ResultSetStatisticsUnknownThe result set statistics.
AzureDataExplorer.RunningSearchQuery.WorkloadGroupStringThe workload group.

Command Example#

!azure-data-explorer-running-search-query-list database_name=Samples limit=1

Context Example#

{
"AzureDataExplorer": {
"RunningSearchQuery": {
"Application": "KusWeb",
"CacheStatistics": "null",
"ClientActivityId": "KustoWebV2;c6ff3e99-d2cb-4a3e-ab05-955ae383a7c6",
"ClientRequestProperties": "{\"SecurityTokenPresent\":true,\"AuthorizationScheme\":\"Bearer\",\"RequestHostName\":\"https://help.kusto.windows.net:443/\",\"LocalClusterName\":\"https://help.kusto.windows.net/\",\"OriginClusterName\":\"https://help.kusto.windows.net/\",\"Options\":{\"servertimeout\":600000000,\"queryconsistency\":\"strongconsistency\",\"query_language\":\"csl\",\"request_readonly\":true,\"request_readonly_hardline\":false,\"api_version\":\"v2\",\"request_app_name\":\"KusWeb\",\"query_datascope\":1,\"query_fanout_nodes_percent\":100,\"query_fanout_threads_percent\":100,\"maxmemoryconsumptionperiterator\":5368709120,\"max_memory_consumption_per_query_per_node\":2000000000,\"truncationmaxsize\":67108864,\"truncationmaxrecords\":500000}}",
"Database": "Samples",
"Duration": "0:00:00",
"FailureReason": "",
"LastUpdatedOn": "2021-11-24T15:16:34",
"MemoryPeak": 0,
"Principal": "aaduser=xxx-xxxx-xxxx",
"ResultSetStatistics": "null",
"RootActivityId": "c8233607-30a9-4cc0-9c54-ec716e5fc246",
"ScannedExtentsStatistics": "null",
"StartedOn": "2021-11-24T15:16:34",
"State": "InProgress",
"Text": "set notruncation;\nCovid19_Bing",
"TotalCpu": "0:00:00",
"User": "dataExplorer@qmasterslabgmail.onmicrosoft.com",
"WorkloadGroup": "default"
}
}
}

Human Readable Output#

List of Currently running Search Queries#

Showing 0 to 1 records out of 2.

Client Activity IdUserTextDatabaseStarted OnLast Updated OnState
KustoWebV2;c6ff3e99-d2cb-4a3e-ab05-955ae383a7c6dataExplorer@qmasterslabgmail.onmicrosoft.comset notruncation;
Covid19_Bing
Samples2021-11-24T15:16:342021-11-24T15:16:34InProgress

azure-data-explorer-running-search-query-cancel#


Starts a best-effort attempt to cancel a specific running search query in the specified database.

Base Command#

azure-data-explorer-running-search-query-cancel

Input#

Argument NameDescriptionRequired
client_activity_idThe client activity ID of the query to delete.Required
database_nameThe database name.Required
reasonThe reason for canceling the running query. .Optional

Context Output#

PathTypeDescription
AzureDataExplorer.CanceledSearchQuery.RunningQueryCanceledBooleanWhether the query was successfully canceled or not.
AzureDataExplorer.CanceledSearchQuery.ClientRequestIdStringThe client activity ID of the cancelled query.
AzureDataExplorer.CanceledSearchQuery.ReasonPhraseStringThe reason for canceling the running query.

Command Example#

!azure-data-explorer-running-search-query-cancel database_name=Samples client_activity_id=xxxx-xxxxx-xxxxx

Context Example#

{
"AzureDataExplorer": {
"CanceledSearchQuery": {
"ClientRequestId": "xxxx-xxxxx-xxxxx",
"ReasonPhrase": "None",
"RunningQueryCanceled": false
}
}
}

Human Readable Output#

Canceled Search Query xxxx-xxxxx-xxxxx#

Client Request IdReason PhraseRunning Query Canceled
xxxx-xxxxx-xxxxxNonefalse