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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...