Splunk Search

How to delete a Value from a MultiValue field based on an eval calculation?

singhbc
Path Finder

I have a few multivalue fields which I created using stats list(A) as A_list, list(B) as B_list, list(_time) as time_list

A_list has some values that are repeated over time, and I would like to delete a value based on an eval calculation which would look like:

| eval x=mvfind(A_list, "alpha") | eval y=(A_list, "beta") | eval time_alpha=mvindex(time_list, x) | eval time_beta=mvindex(time_list, y) | here i need to delete the "alpha" and "time_alpha" in their respective multivalue fields if (time_alpha > time_beta)

Thanks

1 Solution

sideview
SplunkTrust
SplunkTrust

It sounds like you want to filter out any A="alpha" values that happen to come after the first A="beta" value.

I strongly suspect that you're going into "multivalue land" a bit too early, so in this answer I'm going to stay out in the plain old rows and do the filtering there where it's easier.

Wind back what you have, and remove the stats list(A) as A_list, list(B) as B_list, list(_time) as time_list part.

Instead add this

`| eval isBeta=if(A=="beta",1,0) | streamstats sum(isBeta) as betaCount | where A!="alpha" OR betaCount==0 | fields - isBeta betaCount`

Onto the end of that you can add back the | stats list(A) as A_list, list(B) as B_list, list(_time) as time_list if you want, and the unwanted alpha rows will have been removed.

You can peel back the piped commands one by one to see how they work.

OR, you can also study this completely fabricated resultset here. Paste the following search verbatim into your Splunk search bar and you'll get a result set of 8 rows, where the 7th row turns out to be an "alpha" that we want to filter out.

| stats count | fields - count | eval A=split("alpha,alpha,beta,c,d,e,alpha,f",",") | mvexpand A

And applying the same solution to the end of our "fake" search language gives:

| stats count | fields - count | eval A=split("alpha,alpha,beta,c,d,e,alpha,f",",") | mvexpand A | eval isBeta=if(A=="beta",1,0) | streamstats sum(isBeta) as betaCount | where A!="alpha" OR betaCount==0 | fields - isBeta betaCount

where as you can see the alpha is gone.

View solution in original post

sideview
SplunkTrust
SplunkTrust

It sounds like you want to filter out any A="alpha" values that happen to come after the first A="beta" value.

I strongly suspect that you're going into "multivalue land" a bit too early, so in this answer I'm going to stay out in the plain old rows and do the filtering there where it's easier.

Wind back what you have, and remove the stats list(A) as A_list, list(B) as B_list, list(_time) as time_list part.

Instead add this

`| eval isBeta=if(A=="beta",1,0) | streamstats sum(isBeta) as betaCount | where A!="alpha" OR betaCount==0 | fields - isBeta betaCount`

Onto the end of that you can add back the | stats list(A) as A_list, list(B) as B_list, list(_time) as time_list if you want, and the unwanted alpha rows will have been removed.

You can peel back the piped commands one by one to see how they work.

OR, you can also study this completely fabricated resultset here. Paste the following search verbatim into your Splunk search bar and you'll get a result set of 8 rows, where the 7th row turns out to be an "alpha" that we want to filter out.

| stats count | fields - count | eval A=split("alpha,alpha,beta,c,d,e,alpha,f",",") | mvexpand A

And applying the same solution to the end of our "fake" search language gives:

| stats count | fields - count | eval A=split("alpha,alpha,beta,c,d,e,alpha,f",",") | mvexpand A | eval isBeta=if(A=="beta",1,0) | streamstats sum(isBeta) as betaCount | where A!="alpha" OR betaCount==0 | fields - isBeta betaCount

where as you can see the alpha is gone.

singhbc
Path Finder

Thank You, thats a great insight into solving this problem.

0 Karma

stephane_cyrill
Builder

Hi singhbc,
I'm not very sure but this can show you a way:

    .....|eval x=mvfind(A_list, "alpha")|eval delete=if (time_alpha > time_beta,  [ ....| rex field=A_list mode=sed "s/alpha /  /g"|where mvfind(A_list, "alpha")==x|return $x  ],  "no del")|.....

Here the idea is to replace what you want to delete space carracter.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

I think some sample events including desired output may be helpful as well.

0 Karma

sideview
SplunkTrust
SplunkTrust

questions. Do all events contain a value for both A and B? Are there any events which do not have either A or B but only time? If the only events coming in have both A and B, it's probably easier to filter out the specific unwanted events with various streamstats/eventstats/eval tricks before they get rolled into your multivalue fields.

0 Karma

singhbc
Path Finder

can you please take another look now?
I will really appreciate it.

0 Karma

singhbc
Path Finder

Do all events contain a value for both A and B? The answer is YES
Are there any events which do not have either A or B but only time? The answer is NO, A and B are both present.
Please suggest the streamstats/eventstats/eval tricks.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...