Dashboards & Visualizations

How can I fix timechart after the appendpipe ? [stats count | where count=0]

mah
Builder

I have a dashboard which contains several panels displaying kpi: those returning no results as sometimes returns the message "no results found".

Example of my initial search :

| makeresults

| timechart span=1d sum(count) as count

| eval count=0

| append [search index="alert" source="alert*" insight="User alert"

| lookup account_ids account_id OUTPUT title platform

| rename title as account

| search platform="*" account="*"]

| timechart span=1d sum(count) as count

The results are:  "No results found."

I found the start of a solution via a support response to this question: answers.splunk.com/answers/582253/replacing-no-results-found-with-0.html

 

I applied the solution explained to my search : 

 

| makeresults

| timechart span=1d sum(count) as count

| eval count=0

| append [search index="alert" source="alert*" insight="User alert"

| lookup account_ids account_id OUTPUT title platform

| rename title as account

| search platform="*" account="*"]

| timechart span=1d sum(count) as count

| appendpipe [stats count | where count = 0]

 

The new result is now a board with a column count and a result 0 instead the 0 on each 7 days (timechart)

Capture d’écran 2020-06-08 à 15.09.47.png

However, I use a timechart in my request and when I apply at the end of the request | appendpipe [stats count | where count = 0] this only returns the count without the timechart span on 7d.

In an example which works good, I have the result with the timechart on 7d and I can show the trend on my visualization and that's not possible with the search below.

Capture d’écran 2020-06-08 à 15.21.02.png

Capture d’écran 2020-06-08 à 15.22.15.png

How can I fix timechart after the appendpipe ?

Labels (1)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

@mah  - If your problem is solved, please accept the solution that worked.

View solution in original post

0 Karma

dmarling
Builder

That query is very complicated for what you are trying to accomplish.  This is much simpler and accomplishes your goal:

index="alert" source="alert*" insight="User alert"
| lookup account_ids account_id OUTPUT title platform
| rename title as account
| search platform="*" account="*"
| append [| makeresults count=1]
| timechart span=1d count(account) as count

Timechart will put a zero when using count, but will be null when using sum if the field does not exist.  Since you were trying to sum a nonexistent count field it was returning a null field.  Counting a field in your data set will solve that.

If this comment/answer was helpful, please up vote it. Thank you.
Tags (1)

mah
Builder

Hello, it works great. 

Thanks for your help !

Tags (1)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@mah  - If your problem is solved, please accept the solution that worked.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...