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

How to ignore fill null values in the result?

karthi2809 — Wed, 16 May 2018 05:55:17 GMT

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

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

mayurr98 — Wed, 16 May 2018 06:07:47 GMT

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!

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

niketn — Wed, 16 May 2018 06:16:05 GMT

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

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

pablo_sanchez — Fri, 01 May 2020 21:05:58 GMT

This should work better. Let me know

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

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

to4kawa — Fri, 01 May 2020 21:36:45 GMT

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.

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

pablo_sanchez — Tue, 05 May 2020 23:56:13 GMT

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