Splunk Search
Highlighted

How to ignore fill null values in the result?

Communicator

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
Highlighted

Re: How to ignore fill null values in the result?

SplunkTrust
SplunkTrust

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
Highlighted

Re: How to ignore fill null values in the result?

New Member

This should work better. Let me know

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

Re: How to ignore fill null values in the result?

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
Highlighted

Re: How to ignore fill null values in the result?

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
Highlighted

Re: How to ignore fill null values in the result?

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"

View solution in original post

Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.