Splunk Search

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

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

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

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

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

Contributor

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

0 Karma