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!

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...