Kusto regex for extracting IP adresses

%3CLINGO-SUB%20id%3D%22lingo-sub-935875%22%20slang%3D%22en-US%22%3EKusto%20regex%20for%20extracting%20IP%20adresses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-935875%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20my%20AzureDiagnostics%20for%20my%20ResourceType%20%22AzureFirewalls%22%2C%20there's%20a%20column%20named%20%22msg_s%22.%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20contains%20information%20about%20IP-adresses%20trying%20to%20request%20access%20to%20another%20adress.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EExamples%20include%3A%3C%2FU%3E%3C%2FP%3E%3CDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EHTTPS%20request%20from%20%3CSTRONG%3E10%3C%2FSTRONG%3E%3CSTRONG%3E.192.168.10%3A10100%3C%2FSTRONG%3E%20to%20s%3CSTRONG%3Eome-text.blob.core.windows.net%3A443%3C%2FSTRONG%3E.%20Action%3A%20%3CSTRONG%3EAllow%3C%2FSTRONG%3E.%20Azure%20internal%20traffic.%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EHTTPS%20request%20from%201%3CSTRONG%3E98.192.100.10%3A10500%3C%2FSTRONG%3E.%20Action%3A%20%3CSTRONG%3EDeny%3C%2FSTRONG%3E.%20Reason%3A%20SNI%20TLS%20extension%20was%20missing%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3EUDP%20request%20from%20%3CSTRONG%3E10.192.100.1%3A10500%3C%2FSTRONG%3E%20to%20%3CSTRONG%3E10.168.10.20%3C%2FSTRONG%3E.%20Action%3A%20%3CSTRONG%3EAllow%3C%2FSTRONG%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20use%20RegEx%20to%20extract%20the%20first%20IP%20into%20one%20column%2C%20then%20extract%20the%20second%20IP%20if%20there%20is%20one%20(second%20example%20did%20not%20have%20a%20destination%20IP)%2C%20and%20extract%20%22Allow%22%20or%20%22Deny%22%20into%20a%20third%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20solve%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20already%20tried%20using%20Parse%20instead%20of%20RegEx%20but%20I%20believe%20RegEx%20is%20better%20because%20of%20the%20optional%20destination%20adress%20in%20the%20second%20example%2C%20and%20optional%20%3Aport%20in%20the%20third%20example.%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-935875%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-946636%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20regex%20for%20extracting%20IP%20adresses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-946636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F431721%22%20target%3D%22_blank%22%3E%40Preben902%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20can%20get%20the%20first%20one%2C%20but%20will%20have%20to%20have%20a%20think%20about%20the%20other%20cases%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EAzureDiagnostics%0A%7C%20where%20ResourceType%20%3D%3D%20%22AZUREFIREWALLS%22%20%0A%7C%20where%20msg_s%20has%20%22request%20from%22%0A%7C%20extend%20IPaddr%20%3D%20extract(%22((%5B0-9%5D%7B1%2C3%7D)%5C%5C.(%5B0-9%5D%7B1%2C3%7D)%5C%5C.(%5B0-9%5D%7B1%2C3%7D)%5C%5C.((%5B0-9%5D%7B1%2C3%7D)))%22%2C1%2Cmsg_s)%20%0A%7C%20extend%20action%20%3D%20iif(msg_s%20has%20%22%3A%20Deny%22%2C%20%22Deny%22%2C%20%22Allow%22)%20%0A%7C%20project%20IPaddr%20%2C%20msg_s%20%2C%20action%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIn%20the%20meantime%20the%20above%20may%20help%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-946644%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20regex%20for%20extracting%20IP%20adresses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-946644%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F431721%22%20target%3D%22_blank%22%3E%40Preben902%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EI%20also%20forgot%20there%20are%20some%20examples%20on%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Ffirewall%2Flog-analytics-samples%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Ffirewall%2Flog-analytics-samples%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EAzureDiagnostics%0A%7C%20where%20Category%20%3D%3D%20%22AzureFirewallNetworkRule%22%0A%7C%20parse%20msg_s%20with%20Protocol%20%22%20request%20from%20%22%20SourceIP%20%22%3A%22%20SourcePortInt%3Aint%20%22%20to%20%22%20TargetIP%20%22%3A%22%20TargetPortInt%3Aint%20*%0A%7C%20parse%20msg_s%20with%20*%20%22.%20Action%3A%20%22%20Action1a%0A%7C%20parse%20msg_s%20with%20*%20%22%20was%20%22%20Action1b%20%22%20to%20%22%20NatDestination%0A%7C%20parse%20msg_s%20with%20Protocol2%20%22%20request%20from%20%22%20SourceIP2%20%22%20to%20%22%20TargetIP2%20%22.%20Action%3A%20%22%20Action2%0A%7C%20extend%20SourcePort%20%3D%20tostring(SourcePortInt)%2CTargetPort%20%3D%20tostring(TargetPortInt)%0A%7C%20extend%20Action%20%3D%20case(Action1a%20%3D%3D%20%22%22%2C%20case(Action1b%20%3D%3D%20%22%22%2CAction2%2CAction1b)%2C%20Action1a)%2CProtocol%20%3D%20case(Protocol%20%3D%3D%20%22%22%2C%20Protocol2%2C%20Protocol)%2CSourceIP%20%3D%20case(SourceIP%20%3D%3D%20%22%22%2C%20SourceIP2%2C%20SourceIP)%2CTargetIP%20%3D%20case(TargetIP%20%3D%3D%20%22%22%2C%20TargetIP2%2C%20TargetIP)%2CSourcePort%20%3D%20case(SourcePort%20%3D%3D%20%22%22%2C%20%22N%2FA%22%2C%20SourcePort)%2CTargetPort%20%3D%20case(TargetPort%20%3D%3D%20%22%22%2C%20%22N%2FA%22%2C%20TargetPort)%2CNatDestination%20%3D%20case(NatDestination%20%3D%3D%20%22%22%2C%20%22N%2FA%22%2C%20NatDestination)%0A%7C%20project%20TimeGenerated%2C%20msg_s%2C%20Protocol%2C%20SourceIP%2CSourcePort%2CTargetIP%2CTargetPort%2CAction%2C%20NatDestination%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EResults%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3ETimeGenerated%3C%2FTH%3E%0A%3CTH%3Emsg_s%3C%2FTH%3E%0A%3CTH%3EProtocol%3C%2FTH%3E%0A%3CTH%3ESourceIP%3C%2FTH%3E%0A%3CTH%3ESourcePort%3C%2FTH%3E%0A%3CTH%3ETargetIP%3C%2FTH%3E%0A%3CTH%3ETargetPort%3C%2FTH%3E%0A%3CTH%3EAction%3C%2FTH%3E%0A%3CTH%3ENatDestination%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T15%3A20%3A27.718Z%3C%2FTD%3E%0A%3CTD%3ETCP%20request%20from%2010.249.96.136%3A49925%20to%2013.94.141.226%3A12000.%20Action%3A%20Deny%3C%2FTD%3E%0A%3CTD%3ETCP%3C%2FTD%3E%0A%3CTD%3E10.249.96.136%3C%2FTD%3E%0A%3CTD%3E49925%3C%2FTD%3E%0A%3CTD%3E13.94.141.226%3C%2FTD%3E%0A%3CTD%3E12000%3C%2FTD%3E%0A%3CTD%3EDeny%3C%2FTD%3E%0A%3CTD%3EN%2FA%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T15%3A20%3A27.765Z%3C%2FTD%3E%0A%3CTD%3ETCP%20request%20from%2010.249.96.136%3A49925%20to%2013.94.141.226%3A12000.%20Action%3A%20Deny%3C%2FTD%3E%0A%3CTD%3ETCP%3C%2FTD%3E%0A%3CTD%3E10.249.96.136%3C%2FTD%3E%0A%3CTD%3E49925%3C%2FTD%3E%0A%3CTD%3E13.94.141.226%3C%2FTD%3E%0A%3CTD%3E12000%3C%2FTD%3E%0A%3CTD%3EDeny%3C%2FTD%3E%0A%3CTD%3EN%2FA%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T15%3A20%3A27.843Z%3C%2FTD%3E%0A%3CTD%3ETCP%20request%20from%2010.249.96.136%3A49925%20to%2013.94.141.226%3A12000.%20Action%3A%20Deny%3C%2FTD%3E%0A%3CTD%3ETCP%3C%2FTD%3E%0A%3CTD%3E10.249.96.136%3C%2FTD%3E%0A%3CTD%3E49925%3C%2FTD%3E%0A%3CTD%3E13.94.141.226%3C%2FTD%3E%0A%3CTD%3E12000%3C%2FTD%3E%0A%3CTD%3EDeny%3C%2FTD%3E%0A%3CTD%3EN%2FA%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-946764%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20regex%20for%20extracting%20IP%20adresses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-946764%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F431721%22%20target%3D%22_blank%22%3E%40Preben902%3C%2FA%3E%26nbsp%3BHere's%20a%20basic%20pattern.%20It%20doesn't%20check%20for%20valid%20IP%20addresses%2C%20for%20this%20use%20case%20that%20shouldn't%20matter.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Eprint%20extract_all(%22request%20from%20(%3FP%3CFROM%3E.%2B%3F)(%3F%3A%20to%20(%3FP%3CTO%3E.%2B))%3F%5C%5C.%20Action%3A%20(%3FP%3CACTION%3E%5B%5E.%5D%2B)%22%2C%22HTTPS%20request%20from%20198.192.100.10%3A10500.%20Action%3A%20Deny.%20Reason%3A%20SNI%20TLS%20extension%20was%20missing%22)%3C%2FACTION%3E%3C%2FTO%3E%3C%2FFROM%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

