{ "metadata": { "kernelspec": { "name": "SQL", "display_name": "SQL", "language": "sql" }, "language_info": { "name": "kusto", "version": "" } }, "nbformat_minor": 2, "nbformat": 4, "cells": [ { "cell_type": "markdown", "source": [ "# Cross-service querying with Kusto Notebook in Azure Data Studio\n", "\n", "This notebook shows how to query Log Analytics workspace from Azure Data Studio Kusto notebook. \n", "\n", "> **Tip**: Ensure that you connect to the ADX cluster with the AAD account that has access to Log Analytics workspace also. \n", "" ], "metadata": { "azdata_cell_guid": "9001d8c9-6602-4938-a50e-41cfa3598f3a" } }, { "cell_type": "markdown", "source": [ "First, change the Kernel to **Kusto** and Attach to **help.kusto.windows.net** and set the database to **Samples**. Run the following query to ensure that it works. " ], "metadata": { "azdata_cell_guid": "4a911ec4-318d-46be-a3b5-7617def51e64" } }, { "cell_type": "code", "source": [ "StormEvents \r\n", "| take 10" ], "metadata": { "azdata_cell_guid": "c68f8fe6-8df4-4a21-a849-73f71ff1fde1" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "Now, we are ready to start querying Log Analytics workspace. For the example below, I'm following the cross-service syntax to connect to Log Analytics.\r\n", "\r\n", "```\r\n", "cluster(https://ade.loganalytics.io/subscriptions//resourcegroups//providers/microsoft.operationalinsights/workspaces/').database(')\r\n", "```\r\n", "\r\n", "You'll need to replace: \r\n", "* `` with your Subscription ID where your Log Analytics workspace live. e.g. `88a1234b-6cc7-1234-a015-a123bc123456` \r\n", "* `` with the Resource Group where your Log Analytics workspace live. e.g `myresourcegroup` \r\n", "* `` with the name of your Log Analytics workspace. e.g. `mssqlgirlla`\r\n", "\r\n", "For full list of syntax including for Azure AppInsights, see [Additional Syntax examples](https://docs.microsoft.com/en-us/azure/data-explorer/query-monitor-data#additional-syntax-examples)." ], "metadata": { "azdata_cell_guid": "717e20fb-0814-41e2-9fb3-5f7fb0fb6eb9" } }, { "cell_type": "code", "source": [ "cluster('https://ade.loganalytics.io/subscriptions/88a1234b-6cc7-1234-a015-a123bc123456/resourcegroups/myresourcegroup/providers/microsoft.operationalinsights/workspaces/mssqlgirlla').database('mssqlgirlla').AzureDiagnostics | take 10\r\n", "| summarize count() by action_name_s, succeeded_s\r\n", "| take 10" ], "metadata": { "azdata_cell_guid": "d728df32-98bd-49e6-8853-752ebe6f16b6", "tags": [] }, "outputs": [ { "output_type": "display_data", "data": { "text/html": "Commands completed successfully." }, "metadata": {} }, { "output_type": "display_data", "data": { "text/html": "Total execution time: 00:00:03.908" }, "metadata": {} }, { "output_type": "execute_result", "metadata": { "resultSet": { "id": 0, "batchId": 0, "rowCount": 5, "complete": false, "columnInfo": [ { "isBytes": false, "isChars": false, "isSqlVariant": false, "isUdt": false, "isXml": false, "isJson": false, "sqlDbType": 0, "isHierarchyId": false, "isUnknownType": false, "allowDBNull": true, "baseCatalogName": "NewDataSet", "baseColumnName": "action_name_s", "baseSchemaName": null, "baseServerName": null, "baseTableName": "Table_0", "columnName": "action_name_s", "columnOrdinal": 0, "columnSize": -1, "isAliased": null, "isAutoIncrement": false, "isExpression": null, "isHidden": null, "isIdentity": null, "isKey": false, "isLong": false, "isReadOnly": false, "isUnique": false, "numericPrecision": null, "numericScale": null, "udtAssemblyQualifiedName": null, "dataType": "System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e", "dataTypeName": "dynamic" }, { "isBytes": false, "isChars": false, "isSqlVariant": false, "isUdt": false, "isXml": false, "isJson": false, "sqlDbType": 0, "isHierarchyId": false, "isUnknownType": false, "allowDBNull": true, "baseCatalogName": "NewDataSet", "baseColumnName": "succeeded_s", "baseSchemaName": null, "baseServerName": null, "baseTableName": "Table_0", "columnName": "succeeded_s", "columnOrdinal": 1, "columnSize": -1, "isAliased": null, "isAutoIncrement": false, "isExpression": null, "isHidden": null, "isIdentity": null, "isKey": false, "isLong": false, "isReadOnly": false, "isUnique": false, "numericPrecision": null, "numericScale": null, "udtAssemblyQualifiedName": null, "dataType": "System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e", "dataTypeName": "dynamic" }, { "isBytes": false, "isChars": false, "isSqlVariant": false, "isUdt": false, "isXml": false, "isJson": false, "sqlDbType": 0, "isHierarchyId": false, "isUnknownType": false, "allowDBNull": true, "baseCatalogName": "NewDataSet", "baseColumnName": "count_", "baseSchemaName": null, "baseServerName": null, "baseTableName": "Table_0", "columnName": "count_", "columnOrdinal": 2, "columnSize": -1, "isAliased": null, "isAutoIncrement": false, "isExpression": null, "isHidden": null, "isIdentity": null, "isKey": false, "isLong": false, "isReadOnly": false, "isUnique": false, "numericPrecision": null, "numericScale": null, "udtAssemblyQualifiedName": null, "dataType": "System.Int64, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e", "dataTypeName": "dynamic" } ], "specialAction": null } }, "execution_count": 5, "data": { "application/vnd.dataresource+json": { "schema": { "fields": [ { "name": "action_name_s" }, { "name": "succeeded_s" }, { "name": "count_" } ] }, "data": [ { "0": "AUDIT SESSION CHANGED", "1": "true", "2": "53" }, { "0": "DATABASE AUTHENTICATION SUCCEEDED", "1": "true", "2": "59" }, { "0": "BATCH COMPLETED", "1": "true", "2": "279" }, { "0": "BATCH COMPLETED", "1": "false", "2": "25" }, { "0": "RPC COMPLETED", "1": "true", "2": "31" } ] }, "text/html": [ "", "", "", "", "", "", "", "
action_name_ssucceeded_scount_
AUDIT SESSION CHANGEDtrue53
DATABASE AUTHENTICATION SUCCEEDEDtrue59
BATCH COMPLETEDtrue279
BATCH COMPLETEDfalse25
RPC COMPLETEDtrue31
" ] } } ], "execution_count": 5 } ] }