Splunk Search

How to split single multivalue event into multiple multivalue events?

ERFFFFF
Explorer

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,

Labels (5)
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

ERFFFFF
Explorer

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)

0 Karma

somesoni2
Revered Legend

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
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...