Splunk Search

Replace String Values

genesiusj
Builder

Hello,

I need to remove the values found (string) from another field.

Ex. 
FIELD1 - abcmailingxyz
LIST - mailing, ...
Using | eval foundIt=if(match(FIELD1,$LIST$),"X",".") I am able to determine if the list of words are contained in the values for FIELD1

After the eval has found the match, foundIt=X, I need to remove the word "mailing" from the value of FIELD1.
Result - abcxyz (or abc_xyz if we decide to use an underscore in between).

Question.
How do I take the value in LIST and remove it from the value in FIELD1, leaving the remaining letters behind?

Thanks in advance.
God bless, safe and healthy to you and yours,
Genesius

Labels (1)
0 Karma
1 Solution

bowesmana
Champion

I am not sure why you are surrounding LIST with $$. If you just use LIST then it is the field name LIST, whereas if you use quotes "LIST" then it is the string LIST. 

This will do what you want as long as you have Splunk 8

| makeresults
| fields - _time
| eval n=mvrange(0,3)
| mvexpand n
| eval FIELD1=mvindex(split("abcprocessor123,digitalxyzmonitor,fghcpuscreen1313drive", ","), n)
| eval LIST=split("mailing,other,network,splunk,processor,cpu,screen,drive,digital,monitor", ",")
| eval match=mvmap(LIST,if(match(FIELD1,LIST),LIST,null))
| eval match=coalesce(match, "<<No Match Found>>")
| eval matchStr=mvjoin(match,"|")
| eval FIELD1_REPLACED=replace(FIELD1,matchStr, "")
| eval replaceCount=mvcount(match)
| table FIELD1 FIELD1_REPLACED match replaceCount

Paste this into the search window and it will show you your results.

To make this work for you, you need to 

  • create the variable LIST which is the multi value field containing all of your exclusion words
  • use these lines that actually do the work for you
| eval match=mvmap(LIST,if(match(FIELD1,LIST),LIST,null))
| eval matchStr=mvjoin(match,"|")
| eval FIELD1_REPLACED=replace(FIELD1,matchStr, "")
| eval replaceCount=mvcount(match)

the removal is done by the replace statement using the regex of a|b|c where a, b and c are the words found in your email address from the list of words

replaceCount is just getting the number of matches

 

View solution in original post

0 Karma

genesiusj
Builder

Thanks @ITWhisperer  and @bowesmana 

Apologies.

The LIST is from a lookup table.

I will test out both of your answers and reply later today.

Thanks and God bless,
Genesius

0 Karma

genesiusj
Builder

@ITWhisperer  @bowesmana 

Pulled onto other priorities. Still checking your responses.

Meanwhile, bosemana.
 | eval foundIt=if(match(FIELD1,$LIST$),"X",".")
The $LIST$ is not a dashboard token. The match function uses regex syntax. Therefore, it is checking to see if the value contained in the LIST field matches FIELD1. Without the $$ I believe it would be checking to see if the word LIST was in FIELD1.

Apologies to both of you. I work with makeresults. However, when multivalue commands are used with makeresults I tend to misunderstand which mv commands are present because of makeresults, and which are part of the solution when there is a data source.

To further clarify.
FIELD1 is an email address, minus the @ and domain name.
LIST is a lookup table with over 100 computer-related terms.
We need to find email addresses that contain these computer-related terms; remove them when found; list the found terms; leave the remaining characters from the email address.

Example
FIELD1 email addresses
abcprocessor123
digitalxyzmonitor
fghcpuscreen1313drive

List of words (and count)
processor 1
digital monitor 2
cpu screen drive 3

Remaining characters (using ..,::,|| for the LIST words removed)
abc..123
..xyz::
fgh..::1313||

Thanks for your help. Enjoy your weekend.
God bless,
Genesius

0 Karma

bowesmana
Champion

