Splunk Search
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

Super Champion

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
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

Super Champion

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

0 Karma
Highlighted

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

Path Finder

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

| addinfo

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

| where time >= infomintime AND _time < infomax_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

0 Karma
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

Super Champion

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*

View solution in original post

Highlighted

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

Path Finder

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

0 Karma