I have a dbinput configured to pull data from SQL table on a daily basis. So I am getting few events each day in a index called A. The count of these index varies. Now on a dashboard, I want to display recent set of events from index A without asking user to select any time range.
It may happen that due to some reason dbinput may not work for few days but still I need to get the latest available events from a index. Note that on a daily basis there will be multiple events coming from dbinput.
Try like this
index=A sourcetype=YourSourcetypeHere [| tstats max(_time) as lastTime WHERE index=A sourcetype=YourSourcetypeHere earliest=-30d | eval earliest=relative_time(lastTime,"@d") | eval latest=relative_time(lastTime,"@d+1d") | table earliest latest | format]
|..rest of your search
The subsearch looks back 30 days worth of data and gets the most recent _time value. Then it generate the timerange for the main search with earliest being start of the day of the most recent _time value it found and latest being the end of the day of the most recent _time value. E.g. if last data was received on 04/15/2018 18:00, the earliest would be 04/15/2018 00:00 04/16/2018 00:00 so that all events from that day are shown in the main search.
Theoratically the logic seems correct. But I am getting below error with this
Error in 'search' command: Unable to parse the search: 'AND' operator is missing a clause on the left hand side.
If I just run the subquery I am getting below results. Search field has some data
earliest latest search
( ( earliest="1524528000.000000" AND latest="1524614400.000000" ) )
Another issue is somehow tstats max(_time) is giving todays date but there is no data in the index for last 2 days. Not sure why it is showing today's date for max event.
Can you try:
| metadata type=sourcetypes index=A | rename totalCount as Count firstTime as "First Event" lastTime as "Last Event" recentTime as "Last Update" | fieldformat Count=tostring(Count, "commas") | fieldformat "First Event"=strftime('First Event', "%c") | fieldformat "Last Event"=strftime('Last Event', "%c") | fieldformat "Last Update"=strftime('Last Update', "%c")
@p_gurav showed oyu a nice way to capture the data about the last event and when it took place.
if you want to see the event itself, try the |head
command and set the time to always capture something
so assuming you set the time picker to last 72 hours and you are searching ...index=A ... | head 100
will give you the first 100 results (or the last 100 events indexed) from that search