Hello, I'm looking a way to filter out Traffic Analysis Logs with Individual IP by allow and Blocked. I have Azure VM with enabled NSG Flow Logs. Need to see which Individual IP hits my Azure VM to 443(HTTPS) with Timestamp,Location and whether it's Blocked or Allowed or not? Currently it shows me only totalblocked IP count and Total Allow IP count. I hope this make sense for you Thanks, Ronit

Hi, I believe a Kusto query like this should serve your purpose when you run it on your Log Analytics workspace AzureNetworkAnalytics_CL | where SubType_s == "FlowLog" | where FlowDirection_s == "I" | where VM2_s == "<resource group name>/<vm name>" | where DestPort_d == 443 | extend AllowedBlocked = iff(AllowedInFlows_d > 0, 'Allowed', iff(DeniedInFlows_d >0, 'Blocked', 'Unknown') ) You either have to choose time range from UI or add it within the query. You will also have to fill in the details of your VM within the query. Column Country_s will give you the location. Column L7Protocol_s will give you the protocol used. You can find the whole schema for Traffic Analysis documented here in case you need further information that is available within it. I have made it easier for you to see if a traffic flow is allowed or blocked. Please " Accept the answer" if the information helped you. This will help us and others in the community as well.

Traffic Analysis Filter Logs by Individual IP

Accepted answer

Stanislav Zhelyazkov 21,336 Reputation points MVP

2022-05-27T12:27:34.657+00:00
Hi,
I believe a Kusto query like this should serve your purpose when you run it on your Log Analytics workspace

AzureNetworkAnalytics_CL | where SubType_s == "FlowLog" | where FlowDirection_s == "I" | where VM2_s == "<resource group name>/<vm name>" | where DestPort_d == 443 | extend AllowedBlocked = iff(AllowedInFlows_d > 0, 'Allowed', iff(DeniedInFlows_d >0, 'Blocked', 'Unknown') )

You either have to choose time range from UI or add it within the query. You will also have to fill in the details of your VM within the query. Column Country_s will give you the location. Column L7Protocol_s will give you the protocol used. You can find the whole schema for Traffic Analysis documented here in case you need further information that is available within it. I have made it easier for you to see if a traffic flow is allowed or blocked.

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.
Please sign in to rate this answer.

3 people found this answer helpful.
Rahul 276 Reputation points

2022-05-27T14:36:29.893+00:00

Thanks, Stan.

I tried above but it shows me no results and try to change Time Range.

Also, I added Column Country_S it shows me wrong Tag.

Stanislav Zhelyazkov 21,336 Reputation points MVP

2022-05-27T15:43:28.353+00:00

I do not have your environment so I do not know what data you have or you are missing some data. May be some of your input is not entered correctly or you just do not have the data.
You can run queries like these to:
show destination VMs

AzureNetworkAnalytics_CL | where SubType_s == "FlowLog" | where FlowDirection_s == "I" | distinct VM2_s

show destination ports for a VM:

AzureNetworkAnalytics_CL | where SubType_s == "FlowLog" | where FlowDirection_s == "I" | where VM2_s == "<resource group name>/<vm name>" | distinct DestPort_d

if any of the filters in the Kusto query filters that data in a way that there are no results, no results will be displayed.
I do not know what you mean by "I added Column Country_S it shows me wrong Tag. "
When you execute the query from previous reply will show you all columns so there is no need to explicitly add column to be shown.

Rahul 276 Reputation points

2022-06-09T08:35:48.913+00:00

Hello Stan,

Thanks for the reply. I'm trying to filter denied IP address uisng Public IP service Provider. I cant able to filter out using single query. There are two different tables AzureNetworkIP CL and AzureNetworkAnalyticsIPdetails CL in Flow Logs. I used belwo two qurrie. Is there any way we can combine both queries?

AzureNetworkAnalytics_CL
| where FlowStatus_s == 'D' and DestPort_d ==443
| where Country_s =='gb'
| distinct TimeGenerated, SrcPublicIPs_s,PublicIPs_s,VM_s,Region_s

AzureNetworkAnalyticsIPDetails_CL
| where SubType_s == 'FlowLog' and PublicIPDetails_s =='<Client_Name>'
| distinct IP_s, PublicIPDetails_s, Location_s, FlowIntervalStartTime_t

Stanislav Zhelyazkov 21,336 Reputation points MVP

2022-06-10T13:04:38.513+00:00

