topic Re: How to edit my search to display appendcols subsearch results, even if the main search returns no events? in Splunk Search

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

nirmalya2006 — Tue, 20 Dec 2016 17:00:39 GMT

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

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

cmerriman — Tue, 20 Dec 2016 17:19:50 GMT

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

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

nirmalya2006 — Tue, 20 Dec 2016 17:40:46 GMT

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

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

cmerriman — Tue, 20 Dec 2016 17:50:13 GMT

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"]

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

nirmalya2006 — Tue, 29 Sep 2020 12:11:02 GMT

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

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

nirmalya2006 — Tue, 20 Dec 2016 18:21:03 GMT

@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"

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

cmerriman — Tue, 20 Dec 2016 19:04:28 GMT

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*

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

nirmalya2006 — Tue, 20 Dec 2016 20:48:41 GMT

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