Forum Discussion

jalilmed's avatar
jalilmed
MCT
Dec 28, 2022

How to Get Last Reboot VM using Azure Graph Query

Hello,

I want to create an Azure Dashboard using Graph Query with date of the last reboot or power-Off.

Thanks for help

Query Language

4 Replies

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Dec 29, 2022

    jalilmed This may help get you started (it will need work).  The challenge with the graph is getting the time of the last action 

     

    resources
| where type has 'microsoft.compute/virtualmachines/extensions' or  type has 'microsoft.hybridcompute/machines/extensions'
| where name has 'MicrosoftMonitoringAgent' or name has 'OmsAgentForLinux' or name has 'AzureMonitorWindowsAgent' or name has 'AzureMonitorLinuxAgent'
| extend AzureVM = extract('virtualMachines/(.*)/extensions',1,id), ArcVM = extract('machines/(.*)/extensions',1,id)
| summarize count() by name=tolower(AzureVM), ArcVM=tolower(ArcVM), subscriptionId, resourceGroup, AgentType=name
| extend hasBoth = iff(count_ > 1, 'Yes', 'No') 
| join 
 ( 
    resources
    | where type =~ 'Microsoft.Compute/virtualMachines'
    | project name, properties.extended.instanceView.powerState.displayStatus,
                    properties.extended.instanceView.powerState.code,
                    created_ = properties.timeCreated
    | order by name desc
 ) on name

     

      • Clive_Watson's avatar
        Clive_Watson
        Bronze Contributor
        Jan 13, 2023

        Indeed, that's why I said "the challenge with the graph is getting the time of the last action" - the majority of resources data doesn't have much other than created. If you only see created then that's all that is available.

        This is much the same in Azure Monitor which will only have logs when the agent runs.

        Now in a Workbook we can MERGE the two datasets, to get the answer.

        This is an example Workbook I did a while ago that shows that (it merges the results from ARG with a Logs Query in KQL)

        https://raw.githubusercontent.com/clivewatson/KQLpublic/master/KQL/Workbooks/findMissingComputers/findComputersMissingInLogs%20v0.2.workbook

        So I now see in my Use Case - I'm showing All machines that ARG knows have been created but have never been started to log any data in the past 90days.   

         

Resources