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
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!!
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
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!!
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
Nice. Way more elegant than mine.
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