Set up alerts on resource log events from VPN Gateway

%3CLINGO-SUB%20id%3D%22lingo-sub-1450262%22%20slang%3D%22en-US%22%3ESet%20up%20alerts%20on%20resource%20log%20events%20from%20VPN%20Gateway%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1450262%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20setting%20up%20monitoring%20for%20vpn%20status%20and%20getting%20below%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EAzureDiagnostics%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Category%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22TunnelDiagnosticLog%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20_ResourceId%20%3D%3D%20tolower(%3C%2FSPAN%3E%3CSPAN%3E%22%2Fsubscriptions%2Fxxxxx%2FresourceGroups%2Fdevresgrp%2Fproviders%2FMicrosoft.Network%2FvirtualNetworkGateways%2Fxxx%22%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20ago(%3C%2FSPAN%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3Em)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20remoteIP_s%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22x.x.x.x%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20status_s%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Disconnected%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%2C%20OperationName%2C%20instance_s%2C%20Resource%2C%20ResourceGroup%2C%20_ResourceId%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esort%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20asc%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E'where'%20operator%3A%20Failed%20to%20resolve%20column%20or%20scalar%20expression%20named%20'remoteIP_s'%20If%20issue%20persists%2C%20please%20open%20a%20support%20ticket.%20Request%20id%3A%2014aba92f-2ba5-4c1a-b622-e9914911d158%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1450262%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I am trying to setting up monitoring for vpn status and getting below error.

 

AzureDiagnostics
| where Category == "TunnelDiagnosticLog"
| where _ResourceId == tolower("/subscriptions/xxxxx/resourceGroups/devresgrp/providers/Microsoft.Network/virtualNetworkGateways/xxx")
| where TimeGenerated > ago(5m)
| where remoteIP_s == "x.x.x.x"
| where status_s == "Disconnected"
| project TimeGenerated, OperationName, instance_s, Resource, ResourceGroup, _ResourceId
| sort by TimeGenerated asc
 
'where' operator: Failed to resolve column or scalar expression named 'remoteIP_s' If issue persists, please open a support ticket. Request id: 14aba92f-2ba5-4c1a-b622-e9914911d158
2 Replies

@atanuazure 

 

It looks like that COLUMN doesn't exist - hence the error.  You can use EXTEND and Column_ifexists to create a default value maybe?   In this case, I use "1.1.1.1" as the default - amend to suit 

 

AzureDiagnostics
//| where Category == "TunnelDiagnosticLog"
//| where _ResourceId == tolower("/subscriptions/xxxxx/resourceGroups/devresgrp/providers/Microsoft.Network/virtualNetworkGateways/xxx")
//| where TimeGenerated > ago(5d)
| limit 5
| extend remoteIP_s = iif(column_ifexists("remoteIP_s","") == true,"","1.1.1.1")
| project remoteIP_s