KQL Function Failure?

%3CLINGO-SUB%20id%3D%22lingo-sub-1860772%22%20slang%3D%22en-US%22%3EKQL%20Function%20Failure%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1860772%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20while%20ago%20I%20posted%20about%20how%20I%20wrote%20a%20function%20for%20extracting%20fields%20from%20Cisco%20Meraki.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20started%20sharing%20my%20work%20on%20my%20github%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2Fjkatzmandu%2Fsentinel_tricks%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2Fjkatzmandu%2Fsentinel_tricks%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20function%20I%20came%20up%20with%20works%20reasonably%20well%2C%20but%20it%20does%20not%20work%20on%20some%20event%20types%2C%20so%20I'm%20working%20to%20expand%20and%20fix%20the%20function.%20The%20problem%2C%20as%20with%20most%20logs%2C%20is%20that%20they%20are%20not%20consistent.%20With%20Meraki%20there%20are%20times%20when%20Source%20and%20destination%20are%20given%20with%20the%20following%20format%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esrc%3D10.0.0.2%20dst%3D1.2.3.4%20sport%3D35323%20dport%3D443%3C%2FP%3E%3CP%3Eand%20sometimes%3C%2FP%3E%3CP%3Esrc%3D10.0.0.2%3A35323%20dst%3D1.2.3.4%3A443%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20using%20some%20regex%20I%20have%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3Bextend%20Dst_Port%20%3D%20extract(%40'dport%3D%5C%22%3F(%5Cd%2B)%5C%22%3F'%2C%201%2C%20RawData)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%5B%20this%20works%20on%20its%20own%20%5D%3C%2FP%3E%3CP%3EORextend%20Dst_Port%20%3D%20extract(%40'dst%3D%5Cd%2B%5C.%5Cd%2B%5C.%5Cd%2B%5C.%5Cd%2B%5C%3A(%5Cd%2B)'%2C%201%2C%20RawData)%5B%20this%20works%20on%20its%20own%20%5D%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20the%20data%20is%20variable%2C%20I%20try%20to%20use%20an%20%22iff%22%20function%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDst_Port%20%3D%20iff(isnull(%20extract(%40'dport%3D%5C%22%3F(%5Cd%2B)%5C%22%3F'%2C%201%2C%20RawData)%20)%2C%20extract(%40'dst%3D%5Cd%2B%5C.%5Cd%2B%5C.%5Cd%2B%5C.%5Cd%2B%5C%3A(%5Cd%2B)'%2C%201%2C%20RawData)%2C%20extract(%40'dport%3D%5C%22%3F(%5Cd%2B)%5C%22%3F'%2C%201%2C%20RawData)%20)%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20if%20the%20following%20extract%2Fregex%20doesn't%20match%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3Bextract(%40'dport%3D%5C%22%3F(%5Cd%2B)%5C%22%3F'%2C%201%2C%20RawData)%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20%22extract%22%20function%20doesn't%20seem%20to%20be%20returning%20a%20null.%20Per%20the%20documentation%2C%20it%20should!%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fextractfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fextractfunction%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20run%20a%20few%20tests.%20If%20I%20run%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3BMeraki_CL%20%7C%20where%20RawData%20contains%20%22security_event%22%20%7C%20extend%20Dst_Port%20%3D%20iff(isnull(%20extract(%40'dport%3D%5C%22%3F(%5Cd%2B)%5C%22%3F'%2C%201%2C%20RawData)%20)%2C%20extract(%40'dst%3D%5Cd%2B%5C.%5Cd%2B%5C.%5Cd%2B%5C.%5Cd%2B%5C%3A(%5Cd%2B)'%2C%201%2C%20RawData)%2C%20extract(%40'dport%3D%5C%22%3F(%5Cd%2B)%5C%22%3F'%2C%201%2C%20RawData)%20)%2C%20Dst_Test%20%3D%20extract(%40'dst%3D%5Cd%2B%5C.%5Cd%2B%5C.%5Cd%2B%5C.%5Cd%2B%5C%3A(%5Cd%2B)'%2C%201%2C%20RawData)%2C%20Null_Test%20%3D%20extract(%40'dport%3D%5C%22%3F(%5Cd%2B)%5C%22%3F'%2C%201%2C%20RawData)%20%7C%20where%20isnull(Null_Test)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20fails.%20If%20I%20change%20the%20last%20%22where%22%20clause%20to%20read%20%22%20Null_Test%20%3D%3D%20%22%22%20%22%20it%20works!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20is%20this%20broken%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1866592%22%20slang%3D%22en-US%22%3ERE%3A%20KQL%20Function%20Failure%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1866592%22%20slang%3D%22en-US%22%3EWell%2C%20I%20changed%20my%20function%20around%2C%20so%20instead%20of%20using%20%22isnull%22%20it's%20using%20%22%22%20as%20a%20comparison.%20And%20it%20works.%3C%2FLINGO-BODY%3E
Contributor

Hi everyone!

 

A while ago I posted about how I wrote a function for extracting fields from Cisco Meraki.

 

I've started sharing my work on my github, https://github.com/jkatzmandu/sentinel_tricks

 

The function I came up with works reasonably well, but it does not work on some event types, so I'm working to expand and fix the function. The problem, as with most logs, is that they are not consistent. With Meraki there are times when Source and destination are given with the following format:

 

src=10.0.0.2 dst=1.2.3.4 sport=35323 dport=443

and sometimes

src=10.0.0.2:35323 dst=1.2.3.4:443

 

So using some regex I have:

 

 extend Dst_Port = extract(@'dport=\"?(\d+)\"?', 1, RawData) 

 [ this works on its own ]

OR

extend Dst_Port = extract(@'dst=\d+\.\d+\.\d+\.\d+\:(\d+)', 1, RawData)

[ this works on its own ] 

Because the data is variable, I try to use an "iff" function:

 

Dst_Port = iff(isnull( extract(@'dport=\"?(\d+)\"?', 1, RawData) ), extract(@'dst=\d+\.\d+\.\d+\.\d+\:(\d+)', 1, RawData), extract(@'dport=\"?(\d+)\"?', 1, RawData) ), 

  

My problem is that if the following extract/regex doesn't match:

  extract(@'dport=\"?(\d+)\"?', 1, RawData) 

the "extract" function doesn't seem to be returning a null. Per the documentation, it should!

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/extractfunction

 

I've run a few tests. If I run this:

 Meraki_CL | where RawData contains "security_event" | extend Dst_Port = iff(isnull( extract(@'dport=\"?(\d+)\"?', 1, RawData) ), extract(@'dst=\d+\.\d+\.\d+\.\d+\:(\d+)', 1, RawData), extract(@'dport=\"?(\d+)\"?', 1, RawData) ), Dst_Test = extract(@'dst=\d+\.\d+\.\d+\.\d+\:(\d+)', 1, RawData), Null_Test = extract(@'dport=\"?(\d+)\"?', 1, RawData) | where isnull(Null_Test) 

 

It fails. If I change the last "where" clause to read " Null_Test == "" " it works!

 

So is this broken?

 

Thanks!

 

 

1 Reply
Well, I changed my function around, so instead of using "isnull" it's using "" as a comparison. And it works.