Splunk Search

Splunk Using Result from Subsearch

ehaque
New Member

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

Tags (2)
0 Karma

ehaque
New Member

Thanks all!

0 Karma

lguinn2
Legend

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
New Member

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?

0 Karma

lguinn2
Legend

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!

0 Karma

woodcock
Esteemed Legend

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
0 Karma

MuS
Legend

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
Esteemed Legend

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
0 Karma

ConnorG
Path Finder

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...