How to edit my search to display appendcols subsearch results, even if the main search returns no events?

[nirmalya2006]

Hi All

I have a search as below.

index = enterprise_idx1 sourcetype = sft   
| dedup _time, BatchId  
| search BatchStatus = C4   
| timechart span="1d" dc(BatchId) as "Successful"   
| appendcols [search index = enterprise_idx1 sourcetype = sft
| dedup _time, BatchId  
| search BatchStatus = E1 OR BatchStatus = E2 OR BatchStatus = E3 OR BatchStatus = E4
| timechart span="1d" dc(BatchId) as "Errored"]   

The query works fine when I have data for both successful and error.
But if I do not have data for successful (as per the above query), the error data is also not displayed.
In short, if the first part of the query does not return any rows, then the appendcols part is also not displayed.

Is there any way this can be handled?

Thanks
Nirmalya

[cmerriman · Accepted solution]

the problem is because you don't have a _time field that is populated, I assume. When I run the first part without the |addinfo, I don't get anything populated under _time because there were no Successful events.

I tried this and I got data back:

 index = enterprise_idx1 sourcetype = sft 
 | dedup _time, BatchId 
 | search BatchStatus = C4 
 | timechart span="1d" dc(BatchId) as "Successful" 
 | append [search index = enterprise_idx1 sourcetype = sft
 | dedup _time, BatchId 
 | search BatchStatus = E1 OR BatchStatus = E2 OR BatchStatus = E3 OR BatchStatus = E4
 | timechart span="1d" dc(BatchId) as "Errored"]
 |stats values(Successful) as Successful values(Errored) as Errored by _time
 | addinfo 
 | eval info_max_time=if (info_max_time="+Infinity",9999999999,info_max_time) 
 | where _time >= info_min_time AND _time < info_max_time 
 | fields - info*

[nirmalya2006]

Ya this worked fine.
It seems replacing "appendcols" with "append" is working.
Also this didn't need the "|stats".
But it needs the |addinfo

@cmerriman
Please convert your comment to Answer. I will accept it for all others who are following this Topic

[cmerriman]

try this:

index = enterprise_idx1 sourcetype = sft 
| dedup _time, BatchId 
| search BatchStatus = C4 
| timechart span="1d" dc(BatchId) as "Successful" 
| append [search index = enterprise_idx1 sourcetype = sft
| dedup _time, BatchId 
| search BatchStatus = E1 OR BatchStatus = E2 OR BatchStatus = E3 OR BatchStatus = E4
| timechart span="1d" dc(BatchId) as "Errored"]
|stats values(Successful) as Successful values(Errored) as Errored by _time

[nirmalya2006]

@cmerriman
Can you please try this with your data. I am attaching the full query that I have in the dashboard.

index = enterprise_idx1 sourcetype = sft 
| dedup _time, BatchId 
| search BatchStatus = C4 
| timechart span="1d" dc(BatchId) as "Successful" 
| appendcols [search index = enterprise_idx1 sourcetype = sft
| dedup _time, BatchId 
| search BatchStatus = E1 OR BatchStatus = E2 OR BatchStatus = E3 OR BatchStatus = E4
| timechart span="1d" dc(BatchId) as "Errored"] 
| addinfo 
| eval info_max_time=if (info_max_time="+Infinity",9999999999,info_max_time) 
| where _time >= info_min_time AND _time < info_max_time 
| fields - info*

The first part of the query should have no data but the second part must have some data.
Please see if you are also getting "No results Found"

[nirmalya2006]

Just found something odd too.
I was actually having the below query appended for tracking datetime from input parameters on the dashboard.

| addinfo

| eval info_max_time=if (info_max_time="+Infinity",9999999999,info_max_time)

| where _time >= info_min_time AND _time < info_max_time

| fields - info*

So if I remove this part, then the query works fine. But with this appended it does not work.
What can be the issue with this addinfo part

[nirmalya2006]

Nopes.. It doesn't work. Same as before.

[cmerriman]

that's odd, I ran that on some of my data and it works. The first bit of data I made sure returned no results and I appended a search with results. I get a table with _time Successful (which has no values) and Errored
Does the Errored subsearch have data?

if Errors doesn't have data, perhaps add this so you know:
| appendpipe [ stats count | eval "Errored"="No Results Found" | where count==0 |table "Errored"]