SQL samples in splunk

[kprinelle]

Just getting started with Splunk & after a little direction.

I have a SQL query that returns a list of requests that a database is handling and some info about those requests.
I output the data as rows of key=value pairs & that's appearing nicely in Splunk (thanks to the various posts about how best to do this).

The one thing I'm struggling with is being able to show only the last set of rows output by the monitor. I include in the output a timestamp which is the same for all rows on each sample interval. The problem is I currently get all rows from all samples, when what I need is all rows from the last sample only - these have the latest/max/last timestamp.

My time format is "2013-05-17 17:41:25", but if needed I could output epoch if that's easier.

I'm sure this is a simple thing, but just starting out so need some pointers about where to look/approach.

Thanks

---

Ok, got a little further;

I think I've got a little further with this now, but I'm still getting multiple samples in my data. I should have said that each sample contains a variable number of rows - say 20-30, and I only want a table containing the that last block of samples.

Anyway - this is what I have;

monitoringInstance=myInstanceName | eval timeEpoch=strptime(timestamp, "%F %H:%M:%S") | stats max(timeEpoch) as lastSample | where timeEpoch=lastSample | table timeEpoch lastSample timestamp requestColumn1 requestColumn2 requestColumn3 requestColumn4

lastSample has the epoch of my last sample time.
timeEpoch contains the conversion of timestamp as an epoch

I think it's the where clause that's letting me down.....or the fact it's wrong 😉

[dwaddle · Accepted solution]

One simple (but not necessarily high performance) approach is to use dedup. The dedup command will by default show only the newest (by time) event for a given set of fields. But, this might not be exactly the same as what you are looking for here.

But here is another approach that might do what you want:

monitoringInstance=myInstanceName 
| eventstats max(_time) as lastSample 
| where _time=lastSample 
| table _time requestColumn1 requestColumn2 requestColumn3 requestColumn4

The eventstats command works differently from the standard stats command in that instead of aggregating your results to a smaller set of rows, it appends new fields to your existing results -- thus making your where work better.

Also, I replaced your epochTime with _time which is a Splunk built-in variable based on its own parsing of the time of the event into epoch.

[kristian_kolb]

figuring that this might be faster, since you'll search over a smaller, yet undefined timespan. The subsearch grabs the time from the last event and passes it to the outer search as earliest:

monitoringInstance=myInstanceName
[search monitoringInstance=myInstanceName
| head 1
| eval earliest = _time
| fields + earliest
]
| table _time blah0 blah1 blah2

/k

[kprinelle]

Excellent!! That's just what I needed - works a treat.
Many thanks for that one.