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

packland — Tue, 20 Feb 2018 01:31:59 GMT

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.

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

HiroshiSatoh — Tue, 20 Feb 2018 04:43:21 GMT

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

topic Re: How do I use a default value if latest(_time) cannot be found? in Splunk Search

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

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