topic Is it possible to ignore the last result? in Splunk Search

Is it possible to ignore the last result?

efaundez — Tue, 29 Sep 2020 19:14:48 GMT

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

Re: Is it possible to ignore the last result?

skoelpin — Tue, 29 Sep 2020 19:14:59 GMT

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

Re: Is it possible to ignore the last result?

elliotproebstel — Wed, 25 Apr 2018 20:02:22 GMT

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.

Re: Is it possible to ignore the last result?

woodcock — Wed, 25 Apr 2018 20:05:02 GMT

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.

Re: Is it possible to ignore the last result?

efaundez — Tue, 29 Sep 2020 19:15:04 GMT

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

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

ignores the last value or the most recent value

Thank you:

Re: Is it possible to ignore the last result?

TISKAR — Wed, 25 Apr 2018 21:53:11 GMT

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

Re: Is it possible to ignore the last result?

efaundez — Thu, 26 Apr 2018 12:45:38 GMT

thanks, it works.