topic coalesce count in Splunk Search

coalesce count

dpolochefm — Wed, 20 Jan 2021 15:03:58 GMT

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

Re: coalesce count

richgalloway — Wed, 20 Jan 2021 15:17:12 GMT

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

Re: coalesce count

dpolochefm — Fri, 22 Jan 2021 18:00:31 GMT

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