Where do I put my fillnull value?

[ebs]

This is my base search:

| datamodel Test summariesonly=true search
| search "TEST.date"=2021-05-23 | rename "TEST.date" as date
| bin _time span=1s
| eventstats count("TEST.exchangeId") by _time
| rename count(TEST.exchangeId) as avg_TPS
| stats avg(avg_TPS) as averageTps by date
| eval averageTps=round(averageTps,3)
| eval _time=$date$
| fields averageTps _time

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

[yuanliu]

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....  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.)

[ebs]

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?

[yuanliu]

---

@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.

[yuanliu]

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

_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

For illustration purposes, instead of calculating TPS, I'll calculate TPM.  Notice gaps between 05:29 and 05:31, then between 05:32 and 05:36.

To calculate TPM, simply do

| 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

_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

If this result is acceptable as TPM, your daily average can easily be calculated as

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

You can calculate TPS, of course, by using span=1s in the first timechart.

(I no longer have the data that triggered the "gap" behavior observed in that old post.  It could also be that in later SPL, timechart now automatically fills blank buckets.)

[bowesmana]

@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

| datamodel Test summariesonly=true search
| search "TEST.date"=2021-05-23
| rename "TEST.date" as date
| timechart span=1s count as TPS 
| stats avg(TPS) as averageTps 
| eval averageTps=round(averageTps,3)
| eval date=$date|s$
| fields averageTps date

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

[ebs]

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.

[bowesmana]

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.

[ebs]

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

[bowesmana]

@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.

[ebs]

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