How to merge my data?

[kbarker302]

My raw data looks like this:

Timestamp      Field1  Field2  Field3  
2017-01-01     AAA     Key1    Key1val
2017-01-01     AAA     Key2    Key2val
2017-01-01     AAA     Key3    Key3val

For my purposes, Timestamp and Field1 uniquely identify a set of events that I would like to merge together.

I would like the formatted results to look like this:

Timestamp    Field1  Key1     Key2     Key3
2017-01-01   AAA     Key1val  Key2val  Key3val

Note that in the raw data, Field2 contains the values I would like to appear as column headers, with corresponding row values from Field3.

I have tried various things using eval, stats, eventstats, and appendcols, but have not been able to achieve the desired effect. Is anything like this possible using SPL?

[vasanthmss · Accepted solution]

Hello,

try the below search,

.... your base search  |eval temp=Timestamp+"###"+Field1   |xyseries temp Field2, Field3 | rex field=temp "(?<Timestamp>.*)###(?<Field1>.*)" | fields - temp | table Timestamp, Field1, *

Sample,

raw data creation,

|stats c | eval  Timestamp="2017-01-01" | eval Field1="AAA" | eval Field2 ="Key1" | eval Field3="Key1val" |append[ |stats c | eval  Timestamp="2017-01-01" | eval Field1="AAA" | eval Field2 ="Key2" | eval Field3="Key2val" ] |append[ |stats c | eval  Timestamp="2017-01-01" | eval Field1="AAA" | eval Field2 ="Key3" | eval Field3="Key3val" ] | table Timestamp, Field1, Field2, Field3 

Sample data with logic,

|stats c | eval  Timestamp="2017-01-01" | eval Field1="AAA" | eval Field2 ="Key1" | eval Field3="Key1val" |append[ |stats c | eval  Timestamp="2017-01-01" | eval Field1="AAA" | eval Field2 ="Key2" | eval Field3="Key2val" ] |append[ |stats c | eval  Timestamp="2017-01-01" | eval Field1="AAA" | eval Field2 ="Key3" | eval Field3="Key3val" ] | table Timestamp, Field1, Field2, Field3 |eval temp=Timestamp+"###"+Field1   |xyseries temp Field2, Field3 | rex field=temp "(?<Timestamp>.*)###(?<Field1>.*)" | fields - temp | table Timestamp, Field1, *

Hope this will helps you,

[kbarker302]

Outstanding - that does exactly what I wanted. Thank you very much! That really is some advanced stuff. Also, I hadn't seen an asterisk used in the table command before.