Splunk Search

Data Model: TStats time-functions get latest NOT NULL

hbrandt84
Path Finder

Hi,

I'm having trouble retrieving my fields from an accelerated data model.
The main problem is that most of the fields are optional...
lets say the corresponding sourcetype looks like this:

  • asset_type (required)
  • asset_id (required)
  • sensor_01 (optional)
  • sensor_02 (optional)

then two events could look like this:

asset_type / asset_id / sensor_01 / sensor_02
"123" / "007" / "12.75" / ""
"123" / "007" / "" / "1265.99"

Now, when i search via the tstats command like this:

| tstats summariesonly=t
latest(dm_main.sensor_01)
latest(dm_main.sensor_02)
FROM
datamodel=dm_main
by
dm_main.asset_type 
dm_main.asset_id
| rename dm_main.* AS *

I only get either a value for sensor_01 OR sensor_02, since the latest value for the other is a blank...
I tried reaching around that by using list(), but its not available for tstats.
Then I tried using values(), which gives me the values I need, but in alphabetically order. But I need to know the lastest.

Is there a way with tstats to search for LATEST NOT NULL?

additional info:

the fields in Question are not native to the sourcetype, they are calculated fields:

EVAL-sensor_01 = if(valueName="raw_sensor_01", value, "")
EVAL-sensor_02 = if(valueName="raw_sensor_02", value, "")
0 Karma

hbrandt84
Path Finder

Okay, It seems like I need to change:
EVAL-sensor_01 = if(valueName="raw_sensor_01", value, "")

to:
EVAL-sensor_01 = if(valueName="raw_sensor_01", value, NULL)

0 Karma

dmarling
Builder

You are on the correct path, you should avoid using empty sets in any fields unless some very specific use cases. Your evals should be this EVAL-sensor_01 = if(valueName="raw_sensor_01", value, null()) The null() command makes it a null value instead of an empty set.

If this comment/answer was helpful, please up vote it. Thank you.
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 ...