topic Re: Where do I put my fillnull value? in Splunk Search

Where do I put my fillnull value?

ebs — Tue, 01 Jun 2021 23:17:57 GMT

This is my base search:

Where do I put my fillnull value for averageTps? With the multiple stats values, I'm unsure and everytime I've tried putting it in I get no results whereas I want the averageTps field to be 0

Re: Where do I put my fillnull value?

bowesmana — Wed, 02 Jun 2021 01:05:40 GMT

@ebs

Some questions and observations:

In your example, date has a single value, 2021-05-23, so the 'by date' part of "| stats avg(avg_TPS) as averageTps by date" is redundant as there will be a single result
You are doing count("TEST.exchange_id") by _time
- This will just return a count of events that contain that field
  - if you know that all events contain that field, the just count by _time is sufficient
Try to use the rename part of a stats aggregation - it makes your SPL easier to read, e.g.
- | eventstats count("TEST.exchangeId") as avg_TPS by _time
- so you can avoid having to do the subsequent rename
what is $date$ value - in Splunk, _time variable is an epoch time, not a formatted date string, so your $date$ token would have to be a numeric epoch
what is your expected results
- as single value showing average TPS per second for a single day
  - if so, what null values are you trying to fill?

Are you trying to include seconds where there are 0 TPS, in which case you should use timechart instead

Note also in your example, where you use eventstats followed by stats

Using eventstats count will end up giving you the wrong result compared to stats count, e.g. if your eventstats count data is

_time, TPS
2021-05-23 08:00:00 5
2021-05-23 08:00:00 5
2021-05-23 08:00:00 5
2021-05-23 08:00:00 5
2021-05-23 08:00:00 5
2021-05-23 08:00:01 2
2021-05-23 08:00:01 2

then stats avg(TPS) will give you 29/7 = 4.143, whereas if you do stats count your data will be

2021-05-23 08:00:00 5
2021-05-23 08:00:01 2

and the stats avg(TPS) will be 3.5

Hope this helps

Re: Where do I put my fillnull value?

ebs — Thu, 03 Jun 2021 00:35:50 GMT

Hi @bowesmana

You've helped me improve my search, however I still don't know where to place my fillnull value in case there are no transaction which was my initial question.

Re: Where do I put my fillnull value?

bowesmana — Thu, 03 Jun 2021 00:49:50 GMT

Can't really answer your fillnull question without more information. What null values are present in your data you want to fill?

From your original search there are no null values.

If my comment about using timechart vs stats did not answer your question about what _might_ be null values, perhaps you can say.

Re: Where do I put my fillnull value?

ebs — Thu, 03 Jun 2021 01:47:52 GMT

This specific line could result in a null value if there were no transactions today (unlikely but want it just in case)

Re: Where do I put my fillnull value?

yuanliu — Thu, 03 Jun 2021 07:46:52 GMT

Take a look at this old thread and @somesoni2's solutionhttps://community.splunk.com/t5/Deployment-Architecture/How-to-produce-empty-time-buckets/m-p/172328/highlight/true#M6442. The idea is to use a cheap subsearch to force missing buckets. There is also a recent comment saying that the fillnull command is not needed if timechart is used, although I haven't tested that. (I can no longer find my old use case.)

Re: Where do I put my fillnull value?

yuanliu — Thu, 03 Jun 2021 10:20:39 GMT

Just noticed that timechart seems to fill non-existent buckets with 0 for data like this:

| timechart span=1m count(exchangeId) as TPM

(If exchangeId is available in every event as the sample data presents, count alone should suffice.) Output for this is

Re: Where do I put my fillnull value?

ebs — Thu, 03 Jun 2021 23:25:14 GMT

I have tried that append pipe and it kind of works, but it changes the original value when the value is not 0 for example one value before the appendpipe was 85, after it was 140. How do I fix this?

Re: Where do I put my fillnull value?

yuanliu — Fri, 04 Jun 2021 04:00:15 GMT

@ebs wrote:
I have tried that append pipe and it kind of works, but it changes the original value when the value is not 0 for example one value before the appendpipe was 85, after it was 140. How do I fix this?

Without knowing what code you tried, and the data used in test, it is rather difficult to cut hair over the telephone.

Re: Where do I put my fillnull value?

bowesmana — Sun, 06 Jun 2021 23:13:23 GMT

@ebs

so what was wrong with my original suggestion to use timechart - that will give you 0 values for a period that does not have data.

Re: Where do I put my fillnull value?

ebs — Sun, 06 Jun 2021 23:38:40 GMT

I had to revert to my original search because timechart was getting overwhelmed. All echange_ids are only 1 value each so it works for the TPS search

_time	exchangeId
2016-08-30 05:27:01	60302
2016-08-30 05:28:03	60302
2016-08-30 05:28:04	60130
2016-08-30 05:28:05	60302
2016-08-30 05:28:08	60130
2016-08-30 05:29:18	60130
2016-08-30 05:29:19	60302
2016-08-30 05:29:31	60302
2016-08-30 05:31:09	60130
2016-08-30 05:31:09	60130
2016-08-30 05:31:21	60130
2016-08-30 05:31:46	60302
2016-08-30 05:31:52	60130
2016-08-30 05:32:35	60302
2016-08-30 05:32:55	60302
2016-08-30 05:36:14	60302
2016-08-30 05:36:50	60302
2016-08-30 05:37:12	60302
2016-08-30 05:37:25	60302
2016-08-30 05:37:41	60302

_time	TPM
2016-08-30 05:27:00	1
2016-08-30 05:28:00	4
2016-08-30 05:29:00	3
2016-08-30 05:30:00	0
2016-08-30 05:31:00	5
2016-08-30 05:32:00	2
2016-08-30 05:33:00	0
2016-08-30 05:34:00	0
2016-08-30 05:35:00	0
2016-08-30 05:36:00	2
2016-08-30 05:37:00	3