SOLVED

Turn off validation for a column existing in a table when searching

%3CLINGO-SUB%20id%3D%22lingo-sub-2219690%22%20slang%3D%22en-US%22%3ETurn%20off%20validation%20for%20a%20column%20existing%20in%20a%20table%20when%20searching%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2219690%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20turn%20off%20validation%20for%20if%20a%20column%20exists%20in%20table%20when%20searching%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20the%20below%20KQL%20to%20pull%20key%20value%20pairs%20from%20logs%20and%20create%20each%20key%20as%20a%20separate%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESyslog%0A%7C%20extend%20kvpairs%3Dparse_json(extract_all(%22(%5C%5Cw%2B)%3D((%3F%3A%5B%5C%5Cw-%5C%5C.%3A%5D%2B)%7C%5C%22(%3F%3A%5B%5E%5C%22%5D%2B)%5C%22)(%3F%3A%5C%5Cs%7C%24)%22%2C%20dynamic(%5B1%2C2%5D)%2C%20SyslogMessage))%0A%7C%20mv-apply%20kvpairs%20on%20(summarize%20make_bag(pack(tostring(replace('-'%2C%20''%2C%20tostring(kvpairs%5B0%5D)))%2C%20trim(%22%5C%22%22%2Ctostring(kvpairs%5B1%5D)))))%0A%7C%20evaluate%20bag_unpack(bag_)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20devices%20that%20send%20the%20Syslog%20over%20dynamically%20generate%20the%20key%20value%20pairs%20depending%20on%20if%20the%20value%20exists%20in%20the%20event%20so%20not%20all%20the%20events%20we%20see%20in%20Sentinel%20have%20all%20the%20same%20set%20of%20columns.%20As%20such%2C%20when%20we%20then%20come%20to%20manipulate%20these%20fields%20later%20on%2C%20not%20all%20of%20them%20exist%20and%20we%20get%20an%20error%20such%20as%20the%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESyslog%0A%7C%20extend%20kvpairs%3Dparse_json(extract_all(%22(%5C%5Cw%2B)%3D((%3F%3A%5B%5C%5Cw-%5C%5C.%3A%5D%2B)%7C%5C%22(%3F%3A%5B%5E%5C%22%5D%2B)%5C%22)(%3F%3A%5C%5Cs%7C%24)%22%2C%20dynamic(%5B1%2C2%5D)%2C%20SyslogMessage))%0A%7C%20mv-apply%20kvpairs%20on%20(summarize%20make_bag(pack(tostring(replace('-'%2C%20''%2C%20tostring(kvpairs%5B0%5D)))%2C%20trim(%22%5C%22%22%2Ctostring(kvpairs%5B1%5D)))))%0A%7C%20evaluate%20bag_unpack(bag_)%0A%7C%20project-rename%20BytesIn%3Dtoint(rcvdbyte)%2C%20BytesOut%3Dtoint(sentbyte)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ChristopherKerryCoop_0-1616066429511.png%22%20style%3D%22width%3A%20728px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F265164i4A79E527EA1D7967%2Fimage-dimensions%2F728x111%3Fv%3Dv2%22%20width%3D%22728%22%20height%3D%22111%22%20role%3D%22button%22%20title%3D%22ChristopherKerryCoop_0-1616066429511.png%22%20alt%3D%22ChristopherKerryCoop_0-1616066429511.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20we%20turn%20off%20this%20column%20reference%20validation%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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)

 

ChristopherKerryCoop_0-1616066429511.png

 

How do we turn off this column reference validation?

2 Replies
best response confirmed by ChristopherKerry (Occasional Contributor)

That works brilliantly - thanks @Gary Bushey