Azure Graph Explorer, intro to aggregation functions
Tags: azure, kql, cloud, beginners
Published: 2023-09-19 17:20:31

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. 

```sql
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. 

```sql
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.

```sql
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](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/aggregation-functions)

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. 

```sql
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()**  

```sql
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))**

```sql
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.

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

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

```sql
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. 

```sql
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.

```sql
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.