topic Re: Replace String Values in Splunk Search

Replace String Values

genesiusj — Wed, 07 Apr 2021 21:02:10 GMT

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

Re: Replace String Values

bowesmana — Wed, 07 Apr 2021 22:24:47 GMT

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

Re: Replace String Values

ITWhisperer — Wed, 07 Apr 2021 22:37:30 GMT

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

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

Re: Replace String Values

genesiusj — Thu, 08 Apr 2021 12:14:16 GMT

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

Re: Replace String Values

genesiusj — Fri, 09 Apr 2021 19:51:09 GMT

@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

Re: Replace String Values

bowesmana — Sun, 11 Apr 2021 11:03:20 GMT

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

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

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

Re: Replace String Values

genesiusj — Mon, 12 Apr 2021 14:54:21 GMT

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

Re: Replace String Values

genesiusj — Mon, 12 Apr 2021 18:59:58 GMT

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