User Profile
ChristopherKerry
Copper Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Joining with wildcarded values
I'm trying to join with wildcarded values such as the below with multiple fields. Does anyone know how to do this in KQL? let X = datatable(Name:string, Src:string, Dest:string) [ 'a','42.0.0.3','10.0.0.3', 'b','42.0.0.3','20.33.44.55' ]; let Y = datatable(Name2:string, Src:string, Dest:string) [ 'b','42.0.0.3','10.0.*', 'c','42.0.0.3','20.33.44.55' ]; X | join Y on Src, Dest Many thanksRe: Searching by more than one field when using a watch list
Ofer_Shezaf I would definitely say this is easier to read and quicker to write (especially when you have a lot of query to go through): | search NOT [| inputlookup LOOKUP | fields src dest dest_port app protocol url] than this: | where SrcIP !in ((_GetWatchlist('LOOKUP') | project SrcIP)) | where Dest !in ((_GetWatchlist('LOOKUP') | project Dest)) | where DestPort !in ((_GetWatchlist('LOOKUP') | project DestPort)) | where App !in ((_GetWatchlist('LOOKUP') | project App)) | where Protocol !in ((_GetWatchlist('LOOKUP') | project Protocol)) | where Url !in ((_GetWatchlist('LOOKUP') | project Url)) Even more so when you have 3 or 4 lookups to correlate and you can end up with 10+ lines of KQL just for a few lookups. Is there a better way to store this information and correlate it in Sentinel?Turn off validation for a column existing in a table when searching
Is there a way to turn off validation for if a column exists in table when searching? I'm using the below KQL to pull key value pairs from logs and create each key as a separate column. Syslog | extend kvpairs=parse_json(extract_all("(\\w+)=((?:[\\w-\\.:]+)|\"(?:[^\"]+)\")(?:\\s|$)", dynamic([1,2]), SyslogMessage)) | mv-apply kvpairs on (summarize make_bag(pack(tostring(replace('-', '', tostring(kvpairs[0]))), trim("\"",tostring(kvpairs[1]))))) | evaluate bag_unpack(bag_) The devices that send the Syslog over dynamically generate the key value pairs depending on if the value exists in the event so not all the events we see in Sentinel have all the same set of columns. As such, when we then come to manipulate these fields later on, not all of them exist and we get an error such as the below: Syslog | extend kvpairs=parse_json(extract_all("(\\w+)=((?:[\\w-\\.:]+)|\"(?:[^\"]+)\")(?:\\s|$)", dynamic([1,2]), SyslogMessage)) | mv-apply kvpairs on (summarize make_bag(pack(tostring(replace('-', '', tostring(kvpairs[0]))), trim("\"",tostring(kvpairs[1]))))) | evaluate bag_unpack(bag_) | project-rename BytesIn=toint(rcvdbyte), BytesOut=toint(sentbyte) How do we turn off this column reference validation?Re: Searching by more than one field when using a watch list
TeachJing Thanks for the response TeachJing. The data is normalised already - it's more just to see if there was a more efficient way of doing things instead of having 3 where statements. In splunk I would do something like this: | search NOT [| inputlookup WL_Global | fields user src dest ] Splunk would translate that into optimised code that would then run on the indexers. I was wondering if there was an equivalent in Sentinel? Or is the best way with 3 where statements loading up the watchlist each time?Searching by more than one field when using a watch list
Hi there, I'm trying to filter by multiple fields in a watchlist. Something similar to the below, but with the fields user, src and dest. | where SrcIP !in ((_GetWatchlist('WL_Global') | project user, src, dest | project-rename SrcIP=src)) I've tried the below, but this comes back with an error: | where * !in ((_GetWatchlist('WL_Global') | project user, src, dest | project-rename SrcIP=src)) Is there a way of doing this without repeating the entire line for each separate field? Many thanks
