Azure Monitor Workbooks: How to find Virtual Machines that are in, and not in Azure!

Sorry I’ve been away for while, however I’m back with a few articles on Azure Monitor Workbooks.  Thanks to Alp Babayigit for the idea and use case for this Workbook.

I first started with Workbooks when Azure Sentinel was launched and published some articles here:

https://techcommunity.microsoft.com/t5/azure-sentinel/azure-sentinel-and-azure-arc/ba-p/999379

https://techcommunity.microsoft.com/t5/azure-sentinel/how-to-use-azure-sentinel-to-follow-a-users-travel-and-map-their/ba-p/981716

https://techcommunity.microsoft.com/t5/azure-sentinel/how-to-use-azure-monitor-workbooks-to-map-sentinel-data/ba-p/971818

 

Summary

In this new Workbook, I describe a use case where Alp wanted to see which computers were created in Azure but had not yet sent any logs to Log Analytics.  Why, maybe its a new computer that has never been started, or it could be a machine that’s been off for a very long time.  The Azure api knows a lot about the configuration of Resources so is a great source to query (and also uses KQL).

Please look for and open the file (RAW mode is best) at this link: FindComputersMissingInLogs.workbook you just need to COPY all the file content, but first, how do you install a Workbook I’ll repeat the installation here:

Installation 

1. [Copy] the workbook file content (these are JSON files), open Azure Monitor Workbooks (from portal.azure.com) – open the “empty” Azure Monitor Workbook, in “advanced edit” mode (press the </> icon for advanced edit ). Please [paste] over any json that exists.
2. Then Press [apply] then [Done Editing]

 

Now you should have the workbook.  It should look like this:

 

 

Section 1 (large red #1),

Is the familiar SUBSCRIPTION, WORKSPACES and TimeRange drop-down list you see in many workbooks – you should see your own Azure data here.  These parameters are created with Azure Resource Graph (ARG) and enumerate the subscriptions and workspaces YOU can see and access.  I have also added a Tab area, as this workbook covers Virtual Machines and Network.  Select the Tab you wish to use.  I’ll mainly document the Virtual Machine tab.  The Subscription and Workspaces drop-downs – also support Azure lighthouse.

 

Azure Resource Graph is a service in Azure that is designed to extend Azure Resource Management by providing efficient and performant resource exploration with the ability to query at scale across a given set of subscriptions so that you can effectively govern your environment. 

 

Section 2, Is a query using ARG to filter on Computers in the Azure api that equal microsoft.compute/virtualmachines

 

Section 3, is a similar query to [section 2] but this time its a Log Analytics query (so you do need an active workspace).  You maybe able to see from the Pie charts the two sections seem to return a similar count of computers (in my example).  But is that right?

 

There is a final Workbook feature and table that I show, and that’s a Merged table. Essentially it allows you to mix the output from ARG and Log Analytics in one table.  This just shows a list of computers that are in ARG but not in Log Analytics.

 

Now lets look at the Query for Section 2.  It simply asks ARG for the name, type, location and resource Groups for all machines that ARG can see.  The Name field is important.

 

In Log Analytics we check the Heartbeat table (note: data will only be here if a computer has started, and the agent has communicated).  I remove the FQDN and just keep the simple name (anything before the first “.”).  I also check to see if its is an Azure Virtual Machine and only show those, essentially removing any Hybrid VMs from the list.  The computer field is important.

 

The final part is a Merged table.

 

This takes the output of the previous 2 queries and maps the columns NAME to COMPUTER columns to each other.  I use a merge type of left anti join (more on JOINs here).  In simple terms, what its doing is a “Left anti join returns all records from the left side that do not match any record from the right side.”  So it will list all those records from ARG (left table) that Log Analytics (right table) doesn’t know about.
Note:  the TimeRange drop-down parameter is important here, Log Analytics uses this – so if you have selected ‘last 7 days’ and the data was seen 14days ago, you’ll get a false result.

 

 

FYI, the Network section is very similar in construction.

 

So that’s this Workbook explained, let me know if its useful to you please.