How to ignore fill null values in the result?

[karthi2809]

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

[niketn · Accepted solution]

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

[mayurr98]

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!

[pablo_sanchez]

This should work better. Let me know

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

[to4kawa]

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.

[pablo_sanchez]

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