Is it possible to ignore the last result?

[efaundez]

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

[TISKAR]

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]

thanks, it works.

[efaundez]

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:

[woodcock]

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 · Accepted solution]

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]

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