Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Special fillnull? Polulate null values with evalua...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

morethanyell

Contributor

06-02-2019
08:41 PM

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.

Thanks in advance

1 Solution

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Special fillnull? Polulate null values with evaluated calculations

morethanyell

Contributor

06-03-2019
07:33 PM

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, *
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Special fillnull? Polulate null values with evaluated calculations

VatsalJagani

Motivator

06-03-2019
11:12 PM

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Special fillnull? Polulate null values with evaluated calculations

morethanyell

Contributor

06-03-2019
11:34 PM

EDIT: i worked it out. thank you very much!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

morethanyell

Contributor

06-04-2019
12:14 AM

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
```