In my AzureDiagnostics for my ResourceType "AzureFirewalls", there's a column named "msg_s". 

It contains information about IP-adresses trying to request access to another adress.

 

Examples include:

 
HTTPS request from 10.192.168.10:10100 to some-text.blob.core.windows.net:443. Action: Allow. Azure internal traffic.
 

HTTPS request from 198.192.100.10:10500. Action: Deny. Reason: SNI TLS extension was missing

 

UDP request from 10.192.100.1:10500 to 10.168.10.20. Action: Allow

 

I'd like to use RegEx to extract the first IP into one column, then extract the second IP if there is one (second example did not have a destination IP), and extract "Allow" or "Deny" into a third column. 

 

Can someone help me solve this?

 

I've already tried using Parse instead of RegEx but I believe RegEx is better because of the optional destination adress in the second example, and optional :port in the third example. 

3 Replies
Highlighted

@Preben902 

 

I can get the first one, but will have to have a think about the other cases 

AzureDiagnostics
| where ResourceType == "AZUREFIREWALLS" 
| where msg_s has "request from"
| extend IPaddr = extract("(([0-9]{1,3})\\.([0-9]{1,3})\\.([0-9]{1,3})\\.(([0-9]{1,3})))",1,msg_s) 
| extend action = iif(msg_s has ": Deny", "Deny", "Allow") 
| project IPaddr , msg_s , action 

