Splunk Search

coalesce count

dpolochefm
Explorer

The goal is to get a count when a specific value exists 'by id'.  This is not working on a coalesced search.

The search below works, it looks at two source types with different field names that have the same type of values.  I used this because appendcols is very computation costly and I try to avoid it as much as possible. 

One alternative I tried was:

count(eval(if(isnotnull(calcValue),1,null())))

Lastly I tried, however that one gives an error:  Error in 'stats' command: The eval expression for dynamic field 'if("total-calcValue">0,1,null())' is invalid. Error='Type checking failed. The '>' operator received different types.'.

 

count(eval(if("total-calcValue">0,1,null())))

 

Below is the full search

 

index=someindex (sourcetype1)  OR (sourcetype2) 
| rex field="Some Amt" "-(?<Amount>[\d\.]+)"
| convert num(Amount)
| rename Amount as total-calcValue, total as total-charges, "Some ID" as miq, "Other Source Company Name" as "other_source_company_name"
| eval mid=coalesce(mid,miq)
| eval Company=coalesce(other_source_company_name,company_name)
| stats count first(Company) AS "Company" sum(total-*) AS * count(eval(if(isnotnull("total-calcValue"),1,null()))) as "calcValue Count" by mid

 

Labels (3)
0 Karma
1 Solution

dpolochefm
Explorer

The issue was that 

"total-calcValue"

needed to be changed to

'total-calcValue'

 It was taking the double quote version as a literal text value instead of the field..

View solution in original post

dpolochefm
Explorer

The issue was that 

"total-calcValue"

needed to be changed to

'total-calcValue'

 It was taking the double quote version as a literal text value instead of the field..

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Using hyphens in field names is not a great idea.  Stick with underscores or use camelCase.  Introducing other characters may be valid, but can lead to errors (as you've seen) or confusion (is "total-calcValue" a string, an expression, or something else?).  Often, putting single quotes around the field will help, but not always.

Try this modification of your query.

index=someindex (sourcetype1)  OR (sourcetype2) 
| rex field="Some Amt" "-(?<Amount>[\d\.]+)"
| convert num(Amount)
| rename Amount as total_calcValue, total as total_charges, "Some ID" as miq, "Other Source Company Name" as "other_source_company_name"
| eval mid=coalesce(mid,miq)
| eval Company=coalesce(other_source_company_name,company_name)
| stats count first(Company) AS "Company" sum(total_*) AS * sum(eval(isnotnull(total_calcValue))) as "calcValue Count" by mid

 

---
If this reply helps you, an upvote would be appreciated.
0 Karma