Splunk Using Result from Subsearch

[ehaque]

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

[ehaque]

Thanks all!

[lguinn2]

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)

[ehaque]

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?

[lguinn2]

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!

[woodcock]

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

[MuS]

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 😉

[woodcock]

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

[ConnorG]

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.

[woodcock]

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.