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