How to split single multivalue event into multiple multivalue events?

[ERFFFFF]

Hello everyone !

I'm trying to split a single multivalue event into multiple multivalue events.

Here is my base search :

sourcetype="xxxx"
| transaction clientip source id maxspan=5m startswith="yesorno=yes" endswith="event=connected" keepevicted=true mvlist=true,responsetime,status,yesorno,clientip,event,_time
| sort _time
| eval MergedColumns=responsetime . " " . yesorno
| stats list(event) as event, list(MergedColumns) as MergedColumns, list(responsetime) as responsetime, by yesorno, clientip, id
| where !(event=="connected")
| table MergedColumns source clientip

Unfortunately, i am obliged to use a transaction here and not the stats command.

Here is my data :

MergedColumns	source	clientip
10 yes 510 no 348 no 50886 no	username1	xxx.xxx.xxx.xxx
10 yes 513 no 1239 no 9 yes 160 no 340 no 21421 no 509 no 685 no 13799 no 149 no	username2	xxx.xxx.xxx.xxx

I would like to split my event on the "xxx yes" like so :

MergedColumns	source	clientip
10 yes 510 no 348 no 50886 no	username1	xxx.xxx.xxx.xxx
10 yes 513 no 1239 no	username2	xxx.xxx.xxx.xxx
9 yes 160 no 340 no 21421 no 509 no 685 no 13799 no 149 no	username2	xxx.xxx.xxx.xxx

Moreover, here, i have only two "xxx yes" in the same multivalue event, but i can possibly have more than that (like 3 or 4).
I tried lots of things but none seems to work...
(here is the regex to extract "xxx yes" => "^\S{1,} yes$")

In fact, adding this :

| mvexpand MergedColumns
| regex MergedColumns="^\S{1,} success"
| table MergedColumns source clientip

This above seems to split my values correctly, however, it removes all the remaining "xxx no" values.

Does anyone have a solution ?

Kind regards,

[ITWhisperer]

Curious to know how tightly your hand is tied behind your back! Why are you obliged to use transaction and not stats?

[ERFFFFF]

because i track which user is trying to connect (starting with the event "yesorno=yes" and terminate with the "event=connected") (if i'am in the wrong, please correct me, i'am still learning)

[somesoni2 · Accepted solution]

Give this a try

sourcetype="xxxx"
| transaction clientip source id maxspan=5m startswith="yesorno=yes" 
endswith="event=connected" keepevicted=true mvlist=true,responsetime,status,yesorno,clientip,event,_time
| sort _time
| eval MergedColumns=responsetime . " " . yesorno
| stats list(event) as event, list(MergedColumns) as MergedColumns, 
list(responsetime) as responsetime by yesorno, clientip, id
| where !(event=="connected")
| table MergedColumns source clientip
| mvexpand MergedColumns
| eval temp=if(match(MergedColumns,".+yes$"),1,0)
| accum temp
| stats list(MergedColumns) as MergedColumns by source clientip temp | fields - temp

[ERFFFFF]

Thank you very much, it works like a charm ! 😄