Splunk Search

How to ignore fill null values in the result?

karthi2809
Builder

In below scenario i want to ignore two vales are null in the result.

index=test |stats count by ErrorDetail ErrorMessage|fillnull value="Not Available" ErrorDetail |fillnull value="Not Available" ErrorMessage|where ErrorDetail!="Not Available" AND Errormessage!="Not Available"

Result:
PHARMACY Not Available Not Available 16

BenefitAccums INFRASTRUCTURE ERROR- WGMMMIOS BAD RETURN; RC = 1 Not Available 18

Excpected Result:
BenefitAccums INFRASTRUCTURE ERROR- WGMMMIOS BAD RETURN; RC = 1 Not Available 18

Have to exclude both field have not available in the result.

Thanks

0 Karma
1 Solution

niketn
Legend

@karthi2809, try the following filter if you wish to retain BenefitAccum's row

 index=test 
| stats count by ErrorDetail ErrorMessage
| fillnull value="Not Available" ErrorDetail,ErrorMessage
| search ErrorDetail!="Not Available" OR ErrorMessage!="Not Available"

Following is a run anywhere search based on sample data provided:
Commands from | makeresults till | fields - data generate dummy data similar to the output of stats and fillnull commands in your example. I was not sure of PHARMACY as it should not show up in results if both ErrorDetail and ErrorMessage are NA. So I have created an additional field in my dummy search. Final search filter would still remain the same.

| makeresults
| eval data="PHARMACY,Not Available,Not Available,16|BenefitAccums,INFRASTRUCTURE ERROR- WGMMMIOS BAD RETURN; RC = 1,Not Available,1"
| makemv data delim="|"
| mvexpand data
| makemv data delim=","
| eval Field1=mvindex(data,0), ErrorDetail=mvindex(data,1),ErrorMessage=mvindex(data,2)
| fields - data
| search ErrorDetail!="Not Available" OR ErrorMessage!="Not Available"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@karthi2809, try the following filter if you wish to retain BenefitAccum's row

 index=test 
| stats count by ErrorDetail ErrorMessage
| fillnull value="Not Available" ErrorDetail,ErrorMessage
| search ErrorDetail!="Not Available" OR ErrorMessage!="Not Available"

Following is a run anywhere search based on sample data provided:
Commands from | makeresults till | fields - data generate dummy data similar to the output of stats and fillnull commands in your example. I was not sure of PHARMACY as it should not show up in results if both ErrorDetail and ErrorMessage are NA. So I have created an additional field in my dummy search. Final search filter would still remain the same.

| makeresults
| eval data="PHARMACY,Not Available,Not Available,16|BenefitAccums,INFRASTRUCTURE ERROR- WGMMMIOS BAD RETURN; RC = 1,Not Available,1"
| makemv data delim="|"
| mvexpand data
| makemv data delim=","
| eval Field1=mvindex(data,0), ErrorDetail=mvindex(data,1),ErrorMessage=mvindex(data,2)
| fields - data
| search ErrorDetail!="Not Available" OR ErrorMessage!="Not Available"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

mayurr98
Super Champion

you can try something like this

index=test 
| stats count by ErrorDetail ErrorMessage 
| where isnotnull(ErrorDetail) AND isnotnull(ErrorMessage)

let me know if this helps!

0 Karma

pablo_sanchez
New Member

This should work better. Let me know

 your search 
 | where isnotnull(ErrorDetail) AND isnotnull(ErrorMessage)
 | stats count by ErrorDetail ErrorMessage 
0 Karma

to4kawa
Ultra Champion

In this query, where can't keep the events has only one field( ErrorDetail or ErrorMessage )

In this case,
after aggregation, if there is not ErrorDetail or ErrorMessage, it is not populated the result.

the result is different.

0 Karma

pablo_sanchez
New Member

You're correct. overlooked it.
Perhaps using OR instead will do it. Worked for me on a similar query.

 your search 
  | where isnotnull(ErrorDetail) OR isnotnull(ErrorMessage)
  | stats count by ErrorDetail ErrorMessage 
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...