SOLVED

Find Virtual Machines Not Connected to Log Analytics Agent?

Copper Contributor

Hello! 

 

I've run into another issue in regards to Log Analytics. I'd like to query all the virtual machines inside a certain subscription and determine if they are not joined or not to log analytics. Anything not joined will then be placed into a list. After doing some reason and digging, I cannot find a way to query Azure machine names unless they are connected to log analytics. I'd believe there would be some kind of way to do this correct?

 

Here was what I was thinking in regards to the query that should work:

 

Heartbeat | where (SubscriptionId contains "SubscriptionID" or contains "SubscriptionID") | Distinct Computer | join type=leftouter (

Code Here that grabs all all virtual machines that have been made in Azure | Distinct Computer

) on $left.Computer == $right.Computer

 

Can someone provide assistance to me on this? I was here a couple of weeks ago and someone provided me with some great help.

 

 

 

4 Replies
best response confirmed by envyforme (Copper Contributor)
Solution

Nevermind! Actually found this out myself doing some more research.

 

AzureActivity | where TimeGenerated > ago(24h)

| where SubscriptionId contains "SUB ID HERE ")

| where ResourceId contains "virtualmachines"

| distinct Resource |

join kind= leftanti

(
         Heartbeat * | distinct Computer 
) on $left.Resource == $right.Computer

Great solution, thanks for sharing!

Not sure if I understand the logic of the solution or may be I do not understand the request. The Azure activity log is generated only when there is some action on the VMs. So if you have a VMs that did not have any action they will not appear in you azure activity log thus not in the query as well no matter if they are connected to Log Analytics or not. Best way to achieve this is probably using Azure Resource Graph. Resource graph uses the same query language as Log Analytics - Kusto. From there you can get all VMs and compare the results to Log Analytics data. The downside is that you cannot do this with single query you will have to extract the results from those services and do comparison in another language like PowerShell or Python. You can also extract the results from Resource Graph and feed them to the query in Log Analytics as well.

We use computer groups in the query so you should see also  computer in the output if never connected in general or in  special time frame..

1 best response

Accepted Solutions
best response confirmed by envyforme (Copper Contributor)
Solution

Nevermind! Actually found this out myself doing some more research.

 

AzureActivity | where TimeGenerated > ago(24h)

| where SubscriptionId contains "SUB ID HERE ")

| where ResourceId contains "virtualmachines"

| distinct Resource |

join kind= leftanti

(
         Heartbeat * | distinct Computer 
) on $left.Resource == $right.Computer

View solution in original post