topic Re: Unable to divide output of two queries in Splunk Search

Unable to divide output of two queries

preetham2215 — Thu, 23 Jul 2020 20:17:16 GMT

Hi team, I want to divide the output result of one query with output of second query and get a remainder. I am using the following query but unable to get any results for this

index="wcnp_search-frontend" kubernetes.container_name=search-electrode-app "log.event"=ATC_CLICK | stats count by log.event |rename log.event as total_atc_events | append [ search index="wcnp_search-frontend" kubernetes.container_name=search-electrode-app "log.msg"="ATC click failure" | stats count by log.msg | rename log.msg as atc_failures ] | eval error = max(total_atc_events) / max(atc_failures) | stats count by error
Can anyone please assist ?

Re: Unable to divide output of two queries

richgalloway — Thu, 23 Jul 2020 21:09:37 GMT

There are a couple of lapses in that query.

The eval uses fields (max(total_atc_events) and max(atc_failures)) that don't exist. max is a stats function, not an eval function.

There seems to be some confusion over how append works. I'll try to explain.

The main search returns a set of results with fields "count" and "total_atc_events".

The subsearch within append returns a separate set of results with fields "count" and "atc_failures".

It's like writing two shopping lists on the same page. There's nothing to tie them together other than being on the same paper.

The solution is simple. Use the stats command to correlate the two sets of results based on one or more common fields. Do the events have a common field?

index="wcnp_search-frontend" kubernetes.container_name=search-electrode-app "log.event"=ATC_CLICK | stats count by log.event |rename log.event as total_atc_events | append [ search index="wcnp_search-frontend" kubernetes.container_name=search-electrode-app "log.msg"="ATC click failure" | stats count by log.msg | rename log.msg as atc_failures ] | stats values(*) as * by <some common field name> | eval error = max(total_atc_events) / max(atc_failures) | stats count by error

Re: Unable to divide output of two queries

to4kawa — Thu, 23 Jul 2020 23:57:16 GMT

index="wcnp_search-frontend" kubernetes.container_name=search-electrode-app "log.event"=ATC_CLICK
| stats count as total_atc_events by log.event
| appendcols
[ search index="wcnp_search-frontend" kubernetes.container_name=search-electrode-app "log.msg"="ATC click failure"
| stats count as atc_failures by log.msg ]
| eval error = max(total_atc_events) / max(atc_failures)

if it's going to run eval, like above.
I'm not sure what you want.

index="wcnp_search-frontend" kubernetes.container_name=search-electrode-app "log.event"=ATC_CLICK OR "log.msg"="ATC click failure"
| stats count(eval(log.event="ATC_CLICK")) as total_atc_events count(eval(log.msg="ATC click failure")) as atc_faiures
| eval error = round(atc_failures / total_atc_events * 100,2)."%"

Isn't that good enough?