topic Re: How to write multiple fields into one column as values? in Splunk Search

How to write multiple fields into one column as values?

snetuschil — Thu, 02 Feb 2017 14:35:03 GMT

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

Re: How to write multiple fields into one column as values?

DalJeanis — Thu, 02 Feb 2017 20:54:16 GMT

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

Re: How to write multiple fields into one column as values?

vasanthmss — Thu, 02 Feb 2017 21:25:31 GMT

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!!

Re: How to write multiple fields into one column as values?

DalJeanis — Thu, 02 Feb 2017 21:27:31 GMT

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

Re: How to write multiple fields into one column as values?

DalJeanis — Thu, 02 Feb 2017 21:28:53 GMT

Nice. Way more elegant than mine.

Re: How to write multiple fields into one column as values?

snetuschil — Tue, 29 Sep 2020 12:43:06 GMT

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