Splunk Search

How to extract multiple key value pairs from the logs

knr26
New Member

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

Tags (1)
0 Karma

somesoni2
Revered Legend

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
New Member

Thank you so much. This worked

0 Karma

sudosplunk
Motivator

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

0 Karma

knr26
New Member

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

0 Karma

sudosplunk
Motivator

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

0 Karma

somesoni2
Revered Legend

Are those values within parenthesis extracted in a field?

0 Karma

knr26
New Member

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

0 Karma

somesoni2
Revered Legend

Give this a try

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

knr26
New Member

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

0 Karma

knr26
New Member

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"

0 Karma
Get Updates on the Splunk Community!

Data Preparation Made Easy: SPL2 for Edge Processor

By now, you may have heard the exciting news that Edge Processor, the easy-to-use Splunk data preparation tool ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Tips & Tricks When Using Ingest Actions

Tune in to learn about:Large scale architecture when using Ingest ActionsRegEx performance considerations ...