Splunk Search

How to exclude field values from a column?

Communicator
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.

0 Karma

Ultra Champion

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

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

0 Karma

Communicator

o1N0FIQQx329x15798006x3 *37**
i1N0FIQQx329x15798006x3 *-**
o1N0FIQQx329x15798005x3 *5**
i1N0FIQQx329x15798005x3 *-**
o1N0FIQQx329x15798004x3 *58**
i1N0FIQQx329x15798004x3 *-**
o1N0FIQQx329x15798002x5 *281**
o1N0FIQQx329x15798003x4 *8**

Above is how the output looks like.
the first column is 'requestid' 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") datewday!=saturday AND datewday!=sunday
| eval headers=split(raw,"|")
| eval request
id=mvindex(headers,2)
| eval rtim=mvindex(headers,11)
| eval reqtimeseconds=rtim*0.001
| timechart span=1d eval(round(avg(reqtimeseconds),2)) as AverageResponseTime

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

0 Karma

Ultra Champion

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

0 Karma

Communicator

Yes, I am.
The 'rtim' field contains "-" if reqeustid starts with i*
I want to calculate average (i.e. | timechart span=1d eval(round(avg(rtim),2)) as Average
ResponseTime) 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*.

0 Karma

Ultra Champion

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

0 Karma

Communicator

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

host=A OR B or C
| eval headers=split(raw,"|")
| eval request
id=mvindex(headers,2)
| eval rtim=mvindex(headers,11)
| eval reqtimeseconds=rtim*0.001
| timechart span=1d eval(round(avg(reqtimeseconds),2)) as AverageResponseTime

0 Karma

Ultra Champion

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
0 Karma

Communicator

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

0 Karma

Ultra Champion

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.

0 Karma

Communicator

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

0 Karma

Ultra Champion

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

0 Karma