Splunk Search

How to write multiple fields into one column as values?

snetuschil
New Member

Hi,

I have a data that looks like this:

----------

*ID1       field1=value1&field2=value2&field3=value3*

----------

*ID2       field1=value1&field3=value3&field4=value4&field7=value7*

----------

The fields are extracted separately. However, I would like to summerize them all in one field and list them in a table according to ID. The result should look like this:

| ID  | field  | value  |
| ID1 | field1 | value1 |
| ID1 | field2 | value2 |
| ID1 | field3 | value3 |
| ID2 | field1 | value1 |
| ID2 | field3 | value3 |
| ID2 | field4 | value4 |
| ID2 | field7 | value7 |

The field names are variable both in their names and count per ID. I tried unsuccessfully to extract a mv-field.

Is there a way to do this?

Best regards
Sascha

0 Karma
1 Solution

vasanthmss
Motivator

try something like this,

your base search .. | rex "(?<id>\w+)\s(?<fields>.*)" | eval temp=split(fields,"&") | mvexpand temp | rex field=temp "(?<field>.*)=(?<value>.*)" | table id, field, value 

Sample

|stats c | eval _raw="ID1 field1=value1&field2=value2&field3=value3" |append[|stats c | eval _raw="ID2 field1=value1&field2=value2&field3=value3&field4=value4"] | rex "(?<id>\w+)\s(?<fields>.*)" | eval temp=split(fields,"&") | mvexpand temp | rex field=temp "(?<field>.*)=(?<value>.*)" | table id, field, value

your raw event should like as per your question... post your sample events .

ID1 field1=value1&field2=value2&field3=value3

Hope this helps you!!

V

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

Here's the basic method -

| makeresults | eval myraw = "ID1 field1=value1&field2=value2& field3=value3"
| append [| makeresults | eval myraw = "ID2 field1=value1&field3=value3&field4=value4&field7=value7"]
| append [| makeresults | eval myraw = "ID3 field1=George%20has%20a%20cat%20&field2=2500"]

The above code just generates test data. this processes it to produce the table -

| rex field=myraw "^(?<ID>\w+)\s"
| rex field=myraw max_match=10 "[\s|&](?P<fieldname>[^=]*)=(?P<fieldvalue>[^&]*)"
| eval fieldpair=mvzip(fieldname,fieldvalue,"!!!!")
| table ID fieldpair
| mvexpand fieldpair
| rex  field=fieldpair "(?<fieldname>.*)!!!!(?<fieldvalue>.*)"
| table ID fieldname fieldvalue
0 Karma

vasanthmss
Motivator

try something like this,

your base search .. | rex "(?<id>\w+)\s(?<fields>.*)" | eval temp=split(fields,"&") | mvexpand temp | rex field=temp "(?<field>.*)=(?<value>.*)" | table id, field, value 

Sample

|stats c | eval _raw="ID1 field1=value1&field2=value2&field3=value3" |append[|stats c | eval _raw="ID2 field1=value1&field2=value2&field3=value3&field4=value4"] | rex "(?<id>\w+)\s(?<fields>.*)" | eval temp=split(fields,"&") | mvexpand temp | rex field=temp "(?<field>.*)=(?<value>.*)" | table id, field, value

your raw event should like as per your question... post your sample events .

ID1 field1=value1&field2=value2&field3=value3

Hope this helps you!!

V

snetuschil
New Member

Hi,

this works very well on my data, thank you very much! The dummy data I posted was simplified, which is why I get some clutter in the transformed table. The real data looks like this:

6388700277 27 attribution 1486110252126 anb_3=-1&uids=6331855527&anb_2=xpv&anb_1=-1&anb_0=-1&promo_3=-1&promo_2=-1&promo_1=-1&promo_0=-1&entrysourceID_3=-1&entrysourceID_2=-1&entrysourceID_1=-1&entrysourceID_0=-1&akt_3=-1&akt_2=-1&akt_1=-1&akt_0=-1&wid_3=6388700277&version_3=-1&wid_2=6385404791&version_2=-1&wid_1=6379987259&version_1=-1&wid_0=6375153270&version_0=-1

Your extraction works fine for all fields but the first field where it should only read "anb_3". The values "27" and "attribution" are fixed "1486110252126" is different for every ID:

id field value
6388700277 27 attribution 1486110252126 anb_3 -1
6388700277 uids 6331855527
6388700277 anb_2 xpv
6388700277 anb_1 -1
6388700277 anb_0 -1
6388700277 promo_3 -1
6388700277 promo_2 -1
6388700277 promo_1 -1
6388700277 promo_0 -1
6388700277 entrysourceID_3 -1
6388700277 entrysourceID_2 -1
6388700277 entrysourceID_1 -1
6388700277 entrysourceID_0 -1
6388700277 akt_3 -1
6388700277 akt_2 -1
6388700277 akt_1 -1
6388700277 akt_0 -1
6388700277 wid_3 6388700277
6388700277 version_3 -1
6388700277 wid_2 6385404791
6388700277 version_2 -1
6388700277 wid_1 6379987259
6388700277 version_1 -1
6388700277 wid_0 6375153270
6388700277 version_0 -1

Best regards
Sascha

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Nice. Way more elegant than mine.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

1) How do you identify the ID field value? is it everything before the first space, or what?

2) do the fields have spaces in them, or have they been properly masked? For instance, if field1 had the value george has a cat" and field2 had the value 2500, would it say

field1="George has a cat"&field2=2500

or would it say

field1=George%20has%20a%20cat%20&field2=2500

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...