Azure Graph Explorer, intro to aggregation functions

KQL and Azure Resource Graph Explorer are perfect for dealing with the large amount of data produced by resource metadata in Azure. Without them, you will need to scan all your subscriptions using Azure PowerShell or Azure CLI, and with a large number of subscriptions, it is challenging.
KQL and Azure Resource Graph Explorer query Azure Metadata of your Azure resources. These metadata are the properties of each resource, and with Graph Explorer you can explore them across your tenant, management group, or subscription.
The language used to run queries in Azure Resource Graph Explorer is KQL. It is an SQL-like language, where you query a table to search for information.

For example, you can use KQL to list all resource group names and their location in your tenant.

resourcecontainers
| where type == "microsoft.resources/subscriptions/resourcegroups"
| project name, location

But as with any other SQL-like language, you can use aggregation to summarize information. For example, instead of having the list of resource groups, I wanted to have the number of resource groups.

resourcecontainers
| where type == "microsoft.resources/subscriptions/resourcegroups"
| count 

Count here, will count the number of records.
You can go further, what if we can have the number of records per Azure Location.

resourcecontainers
| where type == "microsoft.resources/subscriptions/resourcegroups"
| summarize count() by location 

Here we use the keyword summarize to use an aggregation function and the count() function that will count the number of elements from the field provider by the keyword by
You can find the list of aggregation functions on this page

Let’s take some concrete examples that can be used in everyday Cloud engineer tasks.

Often you will need to make some list from a query that could be used in another query. In the example of resource groups. Let's try to create a list of locations used by resource groups.

resourcecontainers
| where type == "microsoft.resources/subscriptions/resourcegroups"
| project location 
| summarize mylist = make_list(location)

But in the result, you will see that there are duplicates.
To create the same list without duplicates we need to use make_set()

resourcecontainers
| where type == "microsoft.resources/subscriptions/resourcegroups"
| project location 
| summarize mylist = make_set(location)

The make_set() function will create a list with unique values.
Aggregation functions work very well to extract numerical values from the query.

Let's see how we can get the average number of websites per Azure web server farm. The number of websites is given by the 'properties.numberOfSites' property. But it is a dynamic value, it can't be used in a function, and it needs to be converted to an Int.
toInt(properties.numberOfSites))

resources
| where type == 'microsoft.web/serverfarms'
| summarize numberOfSite = avg(toint(properties.numberOfSites))

We can also try to get the maximum, and the minimum number of websites as well as the sum of all websites sites in the tenant.

resources
| where type == 'microsoft.web/serverfarms'
| summarize numberOfSite = avg(toInt(properties.numberOfSites))

We can have the same for the maximum number of websites.

resources
| where type == 'microsoft.web/serverfarms'
| summarize numberOfSite = max(toint(properties.numberOfSites))

We can also sum up all websites from all server farms in the tenant.

resources
| where type == 'microsoft.web/serverfarms'
| summarize numberOfSite = sum(toint(properties.numberOfSites))

More complex, we can calculate the standard deviation to calculate the amount of dispersion of a set of values, there are two available functions, stdev for small data samples, and stdevp for larger datasets. Here we have the entire population of websites so stdevp is more appropriate.

resources
| where type == 'microsoft.web/serverfarms'
| summarize  stdevp(toint(properties.numberOfSites))

Aggregation functions can solve some challenges when you need to query Azure resources with KQL, especially with numerical value. It is important to master it.