Splunk Search

How to ignore fill null values in the result?

karthi2809
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
1 Solution

niketnilay
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

niketnilay
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

mayurr98
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

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
SplunkTrust
SplunkTrust

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
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.