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

Aashritha Reddeddy 1 Reputation point
2022-03-24T16:41:31.547+00:00

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!

Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
2,850 questions
Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
4,352 questions
Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
7,242 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. tbgangav-MSFT 10,386 Reputation points
    2022-03-28T10:19:38.063+00:00

    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  
    

    187456-image.png

    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"  
    

    187369-image.png

    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.

    2 people found this answer helpful.

  2. Andrew Blumhardt 9,576 Reputation points Microsoft Employee
    2022-03-30T13:10:52.813+00:00

    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)


  3. MayankBargali-MSFT 69,331 Reputation points
    2022-04-08T13:57:57.453+00:00

    @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.
    191319-image.png

    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.