How to extract multiple key value pairs from the logs

[knr26]

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

[somesoni2]

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

[knr26]

Thank you so much. This worked

[sudosplunk]

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

[knr26]

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

[sudosplunk]

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

[somesoni2]

Are those values within parenthesis extracted in a field?

[knr26]

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

[somesoni2]

Give this a try

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

[knr26]

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

[knr26]

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"