Hi,
I have been trying to get the last active time of a disk/VM using Kusto Query Language on Azure portal. Is it possible to track it? I want this information to alert the user if that resource is not used for more than 14 days.
Also, could anyone help me finding out if there is any other way to query the list of unused resources (say for N days) within a subscription?
Thanks!
AFAIK currently there is no direct way to fetch the unused resources for last N days. If interested, you may share it here as feedback. In general, Azure product or feature team would check feasibility of a feature request, prioritize against existing feature backlog, add in roadmap as appropriate and would announce and/or update the related Azure document once a feature request is addressed.
As a workaround, with the features that are currently available, you may leverage Activity logs and Resource Graph Explorer to accomplish this requirement i.e., configure to get the activity logs in Log Analytics workspace and then query the AzureActivity kusto table as shown below.
AzureActivity
| where TimeGenerated >= ago(14d)
| where _ResourceId contains "Microsoft.Compute/disks" or _ResourceId contains "Microsoft.Compute/virtualMachines"
| distinct _ResourceId
Then query the Resource Graph Explorer with the help of resources table as shown below.
resources
| where type == "microsoft.compute/virtualmachines" or type == "microsoft.compute/disks"
Compare the output of AzureActivity table with that of resources table and the difference can be considered as unused resources for more than 14 days.
Hi @tbgangav-MSFT,
Thank you for the answer. Yes, you are right about the 2 queries there. But having the queries in two different locations wouldn't address the entire problem. I have one question here. Is it possible to query the Activity logs and Resource Graph Explorer in one script say in Azure functions? If so, how could I achieve that?
Also, thank you for the link. I'll give the feedback. Meanwhile, I hope to find the workaround.
Thanks!
Querying both Log Analytics' AzureActivity table and Resource Graph's resources table can be done via
So, it should be feasible to automate it. Let me check more on it and get back to you as early as possible.
@AashrithaReddeddy-4886 Adding to @tbgangav-MSFT comment. In case if you want to use the graph API to automate this using logic app then you can refer to this document.
In case of function you can create function with any of the support triggers and use REST call with the Graph API endpoint in any of the supported languages.
I personally would look for any related activity and use an "arg max" query to get the last (most recent) record. Disks don't appear to have resource-specific diagnostics (log analytics logs). I do see some Azure Monitor metrics. You have some related tables like AzureActivity, Heartbeat, VMComputer, etc. Any log that reports insights or metrics can be used.
Heartbeat
| summarize arg_max(TimeGenerated, *) by Computer
| where TimeGenerated < ago(10d)
VMComputer
| summarize arg_max(TimeGenerated, *) by Computer
| where TimeGenerated < ago(10d)
Thank you @AndrewBlumhardt-1137. This is useful. The Heartbeat table lists out the last heartbeat of each computer created 10 days ago. But yeah it misses out the attached disks. I'm trying to generate a query or use Azure functions that would alert the users if the resources(VMs, disks) in their subscription are unused for say N days. Could you help me achieving it? Is it possible to have something like that?
@AashrithaReddeddy-4886 I am colleague of @tbgangav-MSFT and had a discussion on your scenario. As per your scenario, as you want to send the email to the user (owner of the VM) if their VM is unused then I will suggest you leverage the logic app workflow.
Workflow:
Timer Trigger (that will execute your workflow based on the configuration let's say every day at particular hr.) --> run query and list results action (Azure Monitor Logs) --> Loop in through the result set of previous action --> HTTP Native Connector to call the REST API to get the user email ID who has created that VM using the resource ID (as Heartbeat table doesn't return the email ID) --> Send an EMail action
You can modify the above workflow as per your requirement and if there is way to get the email ID then you can modify the query that will give you the Heartbeat table data along with the email Id of the user who has created the resource then you can skip the HTTT Native connector action.
For run query and list results action you can leverage the azure logic app adddays function to get the data for last n days as below.
Please review different hyperlinks in the workflow which will give more details on the action/connectors. Feel free to get back to me if you need any assistance.
Thank you @MayankBargali-MSFT. I'll definitely try this out and will let you know. I apologize for the delay in response.
Thanks again!
47 people are following this question.
