KQL Query sought for Source and Destination IP and TCP Port

%3CLINGO-SUB%20id%3D%22lingo-sub-3260112%22%20slang%3D%22en-US%22%3EKQL%20Query%20sought%20for%20Source%20and%20Destination%20IP%20and%20TCP%20Port%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3260112%22%20slang%3D%22en-US%22%3E%3CP%3ELog%20analytics%20is%20ON%20and%20I%20wish%20to%20run%20a%20KQL%20query%20as%20described%20in%20the%20title.%20In%20terms%20of%20time%20duration%20it%20can%20be%20for%20last%2024hours%20for%20example.%20This%20is%20for%20traffic%20going%20through%20Azure%20firewall.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20want%20to%20be%20able%20to%20specify%20a%20host%20and%20destination%20IP%20address%2C%20with%20TCP%20port%20443%20for%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20searched%2C%20but%20nothing%20this%20specific%20found%20and%20I%20don't%20come%20from%20a%20'script%20writing'%20background%20neither%2C%26nbsp%3B%20though%20I%20do%20accept%20that%20must%20change%20going%20forwards.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3260112%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Monitor%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3260385%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Query%20sought%20for%20Source%20and%20Destination%20IP%20and%20TCP%20Port%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3260385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1336098%22%20target%3D%22_blank%22%3E%40ajaznawaz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20say%2C%20many%20ways%2C%20there%20is%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EAzureDiagnostics%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1h)%0A%7C%20where%20Category%20%20%3D%3D%20'AzureFirewallNetworkRule'%0A%7C%20where%20msg_s%20has_any%20('Deny'%2C'TCP')%0A%2F%2F%20this%20was%20from%20an%20old%20exmaple%2C%20I%20think%20its%20right%20but%20I%20dont%20have%20data%20to%20test%20%0A%7C%20parse%20msg_s%20with%20Protocol%20%22%20request%20from%20%22%20SourceIP%20%22%3A%22%20SourcePort%20%22%20to%20%22%20DestinationIP%20%22%3A%22%20DestinationPort%20%22%20was%20%22%20Action%20%22%20to%20%22%20NatDestination%0A%7C%20where%20DestinationIP%20%3D%3D%20'2.2.2.2'%20and%20SourceIP%20%3D%3D'1.1.1.1'%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20an%20example%20in%20the%20Community%20Github%3A%26nbsp%3B%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2Fmicrosoft%2FAzureMonitorCommunity%2Ftree%2Fmaster%2FAzure%2520Services%2FFirewalls%2FQueries%2FFirewall%2520Logs%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzureMonitorCommunity%2FAzure%20Services%2FFirewalls%2FQueries%2FFirewall%20Logs%20at%20master%20%C2%B7%20microsoft%2FAzureMonitorCommunity%20(github.com)%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3260260%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Query%20sought%20for%20Source%20and%20Destination%20IP%20and%20TCP%20Port%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3260260%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1336098%22%20target%3D%22_blank%22%3E%40ajaznawaz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%2C%20seems%20to%20be%20working.%20I%20guess%20there%20a%20few%20different%20ways%20to%20skin%20this%20cat%20hey%20..%3C%2FP%3E%3CP%3EAny%20tips%20would%20be%20appreciated%2C%20atm%20just%20doing%20trial%20and%20error%20%3B)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAzureDiagnostics%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1h)%3CBR%20%2F%3Eand%20Category%20%3D%3D%20%22AzureFirewallNetworkRule%22%3CBR%20%2F%3Eand%20msg_s%20contains%20%22Deny%22%3CBR%20%2F%3Eand%20msg_s%20contains%20%22TCP%22%3CBR%20%2F%3Eand%20msg_s%20contains%20%22from%20192.6.2.41%22%3CBR%20%2F%3Eand%20msg_s%20contains%20%22to%20192.6.56.107%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Log analytics is ON and I wish to run a KQL query as described in the title. In terms of time duration it can be for last 24hours for example. This is for traffic going through Azure firewall.

 

I just want to be able to specify a host and destination IP address, with TCP port 443 for example.

 

I've searched, but nothing this specific found and I don't come from a 'script writing' background neither,  though I do accept that must change going forwards.

2 Replies

@ajaznawaz 

 

I tried this, seems to be working. I guess there a few different ways to skin this cat hey ..

Any tips would be appreciated, atm just doing trial and error ;)

 

AzureDiagnostics
| where TimeGenerated > ago(1h)
and Category == "AzureFirewallNetworkRule"
and msg_s contains "Deny"
and msg_s contains "TCP"
and msg_s contains "from 192.6.2.41"
and msg_s contains "to 192.6.56.107"

@ajaznawaz 

 

As you say, many ways, there is 

AzureDiagnostics
| where TimeGenerated > ago(1h)
| where Category  == 'AzureFirewallNetworkRule'
| where msg_s has_any ('Deny','TCP')
// this was from an old exmaple, I think its right but I dont have data to test 
| parse msg_s with Protocol " request from " SourceIP ":" SourcePort " to " DestinationIP ":" DestinationPort " was " Action " to " NatDestination
| where DestinationIP == '2.2.2.2' and SourceIP =='1.1.1.1'

 

There is an example in the Community Github: 
AzureMonitorCommunity/Azure Services/Firewalls/Queries/Firewall Logs at master · microsoft/AzureMoni...