Query to get the last active time of a resource(disk/ vm) within a Azure subscription

Question

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!

Answer 1

Hi @Aashritha Reddeddy ,

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.

Answer 2

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)

Answer 3

@Aashritha Reddeddy 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.