I am not sure why you are surrounding LIST with $$. If you just use LIST then it is the field name LIST, whereas if you use quotes "LIST" then it is the string LIST. 

This will do what you want as long as you have Splunk 8

| makeresults
| fields - _time
| eval n=mvrange(0,3)
| mvexpand n
| eval FIELD1=mvindex(split("abcprocessor123,digitalxyzmonitor,fghcpuscreen1313drive", ","), n)
| eval LIST=split("mailing,other,network,splunk,processor,cpu,screen,drive,digital,monitor", ",")
| eval match=mvmap(LIST,if(match(FIELD1,LIST),LIST,null))
| eval match=coalesce(match, "<<No Match Found>>")
| eval matchStr=mvjoin(match,"|")
| eval FIELD1_REPLACED=replace(FIELD1,matchStr, "")
| eval replaceCount=mvcount(match)
| table FIELD1 FIELD1_REPLACED match replaceCount

Paste this into the search window and it will show you your results.

To make this work for you, you need to 

  • create the variable LIST which is the multi value field containing all of your exclusion words
  • use these lines that actually do the work for you
| eval match=mvmap(LIST,if(match(FIELD1,LIST),LIST,null))
| eval matchStr=mvjoin(match,"|")
| eval FIELD1_REPLACED=replace(FIELD1,matchStr, "")
| eval replaceCount=mvcount(match)

the removal is done by the replace statement using the regex of a|b|c where a, b and c are the words found in your email address from the list of words

replaceCount is just getting the number of matches

 

View solution in original post

0 Karma

genesiusj
Builder

@bowesmana 
Thank you for breaking the SPL needed into a separate section.
This worked like a charm.
God bless,
Genesius

0 Karma

genesiusj
Builder

Good morning,  @bowesmana 

According to the Splunk docs, the match function uses regex, which is why I am using the $LIST$.
https://docs.splunk.com/Documentation/Splunk/8.1.3/SearchReference/ConditionalFunctions

I found the article where I learned about the $LIST$.
https://tinyinput.blogspot.com/2017/02/splunk-do-you-like-or-do-you-match.html
I read through the article too quickly and missed that the author was in a section concerning dashboards. My bad. However, using $LIST$ does work. Interesting.

I have yet to try your latest update. I will hopefully advise shortly.

Thanks and God bless,
Genesius

0 Karma

ITWhisperer
Ultra Champion

Assuming your list can be made into a pipe-delimited string, this acts as an or in the regex used by replace, so you can replace any of the values in the list with an empty string

| makeresults
| eval _raw="field1,list
abcmailingdef,mailing|post
pqrpostxyz,mailing|post
defmailingpostrst,mailing|post
nothingmatch,mailing|post"
| multikv forceheader=1
| fields field1 list
| fields - _*

| eval field2=replace(field1,list,"")

Each event with field1 can have different values in their list if this is helpful too

0 Karma

bowesmana
Champion

I am not sure how your existing match is working if you have multiple values in the list - and with your surrounding LIST with $$ - is this in a dashboard and is that a token?

Anyway, if you are using Splunk 8, then you could do it this way

| makeresults
| fields - _time
| eval n=mvrange(1,11)
| mvexpand n
| eval FIELD1=mvindex(split("abcmailingxyz,test_splunkquery,otheroptiontext,errorinnetwork,nnnmailing2,nomatchesanywhere", ","), random() % 6)
| eval LIST=split("mailing,other,network,splunk", ",")
| eval match=mvmap(LIST,if(match(FIELD1,LIST),LIST,null))
| eval match=coalesce(match, "<<No Match Found>>")
| eval FIELD1_REPLACED=replace(FIELD1,match, "")
| table FIELD1 FIELD1_REPLACED match

where the key function is the MVMAP line and it is taking your list values (which is a multivalue field containing your match strings) and then the replace() function is removing the match found to create the new FIELD1_REPLACED

Hope this helps

 

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!