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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...