topic Re: How to extract multiple key value pairs from the logs in Splunk Search

How to extract multiple key value pairs from the logs

knr26 — Thu, 02 Aug 2018 18:02:09 GMT

We have a requirement where we need to extract the multiple key value pairs from the log files

Ex: places= multiple, values = They are (city=ABC;location=PNX.AZ;45678=0879), extraction=true

I am trying to extract the values enclosed in brackets which are seperated by ";". The output I am looking for is

a) city as the filed and value as ABC
b)location as field name and PNX.AZ as its value
c) 45678 as field name and 0879 as its value ( I read that splunk field name cannot start with a number - If this is so, is there any possibility that we can add a value before it, eg: A45678 as field name )

There are many events in the same format and all the values for the field city should go in "city" field.

I am so unsure if this has to be done by a regex or if I can use a split command to extract the values. User is looking for a search where he can query by City to find list of all the cities and similarly stats count by location should give all the locations

Thanks for your help

Re: How to extract multiple key value pairs from the logs

sudosplunk — Thu, 02 Aug 2018 18:35:37 GMT

Hello there,

Assuming below statements are always true, give this a shot. your_search | rex field=_raw "\(city\=(?<city>\w+)\;location\=(?<location>\w+\.\w+)\;\d+\=(?<A456789>\d+)"

a) city field will always start with city=
b)location field will always start with location= and the value is always city.state (For ex. SFO.CA, NYC.NY)

c) 45678 field will always start with numbers=

Tested here: https://regex101.com/r/qRmhFi/2

If it works, then your final search query will look something like your_search | rex field=_raw "\(city\=(?<city>\w+)\;location\=(?<location>\w+\.\w+)\;\d+\=(?<A456789>\d+)" | stats count by location

Re: How to extract multiple key value pairs from the logs

knr26 — Thu, 02 Aug 2018 19:38:19 GMT

Thanks for your response surya. Those are just examples, we do have a bunch of Key value pairs within the brackets. Most of them are digits and they are not consistent or in the order.

Eg: If the first event has only three key value pairs, second have more than 20 and the third has 10.

Do we need to write regex to match all of these seperately? Please note that they are not in the order too

Re: How to extract multiple key value pairs from the logs

somesoni2 — Thu, 02 Aug 2018 19:46:32 GMT

Are those values within parenthesis extracted in a field?

Re: How to extract multiple key value pairs from the logs

sudosplunk — Thu, 02 Aug 2018 19:52:26 GMT

In that case, can you provide sample data and highlight the fields which are supposed to be extracted. Please mask unwanted information. Thank you.

Re: How to extract multiple key value pairs from the logs

knr26 — Thu, 02 Aug 2018 20:06:19 GMT

No, they are not. The values in the parenthesis are still within a field along with a string message before the paranthesis

In the first event : value= null, Message= 100 the message is (123=456.78; 45=Yes; .......)
In the second event : value= null, Message = 101 the message is ( 123=678 ; 45=No ; .....)
Third event : value=null, Message = 102 the message is (Abc=wbc; 123=456.78 ; 21=56; 45=No ; ....)
Fouth event: value=null, Message =103 the message is (45=No;......) , value1=zero

The values in the parenthisis are still within a field called "Message"

Re: How to extract multiple key value pairs from the logs

knr26 — Thu, 02 Aug 2018 20:16:04 GMT

The values in the parenthesis are still within a field called "message". the events looks like below

First event: value=null , message = 100 the message is (123=456.56;45=Yes;......), key=value
Second event: value=null, message= 101 the message is (123=12; 45=No; ...) , key1=value1
Third event: value=1, message = 102 the message is (abc=wxy.yx;123=456.56,45=no;.....)
Fourth event: value=2, message= 103 the message is (45=Yes;...) , key2=value2

I need the values in the paranthesis in a field, so that I can look for the different values of 123, 45 , abc in the logs with the help of a query.

Thanks

Re: How to extract multiple key value pairs from the logs

somesoni2 — Thu, 02 Aug 2018 20:36:01 GMT

Give this a try

your base search 
| extract pairdelim=";" mv_add=t

Re: How to extract multiple key value pairs from the logs

knr26 — Thu, 02 Aug 2018 21:24:07 GMT

Thanks for your response. But, this did not work. The values are not extracted

Re: How to extract multiple key value pairs from the logs

somesoni2 — Thu, 02 Aug 2018 21:39:29 GMT

See this (everything before extract is to generate sample data)

| gentimes start=-1 | eval raw="value= null, Message= 100 the message is (123=456.78; 45=Yes; .......)#value= null, Message = 101 the message is ( 123=678 ; 45=No ; .....) #value=null, Message = 102 the message is (Abc=wbc; 123=456.78 ; 21=56; 45=No ; ....) #value=null, Message =103 the message is (45=No;......) , value1=zero" | table raw | makemv raw delim="#" | mvexpand raw | rename raw as _raw 
| extract pairdelim=";" kvdelim="=" clean_keys=f

Re: How to extract multiple key value pairs from the logs

knr26 — Tue, 07 Aug 2018 17:47:09 GMT

Thank you so much. This worked