How to write a transaction search to expand multivalued fields into separate single value events?

[dc595]

Hi,

I'm having difficulties expanding a multivalued Transaction event back into individual events. The overall goal is to use the Transaction command | transaction Device SessionID maxpause=300s nullstr="~" mvlist=t delim="','"to create a common key between time based events from 3 different indexes, then save those grouped events into a summary index, as displayed in the picture below.

With multivalued fields I'm having difficulties grouping a field with another **i.e. TransactionName ** with its respective *** Timestamp.***
I would like to separate each multivalued row into an single value event, maintaining the fields and order.

I can provide more detail if required

Thank you in advance

[sundareshr · Accepted solution]

First, You will have to combine them into a single field using mvzip. Then expand them in to individual rows using mvexpand and finally, split the fields using split() or rex. Something like this should get you started

...| transaction Device SessionID maxpause=300s nullstr="~" mvlist=t delim="','" | eval z=mvzip('data.Timestamp', mvzip(epoch, mvzip(delta, mvzip(TransactionName mvzip(Opcode, mvzip(SequenceSent, SequenceReceived)))))) | mvexpand z | rex field=z "(?<timestamp>[^,]+),(?<epoch>[^,]+),(?<delta>[^,]+),(?<TransactionName>[^,]+),(?<Opcode>[^,]+),(?<SequenceSent>[^,]+),(?<SequenceReceived>[^,]+)" | fields - z - 'data.Timestamp'

[dc595]

Thank you this was very helpful

[sundareshr]

@dc595, if this helped, please click accept to close it out.

[niketn]

One of the options could be to add data.Timestamp and TransactionName as new field using eval, then create transaction and print new field name. Provided data.Timestamp is string time and not epoc. If it is epoc then use strftime command after your base search.

<your base search> | eval data.Timestamp=strftime(data.TimeStamp, "%Y-%m-%d %H:%M:%s.%3N) | eval TransactionTimeAndName= data.TimeStamp + " - " + TransactionName |  fields - data.Timestamp TransactionName | transaction Device SessionID maxpause=300s nullstr="~" mvlist=t delim="','" | <your remaining search> 

Ideally you should use stats instead of transaction. Following is a sample of stats:

stats count as eventcount values(TransactionTimeAndName) as "Transaction Time and Name" min(_time) as MinTime max(_time) as MaxTime by Device SessionID | eval duration=MaxTime-MinTime |

[dc595]

The mvzip approach is what I was trying to accomplish, but thank you for your response it's very helpful