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

[mah]

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)

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.

How can I fix timechart after the appendpipe ?

[dmarling]

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.

[mah]

Hello, it works great. 

Thanks for your help !

[DalJeanis · Accepted solution]

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