Getting Data In

[ Changing the direction of a splunk query]

asarolkar
Builder

We ran into a somewhat strange issue recently:

We have a complex search that needs to be run in the opposite order - earliest to newest - splunk always works the opposite way (it goes newest to oldest)

Is there a way to coerce splunk to reverse the direction of its searches ?

The nature of the search is such that it does not work the way splunk currently operates.

Let me give you some specifics - this is my search :

 SOURCETYPE="somelog" | eval Upgraded=if("B"=  SERIAL_NO , 1 , 0) | table Upgraded

From last to newest, SERIAL_NO has two values , B and A.

We only want it to look at the latest value - which is A - and then contend that this is all thats needed

Tags (2)
0 Karma

doksu
SplunkTrust
SplunkTrust

To search Splunk events in reverse order, you can use tstats on either indexed fields (i.e. index, host, source, sourcetype, or any other fields you choose to index) or a data model (which I'd suggest should be accelerated). The preferred approach is to use a data model, and add your "upgraded" field to the data model itself as an eval. In this way, when you search against the data model, it both searches in reverse and automatically calculates the value of "upgraded".

e.g.: | tstats values(upgraded) FROM datamodel=upgrade BY _time, host

0 Karma

woodcock
Esteemed Legend

You are not explaining your situation clearly enough for me to give you a complete answer but I am pretty confident that I understand it well enough to give you a rough answer that you can use to formulate a specific answer on your. You need to figure out which of 2 stats/eventstats command pairs are appropriate for you. You will either be using first/last or earliest/latest. There 1 HUGE difference between the pairs that is not made clear in the documentation:

The "first/last" pair operates on events AS THEY COME TO IT THROUGH THE PIPELINE and the order of events through this pipeline can be easily modified (e.g. "| sort 0 - _time" vs. "sort 0 _time"). In contrast, the "earliest/latest" pair ALWAYS operates based on each event's "_time" value which cannot (or perhaps I should say SHOULD not) be modified in the pipeline.

I also suspect that you should probably be using "eventstats" (or possibly "streamstats") instead of "stats" to augment the raw events with a statistical field (value) that you can then pipe to another "stats" command using one of the pairs I just explained.

0 Karma

asarolkar
Builder

In my followup post below are details of my situation

0 Karma

araitz
Splunk Employee
Splunk Employee

This might work, but you haven't really been specific enough for me to know for sure:

your complex search | sort -0 _time 

EDIT:

Your search is still quite abstract and obscure. Is SERIAL_NUMBER a static value? Where does it come from? What do the events look like?

Assuming something like this:

03/29/12 16:13:40 my-host serial_number=123456
03/29/12 16:12:40 my-host serial_number=123454

Then:

SOURCETYPE="somelog" | stats last(serial_number) as serial_number
0 Karma

araitz
Splunk Employee
Splunk Employee

What are you looping? You can use | stats first() to get the most recent value and | stats last() to get the oldest value, then do your comparison.

0 Karma

asarolkar
Builder

For SERIAL_NO:

The LAST value is A.
The LATEST (most recent) value is B.

The problem here is that SERIAL_NO has two values over time. Splunk goes LATEST TO LAST

So, it goes through this stupid loop in this order

if("B"= B ,1,0)

if("B"= A ,1,0)

IF Splunk went LAST TO LATEST - it would go in the opposite order - which is what we want it to do

0 Karma

araitz
Splunk Employee
Splunk Employee

see my response above. The stats operator's last() will produce the earliest value of serial_number.

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

you said you only care for the "latest" value, i.e. "most recent". Is that what you meant? Because if so, Splunk works exactly that way, i.e., it returns the most recent. I honestly don't understand the search you posted.

0 Karma

asarolkar
Builder

SERIAL_NO is a value read from indexed data.

SOURCETYPE="somelog" | eval Upgraded=if("B"= SERIAL_NO , 1 , 0) | table Upgraded

Over time SERIAL_NO has two values.

I only want it to care for the latest value.

There's another value that comes up EARLIER in time. Because splunk indexes latest to EARLIEST - it will go into this loop twice. The second time it finds that value, it goes THROUGH this loop and counts that "false positive"

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

No, Splunk does not work this way currently. You can do something like "... | stats earliest(indexvaue)" but that is a rather inefficient search since it does go from most recent to least recent.

Drainy
Champion
0 Karma

asarolkar
Builder

In my followup post below are details of my situation - that did not work

0 Karma

asarolkar
Builder

Reverse does not work

0 Karma

asarolkar
Builder

We are running a search that looks for an index (a serial number) - and we are only interested in getting the first instance of it - and then IGNORING all the instances that follow.

0 Karma

sowings
Splunk Employee
Splunk Employee

Have you tried reverse? What do you mean by "doesn't work"? I assume you have events which relate when read in time order... you could consider stats first(...) or last(...), or transaction to group them. Without more information, it's difficult to offer an answer.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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