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

[singhbc]

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

[sideview · Accepted solution]

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]

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

[stephane_cyrill]

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.

[martin_mueller]

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

[sideview]

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.

[singhbc]

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

[singhbc]

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.