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
@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"
@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"
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!
This should work better. Let me know
your search
| where isnotnull(ErrorDetail) AND isnotnull(ErrorMessage)
| stats count by ErrorDetail ErrorMessage
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.
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