depends on how you want to combine them. I would assume you would want to do some join and there is such Kusto operator. But in order to join them you will have to think on which column(s) you want to join them. Also the columns you do joins require to be in the same format so the values can have a match.

Rahul 276 Reputation points

2022-06-13T09:52:31.563+00:00

Hello Stan,

To Join both the tables, it requires a common column. Both the tables don't have any common column. Is there any other way? I only need information of 443 denied IP address using my client name as Public IP Service Provider.

Stanislav Zhelyazkov 21,336 Reputation points MVP

2022-06-14T11:25:09.44+00:00

I am still not completely sure what exactly you want to achieve as end result and how you want to match the data but I am giving a try with below query.

AzureNetworkAnalytics_CL | where TimeGenerated between (datetime(2022-06-12) .. datetime(2022-06-13)) | where FlowStatus_s == 'D' and DestPort_d ==443 | where Country_s =='gb' | extend SrcPublicIP = tostring(split(SrcPublicIPs_s, '|')[0]) | extend PublicIP = tostring(split(PublicIPs_s, '|')[0]) | distinct TimeGenerated, FlowIntervalStartTime_t, SrcPublicIP,SrcPublicIPs_s,PublicIP ,PublicIPs_s,VM_s,Region_s | join kind=leftouter ( AzureNetworkAnalyticsIPDetails_CL | where TimeGenerated between (datetime(2022-06-12) .. datetime(2022-06-13)) | where SubType_s == 'FlowLog' and PublicIPDetails_s =='<Client_Name>' | distinct IP_s, PublicIPDetails_s, Location_s, FlowIntervalStartTime_t ) on $left.SrcPublicIP == $right.IP_s, FlowIntervalStartTime_t

Stanislav Zhelyazkov 21,336 Reputation points MVP

2022-06-14T11:29:06.993+00:00

The short explanation for the query is. I am normalizing the data for columns SrcPublicIPs_s and PublicIPs_s. In a way they contain only the IP as value. They are the same btw but you should use SrcPublicIPs_s as the other will be deprecated. I am doing join on both tables. The join is leftouter - Returns all the records from the left side and only matching records from the right side. Left records are AzureNetworkAnalytics_CL and right are AzureNetworkAnalyticsIPDetails_CL. If you need other kind of join check Kusto query documentation for join. The join is done based on IP and flowIntervalStartTime_T from both tables. Keep in mind that by doing distinct some of your data is filtered but it is up to you if you want to use it or not. Keep in mind that if you want to do join the TimeGenerated filter should be the same for both otherwise end results might deviate a lot. For example if one table is only for the last hour but the other is for the last 24 hours results will not be very accurate. Scope of time can be controlled from Logs UI as well but than you should remove the filters from the query on TimeGenerated.

Rahul 276 Reputation points

2022-06-15T14:30:00.613+00:00

Hi Stan, Appreciate for your Help. The main aim of this Join query is see the denied IP address 443 which Hits the Azure VM for my Client name as Public IP service provider. I tried to run your query. It shows me the same results of Src IP's for all public IP service providers. I would be more appreciate if it show denied Src IP's as per my Public IP service provider.Currently it shows same IP's for all Public IP service Providers. I have already added my client name in the query.

AzureNetworkAnalytics_CL
| where TimeGenerated between (datetime(2022-06-14) .. datetime(2022-06-15))
| where FlowStatus_s == 'D' and DestPort_d ==443
| where Country_s =='gb'
| extend SrcPublicIP = tostring(split(SrcPublicIPs_s, '|')[0])
| extend PublicIP = tostring(split(PublicIPs_s, '|')[0])
| distinct TimeGenerated, FlowIntervalStartTime_t, SrcPublicIP,SrcPublicIPs_s,PublicIP ,PublicIPs_s,VM_s,Region_s
| join kind=leftouter (
AzureNetworkAnalyticsIPDetails_CL
| where TimeGenerated between (datetime(2022-06-14) .. datetime(2022-06-15))
| where SubType_s == 'FlowLog' and PublicIPDetails_s == 'national farmers union mutual insurance society ltd'
| distinct IP_s, PublicIPDetails_s, Location_s, FlowIntervalStartTime_t
) on $left.SrcPublicIP == $right.IP_s, FlowIntervalStartTime_t

Stanislav Zhelyazkov 21,336 Reputation points MVP

2022-06-20T11:53:10.047+00:00

ok, if it shows more results than you expect filter those results at the end with where option to whatever fits your purpose.
Sign in to comment

Traffic Analysis Filter Logs by Individual IP

0 additional answers