Fetch Azure VMs shutdown duration on a subscription level

Question

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?

Answer 1

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