In the meantime the above may help 

Highlighted

@Preben902 

 

I also forgot there are some examples on https://docs.microsoft.com/en-us/azure/firewall/log-analytics-samples

AzureDiagnostics
| where Category == "AzureFirewallNetworkRule"
| parse msg_s with Protocol " request from " SourceIP ":" SourcePortInt:int " to " TargetIP ":" TargetPortInt:int *
| parse msg_s with * ". Action: " Action1a
| parse msg_s with * " was " Action1b " to " NatDestination
| parse msg_s with Protocol2 " request from " SourceIP2 " to " TargetIP2 ". Action: " Action2
| extend SourcePort = tostring(SourcePortInt),TargetPort = tostring(TargetPortInt)
| extend Action = case(Action1a == "", case(Action1b == "",Action2,Action1b), Action1a),Protocol = case(Protocol == "", Protocol2, Protocol),SourceIP = case(SourceIP == "", SourceIP2, SourceIP),TargetIP = case(TargetIP == "", TargetIP2, TargetIP),SourcePort = case(SourcePort == "", "N/A", SourcePort),TargetPort = case(TargetPort == "", "N/A", TargetPort),NatDestination = case(NatDestination == "", "N/A", NatDestination)
| project TimeGenerated, msg_s, Protocol, SourceIP,SourcePort,TargetIP,TargetPort,Action, NatDestination

 

Results

TimeGenerated msg_s Protocol SourceIP SourcePort TargetIP TargetPort Action NatDestination
2019-09-12T15:20:27.718Z TCP request from 10.249.96.136:49925 to 13.94.141.226:12000. Action: Deny TCP 10.249.96.136 49925 13.94.141.226 12000 Deny N/A
2019-09-12T15:20:27.765Z TCP request from 10.249.96.136:49925 to 13.94.141.226:12000. Action: Deny TCP 10.249.96.136 49925 13.94.141.226 12000 Deny N/A
2019-09-12T15:20:27.843Z TCP request from 10.249.96.136:49925 to 13.94.141.226:12000. Action: Deny TCP 10.249.96.136 49925 13.94.141.226 12000 Deny N/A
Highlighted

@Preben902 Here's a basic pattern. It doesn't check for valid IP addresses, for this use case that shouldn't matter.

 

print extract_all("request from (?P<from>.+?)(?: to (?P<to>.+))?\\. Action: (?P<action>[^.]+)","HTTPS request from 198.192.100.10:10500. Action: Deny. Reason: SNI TLS extension was missing")