How to get my transaction search to return "0" instead of "no results found" if no events are found?

[smhsplunk]

I am trying to use the transaction command to get duration between two events
In case there are no such events, I would like the search to return 0 instead of "no results found".
This following command isn't working:

    index=main host="xyz"   
            | transaction startswith="keyword1" endswith="keyword2" 
            | eval spent_time = duration 
            | stats sum(spent_time) as total_spent_time
                    | table total_spent_time 
                    | fillnull value=NULL

[somesoni2 · Accepted solution]

Try this

index=main host="xyz"   
             | transaction startswith="keyword1" endswith="keyword2" 
             | appendpipe [| stats count | where count=0 | eval duration=0]
             | eval spent_time = duration 
             | stats sum(spent_time) as total_spent_time
                     | table total_spent_time 

[smhsplunk]

thanks, didnt know about the appendpipe command

[ppablo]

Hi @smhsplunk

Glad you found a solution through the awesome @somesoni2 (number 1 ranked user on Splunk Answers btw ;D). Please don't forget to resolve the post by clicking "Accept" directly below his answer. This will make the solution easier to find for other users with a similar requirement.

Cheers

[ppablo]

Hi @smhsplunk

There have been several questions similar to this already on Answers. Here's one of the more recent ones I found by searching:
https://answers.splunk.com/answers/336907/return-0-if-search-returns-no-results-found.html

See if the answer and comments there with proper placement of the fillnull command help solve your issue.