Splunk Search

How do I use a default value if latest(_time) cannot be found?

packland
Path Finder

Hi,

I'm trying to create a search that calculates how long a device has been offline, with a maximum of two days. However in its current form the search will only show sites if they have been online at some point in the last 2 days.

index=<my_index> earliest=-2d primaryStatus=Active OR secondaryStatus=Active
[ search index=<my_index> earliest=-15m primaryStatus=Failed secondaryStatus=Failed
| fields deviceID
| format ]
| stats latest(_time) as lastContact by deviceID

I have 3 devices that have never been online and I would like them to show up in the results with a default value for how long they've been offline. i.e. if any device has been offline for more than 2 days the downtime column will display "2+ Days" or if it's been offline for less than 2 days it should display that in 24 hour format: "1+12:30:00" (this part I already have working, it's just the first part that I can't figure out).

I understand the first line will completely exclude these devices from the results because there will be no events where either of the conditions ( primaryStatus=Active OR secondaryStatus=Active ) are true. But this is how I got it working for sites that have been online at some point during the selected time period.

in the simplest terms I can come up with this is what I need to do:

find the latest(_time) as lastContact
by deviceID where either of its
interfaces are "Active" and if there
are no such events set the lastContact
to a default value.

0 Karma

HiroshiSatoh
Champion

For example, there is a way to use LOOKUP.
※Please set all device ID in the LOOKUP file.

index=<my_index> earliest=-2d primaryStatus=Active OR secondaryStatus=Active
 [ search index=<my_index> earliest=-15m primaryStatus=Failed secondaryStatus=Failed
 | fields deviceID
 | format ]
 | append [search inputlookup deviceID_list.csv|table deviceID]
 | stats latest(_time) as lastContact by deviceID
 | fillnull value="2+days" Average
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...