topic Re: Extract Key value data from raw events in Splunk Search

Extract Key value data from raw events

bijodev1 — Mon, 07 Jun 2021 10:42:16 GMT

Hi Team,

I am trying to pull the data for the below raw events.

{"name":"Content-Length","valueList":["94"]}
{"name":"Referer","valueList":["https://www.abc.com/xyz/pageID"]}

The end result I am looking for is :

Content-Length : 94

Referer : /xyz/pageID

I am not sure if this is really possible to pull make a value to a key pair. Still trying to understand the regex but no luck. if someone could please give a hint or help on how to work with this.

Re: Extract Key value data from raw events

kamlesh_vaghela — Mon, 07 Jun 2021 11:10:45 GMT

@bijodev1

Can you please try this?

YOUR_SEARCH | rename valueList{} as value | table name value | eval result=name.": ".value

My Sample Search :

| makeresults | eval raw="{\"name\":\"Content-Length\",\"valueList\":[\"94\"]}|{\"name\":\"Referer\",\"valueList\":[\"https://www.abc.com/xyz/pageID\"]}",raw=split(raw,"|")| mvexpand raw| rename raw as _raw | extract | rename valueList{} as value | table name value | eval result=name.": ".value

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Extract Key value data from raw events

bijodev1 — Tue, 08 Jun 2021 16:07:31 GMT

thanks @kamlesh_vaghela for the response. The problem is the events are very dense and each field has it's own nested key value pairs. So I am not sure how to pull this data.

