Splunk Search

How to merge my data?

Communicator

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?

0 Karma
1 Solution

Motivator

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,

View solution in original post

Motivator

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,

View solution in original post

Communicator

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!