Find unused resources through Azure Logic Apps

You have been using the Cloud for a few years now and the cost have been increasing every years.

The reason is that resources are multiplying like pests and you cannot stop them.

Developers have full privileges and this makes things worse: they can create VMs, Blob Storage and SQL Databases like there were no tomorrow.

Where you should start cleaning he unicorn poop?

From the low hanging fruits…

Copies of resources

If you work closely with developers you will notice that they are use to create copies of resources.

If there is a system down on one of your customers the very first thing that they do is to:

— Rename the database to database_old or database_09–03–2023 or database_backup or database_to_be_deleted

— Restore a copy of the old database

then they start working to put the customer back online and once the work is done they go to drink beers.

You now have 2 databases for that customer. No one will take the responsibility to delete the old database “in case we need it…” so let’s keep it for one week more. A few months pass and the database is still there.

The same goes for snapshots, disks, storage, images, etc…

Let’s investigate

Open Azure Resource Graph Explorer and use this Kusto query to find all resources with 2022 or 2023 or old in their name:

// Find All resources with ‘2022|2023|old’

Resources

| join kind=inner (

resourcecontainers

| where type == ‘microsoft.resources/subscriptions’

| project subscriptionId, subscriptionName = name)

on subscriptionId

| where isnotempty(extract(‘2022|2023|old’, 0, name))

| where type != ‘microsoft.resourcegraph/queries’

| project subscriptionName,resourceName = subscriptionId, resourceGroup, name,type,properties.requestedServiceObjectiveName,

sku.name,sku.tier,sku.capacity,sku.family,location, tags.createdByEmail, tags.customer

| order by asc

| order by asc

You now have a list of all resources that should be deleted.

You can contact each department and ask them to remove the old resources but a few weeks form now and you will be in the same situation.

What you really want to do is to…

Cut the bad habits

And this is what we are going to do: we want to create a Logic App that

— trigs that Kusto query

— parse the JSON

— eventually take the e-mail of the person who created that resource (if that e-mail is in a tag)

— put the results in a CSV

— send it by e-mail to the creator of that resource

Here are the steps:

1) set a recurrence

This is the easy one, you have to setup a trigger that makes this logic app to be trig every week:

2) Choose a REST API to invoke

Put the Kusto query in the HTTP 2 request as a POST and point it against the endpoint https://management.azure.com/providers/Microsoft.ResourceGraph/resources

3) Initialize a variable to grab the e-mail from the tag

This is optional but if your company is doing things right, most of the time, there is a tag dedicated to the owner or the creator of the resource. We are going to create a variable that we will fill up later on when we will parse the JSON

4) Specify the schema of the JSON content

I found this part very counterintuitive.

In this step you should setup:

— Content: the Body of the HTTPS 2 and in the

— Schema: a sample of the output of the HTTP 2 request

5) Create an Array for the e-mail tag

This again is not mandatory but if you want you can put the e-mail into an array so you can later send this e-mail to the owner of that resource:

6) For each tags remove NULLs

If some of the developers has not put their e-mail in the tag you have to scrap the NULLS through this logic:

7) Compose a function that unions all e-mail separated by a coma

You now have to create a function with the content:

union(variables(‘tags_createdBy’),variables(‘tags_createdBy’))

It will look like this:

8) Set a variable to remove double quotes

It took me a while to understand that I had to remove the double quotes from e-mails. You can do that through this function:

replace(replace(replace(replace(string(outputs(‘Compose’)),’,’,’;’),’’,’’),’”’,’’)

9) We store the JSON into an Array

We create an array variable to store the JSON:

10) We create a CSV

There is a step provided by Log App that creates a CSV automatically. We are going to use that to ingest the previous Array:

11) Vanity step1: we create a string in order to insert an image in the e-mail

I know, you have something better to do…

…but c’mon, you are probably reading this blog while you are at your desk at work.

12) Vanity step2: paste the HTML code image

We are going to use this HTML code for the image, feel free to use another image:

13) Send the e-mail

Is now up to you if you want to use static e-mail addresses (here I’m using static e-mail addresses).

Otherwise paste the e-mail array in the To field:

The final result

You have now created the Azure Log App that will be sent weekly with a list of resources in the CSV in attachment.

This is the result: