Query should return last/latest available data when there is no data for the selected time range

[saichandjawari]

Query should return last/latest available data when there is no data for the selected time range

[ITWhisperer]

Essentially, data is returned from the selected time range, if there is no data, what time range do you want to use?

You could do something like this

<your search>
| appendpipe
  [| stats count as _count
   | where _count = 0
   | where isnull(_count)
   | append
     [| search <your index> [| metasearch index=<your index> earliest=0
                             | head 1
                             | rename _time as earliest
                             | fields earliest]
      ]
   ]

[saichandjawari]

The query is used in a dashboard panel as a statistical table with single row.  the data is usually  not available on regular intervals. Hence we would like to show the last available data instead of “no results found” when there is no data for the selected default time range that we have set.

[ITWhisperer]

OK, so have you tried what I suggested?

[saichandjawari]

Thanks for your response.

[saichandjawari]

Yes, but that isn’t working. 

So here is a solution that I came up with — 

Step 1 - first write your results to a lookup file. 

<your query> |outputlookup yourlookup.csv

Step 2 - use that lookup in the query as shown below:

<your query> |append [|inputlookup yourlookup.csv 

|outputlookup yourlookup.csv override_if_empty = false create_empty = false]

the above query writes the results and stores in yourlookup.csv with wider time range. And  we are rewriting the stored results to the same lookupfile. In the last line override and Create_empty commands will make sure it will not give empty results. 
Note: use |dedup in the last if you see any duplicate results.

step 3- Create a saved search with this query and schedule it according to your requirement.