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
Legend

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

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

0 Karma
Get Updates on the Splunk Community!

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...

IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...