topic Re: How to exclude field values from a column? in Splunk Search

How to exclude field values from a column?

zacksoft — Tue, 05 Jun 2018 08:59:21 GMT

sourcetype="rocket:access" (host="rocket0.painpoint.com" OR host="rocket5.painpoint.com") date_wday!=saturday AND date_wday!=sunday
| eval headers=split(_raw,"|")
| eval request_id=mvindex(headers,2)
| eval rtim=mvindex(headers,11)
| table request_id,rtim

request_id contains fields such as,
o*1N0FIQQx292x15786665x0,
i*1N0FIQQx292x15786665x0
o*1N0FIQQx292x15786664x0
i*1N0FIQQx292x15786664x0

I want to exclude the field values that starts with i and their corresponding rtim value as well.

Re: How to exclude field values from a column?

FrankVl — Tue, 29 Sep 2020 19:46:13 GMT

Wouldn't a simple | where request_id != "i*" suffice? Or if, the request_id values that you want to keep always start with o*, make it a positive filter | where request_id = "o*".

Edit: as mentioned in comments below, where command does not support wildcards like that. Should use | search request_id!="i*".

Re: How to exclude field values from a column?

zacksoft — Tue, 05 Jun 2018 09:18:26 GMT

I tried adding that | where condition at the end , But after adding the search won't give me any output.

Re: How to exclude field values from a column?

FrankVl — Tue, 05 Jun 2018 09:21:50 GMT

Can you then please share a screenshot of what the output looks like after running the search you mention in your question?

Edit: Oh and I see I made a typo in my suggestion (fixed that now), misspelled the request_id field name. If you copy pasted that, maybe that was the simple reason you didn't get results.

Re: How to exclude field values from a column?

zacksoft — Tue, 05 Jun 2018 09:25:12 GMT

I just added | search request_id!="i*"
It solved my problem.

Re: How to exclude field values from a column?

zacksoft — Tue, 29 Sep 2020 19:50:14 GMT

o*1N0FIQQx329x15798006x3 37
i*1N0FIQQx329x15798006x3 -
o*1N0FIQQx329x15798005x3 5
i*1N0FIQQx329x15798005x3 -
o*1N0FIQQx329x15798004x3 58
i*1N0FIQQx329x15798004x3 -
o*1N0FIQQx329x15798002x5 281
o*1N0FIQQx329x15798003x4 8

Above is how the output looks like.
the first column is 'request_id' and second one(mentioned in bold) is 'rtim'.
I want to remove the request_id starting with i* , because the corresponding rtim value is ' -' if reqeust_id is i*
After removing rtim with value '-' I want to be able to find the average of the column.
something like this

sourcetype="rocket:access" (host="rocket0.painpoint.com" OR host="rocket5.painpoint.com") date_wday!=saturday AND date_wday!=sunday
| eval headers=split(_raw,"|")
| eval request_id=mvindex(headers,2)
| eval rtim=mvindex(headers,11)
| eval req_time_seconds=rtim*0.001
| timechart span=1d eval(round(avg(req_time_seconds),2)) as Average_Response_Time

Could you help here ?
Because of the presence of '-' in the rtim , the timechart command won't give any result.

Re: How to exclude field values from a column?

FrankVl — Tue, 05 Jun 2018 09:40:15 GMT

My bad, where doesn't accept wildcard strings. The search command is indeed the way to go.

Re: How to exclude field values from a column?

FrankVl — Tue, 05 Jun 2018 09:41:52 GMT

Is this now solved, based on your other comment, or are you still running into some issue?

Re: How to exclude field values from a column?

zacksoft — Tue, 29 Sep 2020 19:50:17 GMT

Yes, I am.
The 'rtim' field contains "-" if reqeust_id starts with i*
I want to calculate average (i.e. | timechart span=1d eval(round(avg(rtim),2)) as Average_Response_Time) of rtim, But I am not able to do it as some of its values contain '-'.
That's why it is imperative to remove the events with request_id value with i*.

Re: How to exclude field values from a column?

FrankVl — Tue, 05 Jun 2018 10:23:03 GMT

But you succeeded at filtering those out using | search request_id!="i*" right?

Re: How to exclude field values from a column?

zacksoft — Tue, 29 Sep 2020 19:50:19 GMT

yes, |search request_id!="i*" works when I present the output with a table command.
But I intend to present the output in a timechart average format like below. And I am not sure where to apply the search fitering in that scenario

Re: How to exclude field values from a column?

FrankVl — Tue, 05 Jun 2018 10:37:35 GMT

Before the timechart command. Basically you can insert that piece as soon as that request_id field is set, so, this should work:

host=A OR B or C
| eval headers=split(_raw,"|")
| eval request_id=mvindex(headers,2)
| search request_id!="i*"
| eval rtim=mvindex(headers,11)
| eval req_time_seconds=rtim*0.001 
| timechart span=1d eval(round(avg(req_time_seconds),2)) as Average_Response_Time