Splunk Search

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

nirmalya2006
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
1 Solution

cmerriman
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

cmerriman
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*

nirmalya2006
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

cmerriman
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

nirmalya2006
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

nirmalya2006
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 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

0 Karma

nirmalya2006
Path Finder

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

0 Karma

cmerriman
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
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...