Filter out rows in splunk in a table after a stats command

[cindygibbs_08]

Hello everyone I hope you guys are doing well

I have a sort of simple question but I have not been able to sort a solution.. I want to filter out rows of a table where there are multivalues based on a numeric criteria, this is an example:

I have this:

AGENT	INX	ROCKS	TASK
XX_9	7 9 -6	T Y U	TY-8 GY-0 FG-67
XX_10	7 -49 -66	UY IO UJI	TY-8E G-0 VG-67

I would like to only remove all rows in the table where  the multivalue field "INX" have negative numbers and have something like this:

AGENT	INX	ROCKS	TASK
XX_9	7 9	T Y	TY-8 GY-0
XX_10	7	UY	TY-8E

I have tried using mvfilter and mvfind and mvindex but... every trial has not been successful yet so I really love you guys for helping me out thanks a LOTTTT

kindly,

Cindy

[bowesmana]

@cindygibbs_08 

Here is another option that uses foreach

| makeresults
| eval _raw="XX_9:7,9,-6:T,Y,U:TY-8,GY-0,FG-67;XX_10:7,-49,-66:UY,IO,UJI:TY-8E,G-0,VG-67"
| eval rows=split(_raw,";")
| mvexpand rows
| rex field=rows "(?<AGENT>[^:]*):(?<INX>[^:]*):(?<ROCKS>[^:]*):(?<TASK>.*)"
| eval INX=split(INX, ","), ROCKS=split(ROCKS, ","), TASK=split(TASK, ",")
| table AGENT INX ROCKS TASK
| foreach 0 1 2 3 4 5 6 7 [ eval num=mvindex(INX, <<FIELD>>), r=mvindex(ROCKS, <<FIELD>>), t=mvindex(TASK, <<FIELD>>), v_INX=mvappend(if(num>=0, num, null()), v_INX), v_ROCKS=mvappend(if(num>=0, r, null()), v_ROCKS), v_TASK=mvappend(if(num>=0, t, null()), v_TASK) ]
| rename v_* as *

Note that foreach 0 1 2 3 4 5 6 7 - will allow for 8 values in your list - if you have a potentially large number of values this would need to be expanded to include other numbers.

The foreach statement is simply testing num (i.e. the INX value) for >=0 and then appending the ROCKS and TASK values from the same corresponding multi-value index offset onto the collected results (v_*). After that it just renames the collected results to the original fields.

Note I have not checked if all fields are negative - what happens.

[bowesmana · Accepted solution]

Here is one simple way you can do it (full example search)

| makeresults
| eval _raw="XX_9:7,9,-6:T,Y,U:TY-8,GY-0,FG-67;XX_10:7,-49,-66:UY,IO,UJI:TY-8E,G-0,VG-67"
| eval rows=split(_raw,";")
| mvexpand rows
| rex field=rows "(?<AGENT>[^:]*):(?<INX>[^:]*):(?<ROCKS>[^:]*):(?<TASK>.*)"
| eval INX=split(INX, ","), ROCKS=split(ROCKS, ","), TASK=split(TASK, ",")
| table AGENT INX ROCKS TASK
| eval x=mvzip(INX, mvzip(ROCKS, TASK, ":"), ":")
| eval x=mvfilter(!match(x, "^-"))
| table AGENT x
| rex field=x max_match=0 "(?<INX>[^:]*):(?<ROCKS>[^:]*):(?<TASK>.*)"
| fields - x

It's using mvzip to zip up the 3 fields and then filter out only those which do NOT have a - sign at the start, then extracting the fields out again.

It can possibly be done using Splunk 8 mvmap and I can think of a couple of other possibilities, but try this and see if it works for you.

[cindygibbs_08]

Hello @bowesmana Thank you for your reply I trully apreaciate it! iT DOES WORK in some examaples but for some reason this gives me some funny results because I do have a lot of data plus I dont know if mvmap or mvzip will truncate data after 10k statistics... I would LOVE if you could some me the way using mvmap-- thanks a TRILLION

[bowesmana]

Can you give an example of a result row where it fails?