Basic Syntax
Basic Syntax
Basic Selecting and Filtering Data
To select unfiltered data we just enter the name of the table and press Run query
table
As you will see this returns many columns. We can now limit the columns with the project keyword. Each line is passed the data from the previous using the pipe symbol.
table
| project name, location, resourceGroup
To rename the columns in the display we can prefix them with our alias
table
| project name, location, RG=resourceGroup
We can filter down the results using a where clause to only show web app resources in a resource group named demo.
table
| project name, location, resourceGroup
| where ['type'] == 'microsoft.web/sites' and resourceGroup == "demo"
Like Operator
table
| where type contains "blob"
//Or
table
| where type matches regex "^Azure.*"
//Or
table
| where type startswith "Azure"
Basic Aggregation
//SELECT name, AVG(duration) FROM table GROUP BY name
table
| summarize avg(duration) by name
//SELECT name, COUNT(DISTINCT type) FROM table GROUP BY name
table
| summarize by name, type | summarize count() by name
//count of the website resources we have, grouped by resource group name.
table
| project name, location, resourceGroup
| where ['type'] == 'microsoft.web/sites'
| summarize count() by resourceGroup, subscriptionId
Joining Data
//SELECT * FROM dependencies LEFT OUTER JOIN exceptions ON dependencies.operation_Id = //exceptions.operation_Id
dependencies
| join kind = leftouter
(exceptions)
on $left.operation_Id == $right.operation_Id
Another example:
resources
| join kind=inner (
resourcecontainers
| where type == 'microsoft.resources/subscriptions'
| project subscriptionId, subscriptionName = name)
on subscriptionId
| summarize count() by resourceGroup, subscriptionName
JSON data
Sometimes data in a single column will be returned in a json format, take for example the SKU on a resource.
//Sometimes data in a single column will be returned in a json format, take for example the SKU on a resource
table
| where type == "microsoft.web/serverfarms"
| project sku
//We can turn this into separate columns to either filter or project with the expand keyword.
table
| where type == "microsoft.web/serverfarms"
| extend size = tostring(sku["size"])
| extend tier = tostring(sku["tier"])
| project size, tier
Distinct
//SELECT DISTINCT name, type FROM dependencies
dependencies
| summarize by name, type
Order by
//SELECT name, timestamp FROM dependencies ORDER BY timestamp ASC
dependencies
| project name, timestamp
| sort by timestamp asc nulls last
Union
//SELECT * FROM dependencies WHERE timestamp > ...UNION SELECT * FROM exceptions WHERE timestamp > ...
dependencies
| where timestamp > ago(1d)
| union
(exceptions
| where timestamp > ago(1d))
Nested Queries
/*SELECT * FROM dependencies
WHERE resultCode ==
(SELECT TOP 1 resultCode FROM dependencies
WHERE resultId = 7
ORDER BY timestamp DESC)
*/
dependencies
| where resultCode == toscalar(
dependencies
| where resultId == 7
| top 1 by timestamp desc
| project resultCode)
Having
/*SELECT COUNT(*) FROM dependencies
GROUP BY name
HAVING COUNT(*) > 3
*/
dependencies
| summarize Count = count() by name
| where Count > 3