Fetch Azure VMs shutdown duration on a subscription level

Senthilnath TM 241 Reputation points
2024-04-02T10:33:42.9666667+00:00

I am trying to fetch a report from Azure subscription which has several (>100) VMs deployed. The report must fetch the list of VMs which are shutdown past 45 days or more.

The below query provides the Start & Deallocate time of the VM, but unable to match the resource name & find the difference between start and deallocated time.

Query : AzureActivity | where OperationName in ("Deallocate Virtual Machine","Start Virtual Machine") and ActivityStatus == "Succeeded" | summarize max(TimeGenerated) by Resource, OperationName

Can this be achieved using KQL/Resource graph query? If not which is the better option to fetch them?

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,785 questions
Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
7,070 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Clive Watson 5,711 Reputation points MVP
    2024-04-02T17:42:00.6166667+00:00

    How about this example, using arg and logs?

    arg('').resources
    | where type == "microsoft.compute/virtualmachines"
    | extend
        machineStatus = properties.extended.instanceView.powerState.displayStatus,
        OSType = properties.storageProfile.osDisk.osType
    | project
        name,
        VM=id,
        location,
        ResourceGroup=resourceGroup,
        machineStatus,
        OSType,
        properties
    | join 
        (
        AzureActivity
        | where OperationName == "Deallocate Virtual Machine" and ActivityStatus == "Succeeded"
        | summarize max(TimeGenerated) by Resource, OperationName
        | extend lastSeen = datetime_diff('day', now(), max_TimeGenerated)
        )
        on $left.name == $right.Resource
    | where lastSeen > 45
    
    0 comments No comments