Splunk DB Connect 2: How to get the all the rows for a specific timestamp?

[sam_jacob]

I'm currently doing a DB Connect Dump every hour, and the query produces multiple rows. How do I display only those rows as a table?

To explain the question in more detail, here is an example. For example, lets say the time currently is 4:38pm, the database was queried at 4:00pm, and I want to display the only the results from that query at 4:00pm.

This is what the index could look like:

_time            | Count
8/11/2015 4:00pm | 1512456
8/11/2015 4:00pm | 1645241
8/11/2015 4:00pm | 5768575
8/11/2015 4:00pm | 2178565
8/11/2015 4:00pm | 5678688
8/11/2015 4:00pm | 8725768
8/11/2015 3:00pm | 4515351
8/11/2015 3:00pm | 6437567
8/11/2015 3:00pm | 1244795
8/11/2015 3:00pm | 2024553
8/11/2015 3:00pm | 8823452

So the Splunk search query should only return all results which occurred at 4:00pm.

After looking around, I thought maybe I can use the return function to get the first timestamp and use that to create a search query, but splunk didn't like that. Below is the query I just mentioned:

eval timestamp=[search index=[redacted] sourcetype=[redacted] | stats first(_time) as "time" | return time] | append [search index=[redacted] sourcetype=[redacted] _time=timestamp] | table [redacted]

[sam_jacob · Accepted solution]

I ended up using streamstats to find the last queried results, then use head to select only those results. Below is the search string I was looking for:

index=[redacted] sourcetype=[redacted] | streamstats dc(_time) as distinct_times | head (distinct_times == 1) | table [redacted]

[gcato]

Hi Sam,

You could use eval's relative_time() function to show only the result for the previous hour . For example,

 index=[redacted] sourcetype=[redacted] | eval search_time = relative_time(now(), "-1@h")  | where _time = search_time

Is this what you're looking for?

[gcato]

Alternatively, if the results are always on the hour (@h) like that then the simplest and most efficient search would simply be

 index=[redacted] sourcetype=[redacted] earliest=-1@h latest=-1@h | table ...

[sam_jacob]

Thanks for the help, but I ran into issues with both your solutions. In the first solution, it looked promising, but relative_time kept spitting out a weird time, specifically 1439388000.000000.

Then the second solution would have made sense if the queries actually ran on the dot, but sometimes, it'll run a little bit late or early, so some times there might be two different sets of results in an hour. And I feel like this could have been a problem also with the first solution.

But I think I figured it out, and I have posted it as an answer.