topic Need to get latest sets of events in Splunk Search

Need to get latest sets of events

santosh_sshanbh — Mon, 23 Apr 2018 14:31:35 GMT

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.

Re: Need to get latest sets of events

p_gurav — Mon, 23 Apr 2018 16:52:55 GMT

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")

Re: Need to get latest sets of events

adonio — Mon, 23 Apr 2018 17:53:40 GMT

@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

Re: Need to get latest sets of events

somesoni2 — Mon, 23 Apr 2018 18:23:37 GMT

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.

Re: Need to get latest sets of events

santosh_sshanbh — Tue, 24 Apr 2018 06:42:54 GMT

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.