topic Re: Splunk Using Result from Subsearch in Splunk Search

Splunk Using Result from Subsearch

ehaque — Tue, 29 Sep 2020 06:43:42 GMT

Hi, i need to do following from table x for field a and b

For each category b
- sum(a) / (total rows from table x)

I am using subsearch to calc (total rows from x) as shown below. Is there better way. I am getting
"Error in 'SearchProcessor': Mismatched quotes and/or parenthesis."

SPL CODE

source="vx-fcr.csv" host="vx-contacts" sourcetype="csv-vx-fcr" date_year = 2015 earliest=-60d
| timechart span=1w
eval(
sum(a)
/
tonumber([search source="vx-fcr.csv" host="vx-contacts" sourcetype="csv-vx-fcr" date_year = 2015 earliest=-60d | stats sum(count_fcr_eligible_cases) as total | return $total])
) by b

Re: Splunk Using Result from Subsearch

woodcock — Thu, 16 Jul 2015 23:20:44 GMT

Like this:

source="vx-fcr.csv" host="vx-contacts" sourcetype="csv-vx-fcr" date_year = 2015 earliest=-60d | eventstats sum(count_fcr_eligible_cases) as total | timechart span=1w eval(sum(a)/total) BY b

Re: Splunk Using Result from Subsearch

MuS — Thu, 16 Jul 2015 23:29:15 GMT

This will result in an error:

Error in 'timechart' command: Only the split-by and x-axis fields can be directly referenced in the eval expression.

I know, because I tried it myself this way 😉

Re: Splunk Using Result from Subsearch

lguinn2 — Thu, 16 Jul 2015 23:30:40 GMT

No need for a subsearch, which is quite inefficient. The date_year field is unnecessary. Another thing - the timechart command requires a statistical function as an argument; notice that I have used "avg(calcA)". Here is a link to the statistical functions that timechart supports:

source="vx-fcr.csv" host="vx-contacts" sourcetype="csv-vx-fcr" earliest=-60d
|stats sum(a) as sum_a count by b
| eventstats sum(count) as total
| eval calcA = sum_a / total
| timechart span=1w avg(calcA)

Re: Splunk Using Result from Subsearch

woodcock — Thu, 16 Jul 2015 23:54:27 GMT

OK, like this:

source="vx-fcr.csv" host="vx-contacts" sourcetype="csv-vx-fcr" date_year = 2015 earliest=-60d | eventstats sum(count_fcr_eligible_cases) as total | timechart span=1w sum(a) AS sumA BY b | eval sumA=sumA/total

Re: Splunk Using Result from Subsearch

ehaque — Fri, 17 Jul 2015 00:29:29 GMT

Thanks i did something similar, it worked!!

Btw, for different ppurposes, cant i use the subsearch in my original way to calculate something and return a value for the outer query?

Re: Splunk Using Result from Subsearch

ehaque — Fri, 17 Jul 2015 00:30:17 GMT

Thanks all!

Re: Splunk Using Result from Subsearch

lguinn2 — Fri, 17 Jul 2015 08:40:34 GMT

Sometimes subsearch is the only way to solve a problem, but it is usually not the most efficient way. Also, subsearches have limitations that a base search does not. The "Splunk" way to do it is to collect all the data in the base search, if possible.

Often, using subsearches and where commands reflects an SQL mode of thinking, which usually leads to less effective solutions. And you can't use subsearches everywhere... for example, you can't insert the results of a subsearch into a timechart command!

Re: Splunk Using Result from Subsearch

ConnorG — Thu, 13 Aug 2015 21:17:54 GMT

Hey Woodcock,

I'm using a similar syntax and noticed that this:

"timechart span=1w sum(a) AS sumA BY b | eval sumA=sumA/total"
works and this:
"timechart span=1w sum(a) AS sumA | eval sumA=sumA/total"
does not.

Is there any specific reason that this is the case? Hard coding a number in place of total works in all cases.

Re: Splunk Using Result from Subsearch

woodcock — Thu, 13 Aug 2015 22:29:45 GMT

Try this:

source="vx-fcr.csv" host="vx-contacts" sourcetype="csv-vx-fcr" date_year = 2015 earliest=-60d | eventstats sum(count_fcr_eligible_cases) as total | timechart span=1w first(total) AS total sum(a) AS sumA BY b | eval sumA=sumA/total

And this:

source="vx-fcr.csv" host="vx-contacts" sourcetype="csv-vx-fcr" date_year = 2015 earliest=-60d | eventstats sum(count_fcr_eligible_cases) as total | timechart span=1w first(total) AS total sum(a) AS sumA | eval sumA=sumA/total

Don't forget to click "Accept" to close out the Question if it works.