Splunk Search

Is there a way to remove extra separator(s) when using the strcat command?

scottrunyon
Contributor

I have a search that is using the strcat command to string together text fields. My data looks something like this

Name Marbles Hopscotch Jacks Baseball
Mary Marbles Jacks Baseball
John Hopscotch Baseball
Fred Marbles Jacks

Wilma Hopscotch Jacks

My search contains
| eval Marbles = if(isnull(Marbles),"", "".Marbles )
| eval Hopscotch = if(isnull(Hopscotch),"", "|".Hopscotch )
| eval Jacks = if(isnull(Jacks),"", "|".Jacks )
| eval Baseball = if(isnull(Baseball),"", "|".Baseball )
|strcat Marbles Hopscotch Jacks Baseball Games

And the output look like

Name Games
Mary Marbles||Jacks|Baseball
John |Hopscotch||Baseball
Fred Marbles||Jacks|
Wilma |Hopscotch|Jacks|

Is there a way to remove the separators that are after a blank entry so my output look like -

Name Games
Mary Marbles|Jacks|Baseball
John Hopscotch|Baseball
Fred Marbles|Jacks
Wilma Hopscotch|Jacks

Any help is greatly appreciated

Scott

Labels (2)
0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

|makeresults
| eval raw="Mary Marbles Jacks Baseball:John Hopscotch Baseball:Fred Marbles Jacks:Wilma Hopscotch Jacks"
| makemv raw delim=":"
| mvexpand raw
| rename raw AS _raw
| rex "^(?<name>\S+)\s*(?<Marbles>Marbles)?\s*(?<Hopscotch>Hopscotch)?\s*(?<Jacks>Jacks)?\s*(?<Baseball>Baseball)?"
| table name Marbles Hopscotch Jacks Baseball

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval Games=mvappend(Marbles, Hopscotch, Jacks, Baseball)
| nomv Games
| rex field=Games mode=sed "s/[\r\n]+/|/g"

View solution in original post

woodcock
Esteemed Legend

Like this:

|makeresults
| eval raw="Mary Marbles Jacks Baseball:John Hopscotch Baseball:Fred Marbles Jacks:Wilma Hopscotch Jacks"
| makemv raw delim=":"
| mvexpand raw
| rename raw AS _raw
| rex "^(?<name>\S+)\s*(?<Marbles>Marbles)?\s*(?<Hopscotch>Hopscotch)?\s*(?<Jacks>Jacks)?\s*(?<Baseball>Baseball)?"
| table name Marbles Hopscotch Jacks Baseball

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval Games=mvappend(Marbles, Hopscotch, Jacks, Baseball)
| nomv Games
| rex field=Games mode=sed "s/[\r\n]+/|/g"

scottrunyon
Contributor

Woodcock,

I added an eval ltrim and rtrim because the rex added the "|" to the end of the line and if the "Marbles" field was empty, a "|" was the first character.

Thank you for the answer.

Scott

0 Karma

tomasmoser
Contributor

I liked that one with ltrim !!! Saved me 🙂

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...