"Header":[{"name":"Content-Length","valueList":["94"]},{"name":"Referer","valueList":["https://www.abc.com/xyz/pageID"]},{"name":"User-Agent","valueList":["Androidv11"]}.

like this the entire events consist of name and valueList. I am not sure how to remove this from their raw events.

Re: Extract Key value data from raw events

kamlesh_vaghela — Tue, 08 Jun 2021 17:05:01 GMT

@bijodev1

Can you please try this?

YOUR_SEARCH | rex field=_raw "\"Header\":\[(?<raw>.*).$" | rex field=raw mode=sed "s/},{/}|{/g" | eval raw=split(raw,"|")| mvexpand raw| rename raw as _raw | extract | rename valueList{} as value | table name value | eval result=name.": ".value

My Sample Search :

| makeresults | eval _raw="\"Header\":[{\"name\":\"Content-Length\",\"valueList\":[\"94\"]},{\"name\":\"Referer\",\"valueList\":[\"https://www.abc.com/xyz/pageID\"]},{\"name\":\"User-Agent\",\"valueList\":[\"Androidv11\"]}." | rex field=_raw "\"Header\":\[(?<raw>.*).$" | rex field=raw mode=sed "s/},{/}|{/g" | eval raw=split(raw,"|")| mvexpand raw| rename raw as _raw | extract | rename valueList{} as value | table name value | eval result=name.": ".value

Sample event:

"Header":[{"name":"Content-Length","valueList":["94"]},{"name":"Referer","valueList":["https://www.abc.com/xyz/pageID"]},{"name":"User-Agent","valueList":["Androidv11"]}.

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Extract Key value data from raw events

bijodev1 — Wed, 09 Jun 2021 09:42:20 GMT

Hi @kamlesh_vaghela thank you so much for the query. Is it possible to display the table for the "path" and have the "result" column.

PATH | Result.

like PATH - should contain only single row and Result can have multiple data.

Re: Extract Key value data from raw events

kamlesh_vaghela — Wed, 09 Jun 2021 09:58:13 GMT

@bijodev1

PATH means Referer field?? Can you please share your expected op from your given sample?

Re: Extract Key value data from raw events

bijodev1 — Wed, 09 Jun 2021 10:38:26 GMT

@kamlesh_vaghela

thanks for your query. It was really helpful. I was able to extract the data exactly the way I wanted. Inside that headerlist - we have one more field orderID. I want to use that as one column and the rest of them as the result column.

Re: Extract Key value data from raw events

kamlesh_vaghela — Wed, 09 Jun 2021 11:06:01 GMT

@bijodev1

Can you please try this?

| makeresults | eval _raw="\"Header\":[{\"name\":\"orderID\",\"valueList\":[\"101\"]},{\"name\":\"Content-Length\",\"valueList\":[\"94\"]},{\"name\":\"Referer\",\"valueList\":[\"https://www.abc.com/xyz/pageID\"]},{\"name\":\"User-Agent\",\"valueList\":[\"Androidv11\"]}." | rex field=_raw "\"Header\":\[(?<raw>.*).$" | rex field=raw mode=sed "s/},{/}|{/g" | eval raw=split(raw,"|") |eval a=1 | accum a| mvexpand raw| rename raw as _raw | extract | rename valueList{} as value | eval result=name.": ".value | eval orderID = if(name="orderID",value,null()) | stats values(result) as result values(orderID) as orderID by a | table orderID result

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Extract Key value data from raw events

bijodev1 — Wed, 09 Jun 2021 11:34:42 GMT

@kamlesh_vaghela

It's kind of working but not fully. Let's say there are 20000+ events.

Like in a raw events there can be request where the order ID would be same for many of the request.

I will like to pull the logs for specific orderID and the second column would be list of headers with it.

It would look like :

orderID | Result

a123 | content-length, referer, etc

a214 | content-length, referer, accept-language

Re: Extract Key value data from raw events

kamlesh_vaghela — Wed, 09 Jun 2021 12:25:21 GMT

@bijodev1

Can you please share some sample events with OrderId?

Re: Extract Key value data from raw events

bijodev1 — Wed, 09 Jun 2021 12:35:32 GMT

@kamlesh_vaghela

I need to take the count of unique order ID along with result column.

Currently I don't have a sample data with me.

Re: Extract Key value data from raw events

bijodev1 — Wed, 09 Jun 2021 13:11:19 GMT

@kamlesh_vaghela

along with the same data. I want to add status code, it is not working.

The status code is part of raw events but not in "Headers" it is different field with the name status.

*.... | rex field=_raw "\"Headers\":\[(?<raw>.*).$"
| rex field=raw mode=sed "s/},{/}|{/g"
| eval raw=split(raw,"|")| mvexpand raw | rename raw as _raw
| extract | rename valueList{} as value | table name value | eval result=name.": ".value | eval orderID = if(name="OrderID",value,null())
| stats count by orderID status

It is not working for me.

Re: Extract Key value data from raw events

bijodev1 — Wed, 09 Jun 2021 15:20:29 GMT

@kamlesh_vaghela

Can you put some light on the regex which you have wrote :

| rex field=_raw "\"requestHeaderList\":\[(?<raw>.*).$"

| rex field=raw mode=sed "s/},{/}|{/g"

| eval raw=split(raw,"|")| mvexpand raw | rename raw as _raw

if you can just guide what these query basically doing. It would be really helpful.

Thank you so much.

Re: Extract Key value data from raw events

kamlesh_vaghela — Wed, 09 Jun 2021 16:06:50 GMT

@bijodev1

| rex field=_raw "\"Header\":\[(?<raw>.*).$"

This will extract JSON data from _raw event and assign into new field raw.

From: "Header":[{"name":"Content-Length","valueList":["94"]},{"name":"Referer","valueList":["https://www.abc.com/xyz/pageID"]},{"name":"User-Agent","valueList":["Androidv11"]}. to: {"name":"Content-Length","valueList":["94"]},{"name":"Referer","valueList":["https://www.abc.com/xyz/pageID"]},{"name":"User-Agent","valueList":["Androidv11"]}

| rex field=raw mode=sed "s/},{/}|{/g"

This will replace commas between different json with pipe(|). It is required for next operation

From: {"name":"Content-Length","valueList":["94"]},{"name":"Referer","valueList":["https://www.abc.com/xyz/pageID"]},{"name":"User-Agent","valueList":["Androidv11"]} To: {"name":"Content-Length","valueList":["94"]}|{"name":"Referer","valueList":["https://www.abc.com/xyz/pageID"]}|{"name":"User-Agent","valueList":["Androidv11"]}

This will split raw into multiple events and assign into _raw and keep unique value, here it is field a.

From {"name":"Content-Length","valueList":["94"]}|{"name":"Referer","valueList":["https://www.abc.com/xyz/pageID"]}|{"name":"User-Agent","valueList":["Androidv11"]} To: {"name":"Content-Length","valueList":["94"]} {"name":"Referer","valueList":["https://www.abc.com/xyz/pageID"]} {"name":"User-Agent","valueList":["Androidv11"]}

Upto now we cab easily access the name and valueList fields to perform next operation

😀

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Extract Key value data from raw events

kamlesh_vaghela — Wed, 09 Jun 2021 17:46:28 GMT

@bijodev1

Can you please try this?

| makeresults count=2000 | eval a=1 | accum a | eval _raw="\"Header\":[{\"name\":\"orderID\",\"valueList\":[\"a".a."\"]},{\"name\":\"Content-Length\",\"valueList\":[\"94\"]},{\"name\":\"Referer\",\"valueList\":[\"https://www.abc.com/xyz/pageID\"]},{\"name\":\"User-Agent\",\"valueList\":[\"Androidv11\"]}." | rex field=_raw "\"Header\":\[(?<raw>.*).$" | rex field=raw mode=sed "s/},{/}|{/g" | eval raw=split(raw,"|") |eval a=1 | accum a| mvexpand raw| rename raw as _raw | extract | rename valueList{} as value | eval result=name.": ".value | eval orderID = if(name="orderID",value,null()) | stats delim="," values(name) as name values(orderID) as orderID by a | nomv name | table orderID name

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Extract Key value data from raw events

bijodev1 — Thu, 10 Jun 2021 10:08:05 GMT

@kamlesh_vaghela

thank you so much Kamlesh. It was worth look into this.

Re: Extract Key value data from raw events

bijodev1 — Thu, 10 Jun 2021 11:45:20 GMT

@kamlesh_vaghela

sorry to bother you again. for the same query

{"name":"Content-Length","valueList":["94"]}
{"name":"Referer","valueList":["https://www.abc.com/xyz/pageID"]}
{"name":"Cookie","valueList:[abc=123;s_id=9wefdrtunhfkd6; df_id=xijuhygsdd342;data=helloworld]}

I want to pull the complete value List for the name Cookie. Based on that I need to run the query

stats count by Cookie - where it display how much was the count of ---- abc, s_id, df_id, data

Re: Extract Key value data from raw events

kamlesh_vaghela — Thu, 10 Jun 2021 12:07:01 GMT

@bijodev1

Can you please try this?

| makeresults | eval raw="{\"name\":\"Content-Length\",\"valueList\":[\"94\"]}|{\"name\":\"Referer\",\"valueList\":[\"https://www.abc.com/xyz/pageID\"]}|{\"name\":\"Cookie\",\"valueList\":[\"abc=123;s_id=9wefdrtunhfkd6; df_id=xijuhygsdd342;data=helloworld\"]}|{\"name\":\"Content-Length\",\"valueList\":[\"94\"]}|{\"name\":\"Referer\",\"valueList\":[\"https://www.abc.com/xyz/pageID]\"}|{\"name\":\"Cookie\",\"valueList\":[\"abc=123;s_id=9wefdrtunhfkd6; df_id=xijuhygsdd342;data=helloworld]\"}", raw=split(raw,"|") | mvexpand raw | rename raw as _raw | extract | where name= "Cookie" | rename valueList{} as _raw | extract | stats count(eval(isnotnull(abc))) as abc, count(eval(isnotnull(s_id))) as s_id, count(eval(isnotnull(df_id))) as df_id, count(eval(isnotnull(data))) as data

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Extract Key value data from raw events

bijodev1 — Thu, 10 Jun 2021 12:51:09 GMT

@kamlesh_vaghela

can't we make something in the same regex.

| rex field=_raw "\"Headers\":\[(?<raw>.*).$"

| rex field=raw mode=sed "s/},{/}|{/g"

Because the "cookie" field is the part of Headers and it's values are separated with semi colon ;. There are many values under Cookie fields which uses semi colon as separator.

"Headers" :{"name":"Content-Length","valueList":["94"]}, [{"name":"Cookie","valueList":["xrm7=-762BMB0; exp-ck=1; CQTEST=1; xTpYacs=; DQ=Y; DX=wsdaquijhs; S_ID=xyat; Latency=1; TB_N=10; TB_SFOU-100=; C_Flag=0; vct_id=9XgVPsnKid7aaiY; bct_id=X89wgVnSdKdiU1gqaa]},…………….

Re: Extract Key value data from raw events

kamlesh_vaghela — Thu, 10 Jun 2021 13:11:56 GMT

@bijodev1

Can you please try this?

| makeresults | eval _raw="\"Headers\" :{\"name\":\"Content-Length\",\"valueList\":[\"94\"]}, [{\"name\":\"Cookie\",\"valueList\":[\"xrm7=-762BMB0; exp-ck=1; CQTEST=1; xTpYacs=; DQ=Y; DX=wsdaquijhs; S_ID=xyat; Latency=1; TB_N=10; TB_SFOU-100=; C_Flag=0; vct_id=9XgVPsnKid7aaiY; bct_id=X89wgVnSdKdiU1gqaa]\"}" | rex field=_raw "\[\{\"name\":\"Cookie\",\"valueList\":\[\"(?<coockie_value>[^\]]+)" | search coockie_value=* | rename coockie_value as _raw | extract pairdelim=";" kvdelim="=" | stats count(*) as *

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.