topic Re: How to parse field data with delimiter from dbxquery result? in Splunk Search

How to parse field data with delimiter from dbxquery result?

LearningGuy — Mon, 01 May 2023 11:19:11 GMT

how to parse field data with delimiter from dbxquery result?
For example: Dbxquery result is
FW Rule name: DNS
FW Rule: "protocol":udp","port:53","dest_IP:10.10.10.1","direction:ingress"
I would like to have a FW rule display in a separate table in dashboard
Dropdown menu: FW Rule: DNS
Protocol | Port | Dest IP | Direction
UDP | 53 | 10.10.10.1 | Ingress

Thanks

Re: How to parse field data with delimiter from dbxquery result?

richgalloway — Mon, 01 May 2023 13:36:24 GMT

If "protocol":udp" contains an extra quotation mark before the colon then this SPL should do the job. Otherwise, we may have to resort to rex commands.

<<your dhxquery command>> | extract pairdelim=",\"", kvdelim=":" | rename protocol as Protocol, port as Port, dest_IP as "Dest IP", direction as Direction | table Protocol Port "Dest IP" Direction

Re: How to parse field data with delimiter from dbxquery result?

LearningGuy — Mon, 01 May 2023 15:42:40 GMT

Hello,
My apology. Here's actually the format:
[ {"protocol":"value","port":"value","destIP":value}
{"protocol":"value","port":"value","destIP":value}
{"protocol":"value","port":"value","destIP":value} ]
Note that value can be within bracket "value", can also be value (without quote).. this is just an example.. actual data has more key pair and more rows. Thanks

Output needed:
protocol | Port | Dest IP
TCP | 22 | 10.10.10.1
UDP | 53 | 10.10.10.2
TCP | 80 | 10.10.10.3

Re: How to parse field data with delimiter from dbxquery result?

richgalloway — Tue, 02 May 2023 15:13:27 GMT

See if this works better. The sample event is not quite JSON so the JSON SPL commands won't work. That means parsing the event manually.

| makeresults | eval _raw="[ {\"protocol\":\"value\",\"port\":\"value\",\"destIP\":value} {\"protocol\":\"value\",\"port\":\"value\",\"destIP\":value} {\"protocol\":\"value\",\"port\":\"value\",\"destIP\":value} ]" ``` Above creates test data. Remove IRL ``` ``` Strip off brackets and break at newlines ``` | eval data=split(trim(_raw,"][ ")," ") ``` Put each line in a different event ``` | mvexpand data ``` Extract fields ``` | rex field=data "protocol\":\"?(?<protocol>[^,\"}]+)" | rex field=data "port\":\"?(?<Port>[^,\"}]+)" | rex field=data "destIP\":\"?(?<destIP>[^,\"}]+)" | rename destIP as "Dest IP" | table protocol Port "Dest IP"