Query = ..... | eval zip=mvzip(splunk_column_name, actual_column_name) |mvexpand zip |rex field=zip "(?<splunk_column_name>[^,]+),(?<actual_column_name>[^,]+)" | fields - zip |eval zip=mvzip(splunk_column_name,actual_column_name) |foreach field* [eval x<<MATCHSTR>>=if(match(zip,"^<<FIELD>>"),replace(zip,"^.*,",""),'')] | foreach field* [eval {x<<MATCHSTR>>}=<<FIELD>> | fields - x<<MATCHSTR>>,zip] | stats values(*) by ***[list of fields] *** | rename values(*) AS *
splunk_column_name is a multivalued field containing the list of field names (field1, field2, field3,..)
actual_column_name is a multivalued field containing the list of actual field names
( 1. for event_type_id="download" , you have filename, bytes ...
2. for event_type_id="client_error" , you have error_code, description .... )
| eval zip=mvzip(splunk_column_name, actual_column_name) |mvexpand zip |rex field=zip "(?<splunk_column_name>[^,]+),(?<actual_column_name>[^,]+)" | fields - zip |eval zip=mvzip(splunk_column_name,actual_column_name)
This part of the query expands the multivalued fields splunk_column_name, actual_column_name and creates a new field named zip which contains these the values in these two fields combined.
|foreach field* [eval x<<MATCHSTR>>=if(match(zip,"^<<FIELD>>"),replace(zip,"^.*,",""),'')]
This part of the query creates new fields x1, x2, x3 .... (x1 contains the column names for field1, x2 for field2 ......)
You will get something like this
Timestamp,event_type_id,field1,field2,splunk_field_name,actual_field_name,zip,x1,x2
3/20/16 12:00:00.000 AM,download,file1,30,field1,filename,"field1,filename",filename,
3/20/16 12:00:00.000 AM,download,file1,30,field2,size in bytes,"field2,size in bytes",,size in bytes
3/20/16 12:00:00.000 AM,client_error,404,Not Found,field1,error_code,"field1,error_code",error_code,
3/20/16 12:00:00.000 AM,client_error,404,Not Found,field2,description,"field2,description",,description
| foreach field* [eval {x<<MATCHSTR>>}=<<FIELD>>
Creates new fields with the names of fields as retrieved from x1, x2 ,... and takes value from field1, field2,...
Timestamp,event_type_id,filename,size in bytes,error_code,description
3/20/16 12:00:00.000 AM,download,file1 ,,,
3/20/16 12:00:00.000 AM,download,,30,,
3/20/16 12:00:00.000 AM,client_error,,,404,
3/20/16 12:00:00.000 AM,client_error,,,,Not Found
| fields - x<<MATCHSTR>>,zip] | stats values(*) by ***[list of fields] *** | rename values(*) AS *
Removes x1, x2, x3 .... , zip and use stats to get values for all fields for a particular event in one row.
Timestamp,event_type_id,filename,size in bytes,error_code,description
3/20/16 12:00:00.000 AM,download,file1,30,,
3/20/16 12:00:00.000 AM,client_error,,,404,NotFound
... View more