topic Re: Create an eval if when the results from stats are 0 in Splunk Search

Create an eval if when the results from stats are 0

ebs — Wed, 16 Jun 2021 00:26:22 GMT

Is there a way, besides fillnull, to do an eval if(averageResponse=0, 0.000)?

Basically, I want to be able to have the stats table show a result of 0.000 for a field if the results after the stats field is 0 without using a fillnull field in the case where every defined field is equalling zero.

This is the base search:

| datamodel metric summariesonly=true search
| search "metric.date"=2021-06-11 | rename "metric.date" as date
| rename "metric.uri_path" as uri_path
| eval category=case(like(uri_path, "/as/%/resume/as/authorization.ping"), "highPriority", uri_path="/pf/heartbeat.ping", "unattended",
uri_path="/?REF=undefined", "lowPriority", uri_path="/header-logo.svg", "largePayload")
| rename "metric.response_time" as response_time
| stats avg(response_time) by category
| rename avg(response_time) as averageResponse
| eval averageResponse=round(averageResponse,3)
| transpose 0 header_field=category
| fillnull value=0.000 highPriority, lowPriority, largePayload, unattended
| eval _time="$date$"
| fields highPriority, lowPriority, largePayload, unattended, _time

Re: Create an eval if when the results from stats are 0

yuanliu — Wed, 16 Jun 2021 02:24:46 GMT

@ebs wrote:
Is there a way, besides fillnull, to do an eval if(averageResponse=0, 0.000)?
Basically, I want to be able to have the stats table show a result of 0.000 for a field if the results after the stats field is 0 without using a fillnull field in the case where every defined field is equalling zero.

It is not clear what is your expected outcome. If all fields equal zero, do you want the stats table to show all 0.0000, or not show all 0.0000?

Re: Create an eval if when the results from stats are 0

ebs — Wed, 16 Jun 2021 04:12:50 GMT

I have a screen shot of what the stats field looks like in my post previous. I would want under every field it to say 0.000 if there are no results

Re: Create an eval if when the results from stats are 0

yuanliu — Wed, 16 Jun 2021 04:30:41 GMT

Maybe one more clarification: This exercise is to transform any single digit "0" into a 5-digit decimal "0.0000", as opposed to insert "0.0000" where a value is absent (which is the use case for fillnull). If this is correct, your question is already the answer. I don't see why you cannot use it.

As an example, I use this to generate a sequence of digits between 0 and 2, then transform "0" into "0.0000" using the exact expression in your question:

| makeresults count=5 | eval value = random() % 3 | eval value = if(value == 0, "0.0000", value)

Re: Create an eval if when the results from stats are 0

ebs — Wed, 16 Jun 2021 04:47:17 GMT

My main issue is when there are no results there is not stats table, that's why I'm looking into doing an eval if statement

Re: Create an eval if when the results from stats are 0

ebs — Wed, 16 Jun 2021 04:52:53 GMT

Works if I use your eval with the fillnull, thanks!

Re: Create an eval if when the results from stats are 0

ebs — Wed, 16 Jun 2021 05:07:38 GMT

I'm finding it overwrites fields with values as well, I only want the if statement to apply to fields where the value is 0

Re: Create an eval if when the results from stats are 0

ebs — Wed, 16 Jun 2021 05:14:34 GMT

Fixed that, my issue. But now it won't produce the stats table anyway when there are no results at all

Search:

| datamodel metric summariesonly=true search
| search "metric.date"=2021-06-11
| rename "metric.date" as date
| rename "metric.uri_path" as uri_path
| eval category=case(like(uri_path, "/url1"), "highPriority", uri_path="/url2", "unattended",
uri_path="/url3", "lowPriority", uri_path="/url4", "largePayload")
| rename "metric.response_time" as response_time
| stats avg(response_time) by category
| rename avg(response_time) as averageResponse
| eval averageResponse = if(averageResponse == 0, "0.000", averageResponse)
| eval averageResponse=round(averageResponse,3)
| transpose 0 header_field=category
| fillnull value=0.000 highPriority, lowPriority, largePayload, unattended
| eval _time="$date$"
| fields highPriority, lowPriority, largePayload, unattended, _time

Re: Create an eval if when the results from stats are 0

yuanliu — Wed, 16 Jun 2021 15:49:54 GMT

@ebs wrote:
Fixed that, my issue. But now it won't produce the stats table anyway when there are no results at all

To make certain of the situation: A previous search made some false zeros (0); now that you fixed the search, those results become absent (null). As a consequence, the stats table won't show results. Is this correct?

If that is the case (which differs from the title statement), handling all nulls in a timeseries is a common annoyance. I had asked for help several times before, and there have been several recent posts about this. The most recent, and a rather efficient one, is offered by @bowesmana in https://community.splunk.com/t5/Splunk-Search/Need-to-generate-0-results-in-case-of-no-data-available/m-p/555876/highlight/true#M157840

Re: Create an eval if when the results from stats are 0

ebs — Wed, 16 Jun 2021 22:22:09 GMT

So basically the initial eval function I did was overriding the fields with previous values, the ones that were already null were getting filled in by the fillnull which is fine in this moment. But when all of the fields were zero, no stats table would show at all. Just a no results found