How do I show 0 when no results are found in a report with multiple columns?

[arunsoni]

Hi All,

I want to display the value "0" when there are "No results found" and the actual values when there are results in my search query in a scheduled report.

There are many answers that relate to my question, but none of them are with multiple columns in a table.

For ex: My base query | stats count email_Id,Phone,LoginId by user | fields - count Is my actual query and the results have the columns email_id, Phone, LoginId and user.

If I write | appendpipe [stats count | where count=0] the result table looks like below

email_id Phone LoginId User Count
0

The other columns with no values are still being displayed in my final results. Can I show my results as only "0" when there are "No results found" ?

Thanks in advance

[MuS · Accepted solution]

Hi arunsoni,

give this run everywhere search a try:

index=_internal sourcetype=splunkd use anything here 
| stats count by sourcetype 
| append 
    [| stats count 
    | eval sourcetype=if(isnull(sourcetype), "Nothing to see here, move along!", sourcetype)] 
| streamstats count AS line_num 
| eval head_num=if(line_num > 1, line_num - 1, 1) | where NOT ( count=0 AND head_num < line_num ) | table sourcetype count

Change the message in the sub search to what ever you want to display. It will only be shown if you have no events from the search.

Hope this helps ...

cheers, MuS

UPDATE:

after the comment this search :

index=_internal sourcetype=splunkd foo bar 
 | stats count by sourcetype 
 | appendcols 
     [| stats count 
     | eval sourcetype=if(isnull(sourcetype), null(), sourcetype) ] 
 | streamstats count AS line_num 
 | eval head_num=if(line_num > 1, line_num - 1, 1) 
 | where NOT ( count=0 AND head_num < line_num ) 
 | table sourcetype count 
 | transpose 
 | transpose header_field=column 
 | fields - column

provided the expect result.

[arunsoni]

Thanks MuS for your reply. My question is slightly different.

From the example you have provided above - If my base query has some results, i'll get the values of sourcetype and count in my results. But if there are no values, I need to display only "0" ( no other field names in the result).

I am wondering if there is a way to tell Splunk to provide me 3 different fields if a value exist VS only one field(which is zero) when there are no results.

[MuS]

Okay, now I got it. Try this slightly modified search:

index=_internal sourcetype=splunkd foo bar 
| stats count by sourcetype 
| appendcols 
    [| stats count 
    | eval sourcetype=if(isnull(sourcetype), null(), sourcetype) ] 
| streamstats count AS line_num 
| eval head_num=if(line_num > 1, line_num - 1, 1) 
| where NOT ( count=0 AND head_num < line_num ) 
| table sourcetype count 
| transpose 
| transpose header_field=column 
| fields - column

cheers, MuS

[arunsoni]

Thank you. This is working as expected.

[MuS]

I updated the answer to reflect the working search. Please accept this answer so it is marked as answered, and others can benefit from it as well 🙂

cheers, MuS

[arunsoni]

The 0 in the results should be under the field "Count" and the other three fields ( email_id, Phone, LoginId and User) are empty. Sorry for the formatting issue

[somesoni2]

So you want to remove those columns from output if there are no results?

[skoelpin]

You should leverage the internal index or a lookup table if you want to return zero instead of null.

[arunsoni]

Thanks for your response Skoelpin. I am scheduling the report for 30days. I beleive the _internal for 30days will have too many events right. Could you please provide the sample query on how do I need to use it in my search.