Mar 18 2021 04:23 AM
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?
Mar 18 2021 07:22 AM
Solution@ChristopherKerry Take a look at column_ifexists()
https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/columnifexists
Mar 18 2021 08:00 AM
That works brilliantly - thanks @Gary Bushey