Splunk Search

## Special fillnull? Polulate null values with evaluated calculations

Builder

This is my code

`````` index="google_apis" source="https://www.googleapis.com/youtube"
| timechart span=1h avg(subCount) by channel
``````

the results looks something like this

Now, my problem is I can't seem to find a way on how to fill the null values with this formula:

``````"average of the field" + ("stdev of the field" * random(-3, 3))
``````

My intention is to fill the null values with psuedo values that is 3 sigmas away (below or above) from the mean of the fields. I can get the avg and stdev by the `eventstats` command. I can also get random number between -3 to 3. But I can't put them to the right place.

Tags (3)
1 Solution
Builder

Below code is working and possibly the most efficient way of doing it. I'm accepting this as the correct answer but it was only because of @VatsalJagani 's answer that I was able to work it out.

``````index="google_apis" source="https://www.googleapis.com/youtube"
| timechart span=1h avg(subCount) by channel
| fields - NULL
| eventstats avg as a_*, stdev as s_*
| eval rand = tostring("0." . random())
| eval rand =
case(
(rand > 0.00 AND rand < 0.16), -3,
(rand > 0.16 AND rand < 0.32), -2,
(rand > 0.32 AND rand < 0.48), -1,
(rand > 0.48 AND rand < 0.64), 1,
(rand > 0.64 AND rand < 0.80), 2,
(rand > 0.80 AND rand < 1.00), 3,
true(), 1)
| foreach *
[ eval "<<FIELD>>" = floor(ifnull('<<FIELD>>', 'a_<<FIELD>>' + ('s_<<FIELD>>' * rand), '<<FIELD>>'))]
| fields - a_*, s_*, rand
``````
Builder

Below code is working and possibly the most efficient way of doing it. I'm accepting this as the correct answer but it was only because of @VatsalJagani 's answer that I was able to work it out.

``````index="google_apis" source="https://www.googleapis.com/youtube"
| timechart span=1h avg(subCount) by channel
| fields - NULL
| eventstats avg as a_*, stdev as s_*
| eval rand = tostring("0." . random())
| eval rand =
case(
(rand > 0.00 AND rand < 0.16), -3,
(rand > 0.16 AND rand < 0.32), -2,
(rand > 0.32 AND rand < 0.48), -1,
(rand > 0.48 AND rand < 0.64), 1,
(rand > 0.64 AND rand < 0.80), 2,
(rand > 0.80 AND rand < 1.00), 3,
true(), 1)
| foreach *
[ eval "<<FIELD>>" = floor(ifnull('<<FIELD>>', 'a_<<FIELD>>' + ('s_<<FIELD>>' * rand), '<<FIELD>>'))]
| fields - a_*, s_*, rand
``````
Super Champion

Hi @morethanyell,

Check below query, it is really easy to understand whats happening.

``````| makeresults | eval a="12", b="13" | append [| makeresults | eval a="13"] | append [| makeresults | eval b="3"] | append [| makeresults | eval a="2", b="1"]
| appendpipe [| stats stdev(*) as stdev_*, avg(*) as avg_*
| foreach avg_*
[ eval r_<<MATCHSTR>>=random() % 2
| eval r_<<MATCHSTR>>=if(r_<<MATCHSTR>>==0,1,-1)
| eval r_<<MATCHSTR>>=(random() % 4) * r_<<MATCHSTR>>
| eval replace_<<MATCHSTR>> = <<FIELD>> + (stdev_<<MATCHSTR>> * r_<<MATCHSTR>>)] | table replace_*]
| eventstats avg(replace_*) as replace_*
| foreach replace_* [eval <<MATCHSTR>>=coalesce(<<MATCHSTR>>, <<FIELD>>)]
| fields - replace_*
``````

Above query will work, just replace first line with your query in question. Below is just understanding how the query is working, if you are interested.

``````   | makeresults | eval a="12", b="13" | append [| makeresults | eval a="13"] | append [| makeresults | eval b="3"] | append [| makeresults | eval a="2", b="1"]
``````

is just to make similar results that you are having.

``````| appendpipe [| stats stdev(*) as stdev_*, avg(*) as avg_*
``````

Finds the stdev and avg.

``````| foreach avg_*
[ eval r_<<MATCHSTR>>=random() % 2
| eval r_<<MATCHSTR>>=if(r_<<MATCHSTR>>==0,1,-1)
| eval r_<<MATCHSTR>>=(random() % 4) * r_<<MATCHSTR>>
| eval replace_<<MATCHSTR>> = <<FIELD>> + (stdev_<<MATCHSTR>> * r_<<MATCHSTR>>)]  | table replace_*]
``````

Finds the random number between (-3,3) and find the value that can be replaced where NULL is present. (replace_* fields)

``````| eventstats avg(replace_*) as replace_*
| foreach replace_* [eval <<MATCHSTR>>=coalesce(<<MATCHSTR>>, <<FIELD>>)]
``````

Replace NULL values with replace_* field values with coalesce command.

``````| fields - replace_*
``````

Just to remove unnecessary field replace_*

Hope this helps!!! BTW @morethanyell this is really nice question.

Builder

Amazing work. But how can I capture the fields with spaces?
EDIT: i worked it out. thank you very much!

Builder

Brute force method I used:

``````index="google_apis" source="https://www.googleapis.com/youtube"
| timechart span=4h avg(subCount) by channel
| fields - NULL
| eval rand = tonumber("0." . random())
| eval rand =
case(
(rand > 0.00 AND rand < 0.16), -3,
(rand > 0.16 AND rand < 0.32), -2,
(rand > 0.32 AND rand < 0.48), -1,
(rand > 0.48 AND rand < 0.64), 1,
(rand > 0.64 AND rand < 0.80), 2,
(rand > 0.80 AND rand < 1.00), 3,
true(), 1)
| eventstats avg as a_*, stdev as s_*
| eval fnv = 'a_Making it happen Vlog' + (rand * 's_Making it happen Vlog')
| eval "Making it happen Vlog" = floor(coalesce('Making it happen Vlog', fnv))
| eval fnv = 'a_The Juicy Vlog' + (rand * 's_The Juicy Vlog')
| eval "The Juicy Vlog" = floor(coalesce('The Juicy Vlog', fnv))
| eval fnv = a_Morgz + (rand * s_Morgz)
| eval Morgz = floor(coalesce(Morgz, fnv))
| eval fnv = a_MrBeast + (rand * s_MrBeast)
| eval MrBeast = floor(coalesce(MrBeast, fnv))
| eval fnv = a_PewDiePie + (rand * s_PewDiePie)
| eval PewDiePie = floor(coalesce(PewDiePie, fnv))
| eval fnv = a_Music + (rand * s_Music)
| eval Music = floor(coalesce(Music, fnv))
| eval fnv = a_TSeries + (rand * s_TSeries)
| eval TSeries = floor(coalesce(TSeries, fnv))
| fields - a_*, s_*, rand, fnv
| fields _time, *
``````
Get Updates on the Splunk Community!

#### Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

#### Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

#### Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...