Splunk Search

Is it possible to ignore the last result?

efaundez
Path Finder

good afternoon

I have the following query

| dbxquery connection = connection
query = "....."

| chart eval (round (max
(AttachFailure2G), 2)) as
Attach_Failure_2G, eval (round (max
(AttachSuccess2G), 2)) as
Attach_Success_2G by Fecha_Hora

example:

1 - 2018-04-25 14: 45: 00.000 | 7.67 | 95.85 <- ignore
2 - 2018-04-25 14: 30: 00.000 | 23.80 | 79.19
3 - 2018-04-25 14: 15: 00.000 | 23.76 | 79.11
4 - 2018-04-25 14: 00: 00.000 | 23.73 | 79.17

But it is required to ignore the last event brought from the query, is this possible?

regards

Tags (1)
0 Karma
1 Solution

elliotproebstel
Champion

You can remove the event that's listed first by adding this to your search:

your base search
| streamstats count AS order_count
| where order_count>1 
| fields - order_count

This adds a field called order_count to each line, and the first line will always receive the value 1. So we filter to retain only events with order_count>1 and then remove the field because we no longer need it.

View solution in original post

TISKAR
Builder

Hello
May be this solution help you or another person, it's another proposition:

your base search
 | eventstats max(Fecha_Hora) as maxTime
 | where Fecha_Hora!=maxTime
 | fields - maxTime

The Fecha__Hora represent the time if not use _time field

efaundez
Path Finder

thanks, it works.

0 Karma

efaundez
Path Finder

thanks for the answer, I realized that I explained my concern wrongly because the last value corresponded to the first: P means

1 - 2018-04-25 14: 00: 00.000 | 23.73 | 79.17
2 - 2018-04-25 14: 15: 00.000 | 23.76 | 79.11
3 - 2018-04-25 14: 30: 00.000 | 23.80 | 79.19
4 - 2018-04-25 14: 45: 00.000 | 7.67 | 95.85 <- ignore

but how to validate that doing a | sort -Date_Hour and adding

| streamstats count AS order_count
  | where order_count> 1
  | fields - order_count

like

| streamstats count AS _serial
  | search _serial> 1
  | fields - _serial

ignores the last value or the most recent value

Thank you:

0 Karma

woodcock
Esteemed Legend

You can add this to your search:

| streamstats count AS _serial
| search _serial > 1
| fields - _serial

P.S. sometimes you get _serial for free so try it without the first streamstats line and see.

elliotproebstel
Champion

You can remove the event that's listed first by adding this to your search:

your base search
| streamstats count AS order_count
| where order_count>1 
| fields - order_count

This adds a field called order_count to each line, and the first line will always receive the value 1. So we filter to retain only events with order_count>1 and then remove the field because we no longer need it.

skoelpin
SplunkTrust
SplunkTrust

Try something like this.. I tested it against an internal index and it's working as expected, you can apply this against your internal index too and verify it works before applying it with your SPL

index=_internal   component=TailReader 
| eval latest_time=relative_time(now(),"-15m@m"), now=now()
| bin _time span=15m
| stats max(host) by _time, latest_time
| eval ignore_latest_time=if(_time<'latest_time',0,1)
| where ignore_latest_time<1

| eval latest_time=strftime(latest_time,"%H:%M:%S"), now=strftime(now,"%H:%M:%S")
  • We first create 2 evals to get the time 15 minutes ago and now
  • We use bin to make 15 minute buckets of time
  • We use a stats to transform our data into a table view
  • We use evalconditional logic to say if _time is less than our variable latest_time which was defined in step 1, then give this field a "0", else give it a "1". This will result in only 1 row having a value of 1 since we have 15 minute span bins
  • We than use where to count all values less than 1 which will remove the latest row
  • We add strftime to make the _time fields human readable
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...