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