Splunk Search

How to exclude field values from a column?

zacksoft
Contributor
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

FrankVl
Ultra Champion

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*".

0 Karma

zacksoft
Contributor

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.

0 Karma

FrankVl
Ultra Champion

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

0 Karma

zacksoft
Contributor

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

0 Karma

FrankVl
Ultra Champion

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

0 Karma

zacksoft
Contributor

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 req_time_seconds=rtim*0.001
| timechart span=1d eval(round(avg(req_time_seconds),2)) as Average_Response_Time

0 Karma

FrankVl
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

zacksoft
Contributor

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

0 Karma

FrankVl
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

zacksoft
Contributor

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

0 Karma

FrankVl
Ultra Champion

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...