topic Re: How to extract and calculate the sum of a field from different searches? in Splunk Search

How to extract and calculate the sum of a field from different searches?

papemalik — Tue, 31 Jan 2017 17:05:18 GMT

Hello,

i have on a dashboard with 5 different searches, where i have a common (calculated) field (let's call it a score field), that i would like to extract and sum all the score field, in order to have a total score and then the average score.

is that possible? and how?

thank you very much for your help

Re: How to extract and calculate the sum of a field from different searches?

somesoni2 — Tue, 31 Jan 2017 17:08:22 GMT

Can you share your dashboard xml?

Re: How to extract and calculate the sum of a field from different searches?

papemalik — Tue, 31 Jan 2017 17:28:48 GMT

Unfortunately i can't. I'll try to anonymize the information.

but you can see it just as 5 differents queries with a common a field.

Thanks for your help

Re: How to extract and calculate the sum of a field from different searches?

somesoni2 — Tue, 31 Jan 2017 17:30:04 GMT

Natively it's not possible to get the values of field from various panels and show in separate panel. Only option would be merge all the searches together as a base search and use panels to populate data using post-process search. See this for more info on Post-Process in dashboards:
http://docs.splunk.com/Documentation/Splunk/6.5.1/Viz/Savedsearches#Post-process_searches

Re: How to extract and calculate the sum of a field from different searches?

skoelpin — Tue, 31 Jan 2017 18:22:11 GMT

Bingo! Also, this assumes all your values are integers. If some or all of your values are strings then you can change them to integers doing this

... | convert num(FIELD_NAME)

Re: How to extract and calculate the sum of a field from different searches?

papemalik — Wed, 01 Feb 2017 09:08:30 GMT

Ok. Thank you so much

Re: How to extract and calculate the sum of a field from different searches?

papemalik — Wed, 01 Feb 2017 09:09:02 GMT

ok. thank you very much

Re: How to extract and calculate the sum of a field from different searches?

aaraneta_splunk — Tue, 14 Feb 2017 01:12:29 GMT

@papermalik - Did the comment provided by somesoni provide a solution to your question? If yes, please let me know so that I can convert it to an Answer to close out your question. If no, please leave a comment with more feedback. Thank you.

Re: How to extract and calculate the sum of a field from different searches?

papemalik — Tue, 14 Feb 2017 12:57:05 GMT

yes it did help, but the solution is not satisfying yet. Anyway, thank you very much

Re: How to extract and calculate the sum of a field from different searches?

martin_mueller — Tue, 14 Feb 2017 13:00:41 GMT

Within the scope of a dashboard, you could have each search populate a token $score_1$ , $score_2$ , etc. and then merge the five tokens into one overall score token - that score token can then be displayed in an HTML panel or whereever you like.

Docs for setting the individual score tokens: http://docs.splunk.com/Documentation/Splunk/6.5.2/Viz/EventHandlerReference#done

Working example:

<dashboard>
  <label>score</label>
  <row>
    <panel>
      <table>
        <search>
          <query>index=_internal | stats count</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <done>
            <set token="score_1">$result.count$</set>
          </done>
        </search>
      </table>
      <table>
        <search>
          <query>index=_audit | stats count</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <done>
            <set token="score_2">$result.count$</set>
          </done>
        </search>
      </table>
      <table>
        <search>
          <query>| makeresults | eval score = $score_1$ + $score_2$</query>
        </search>
      </table>
    </panel>
  </row>
</dashboard>

Re: How to extract and calculate the sum of a field from different searches?

papemalik — Thu, 16 Feb 2017 11:30:56 GMT

Hello, i'm trying this technique but i'm having trouble.

For the first query i have: | search sourcetype .......... | eval score_1=count*10

For the second query i have: | search sourcetype ...... | eval score_2=count*15

So when i set the token for each query $score_2$ ? ?
each token is like a variable, so i'm giving to the token the resul of the eval.

for the sum

| makeresults (the different queries combined?)

thanks for your help, bcause i'm have troubling to understand it

Re: How to extract and calculate the sum of a field from different searches?

martin_mueller — Thu, 16 Feb 2017 13:17:41 GMT

Did you copy my example into a new dashboard and try running it?

The general flow is like this:

query 1 runs with a result field
the set element under query 1 takes the result field and writes that to the score_1 token
query 2 runs with a result field
the set element under query 2 takes the result field and writes that to the score_2 token
Both tokens being now set, the third query runs and calculates the sum of both scores

Re: How to extract and calculate the sum of a field from different searches?

papemalik — Fri, 17 Feb 2017 15:35:11 GMT

Ok i get it.

It's still not working, maybe because i'm trying to display the result in a different panel?

i even used $result.myfield$ to display only one field, but still no luck.

Thanks

Re: How to extract and calculate the sum of a field from different searches?

papemalik — Fri, 17 Feb 2017 16:09:14 GMT

"| makeresults | eval score = $result.count$ + $result.count$"
This is what it does.

it works when i put an integer

Re: How to extract and calculate the sum of a field from different searches?

martin_mueller — Fri, 17 Feb 2017 16:44:34 GMT

The panels don't matter. Do post your XML, much easier than guessing in the dark.

Re: How to extract and calculate the sum of a field from different searches?

martin_mueller — Fri, 17 Feb 2017 20:07:15 GMT

values(risk_score) as risk_rule yields a multi-value field, what result do you get when you run sourcetype=web | stats count as count values(risk_score) as risk_rule by user | eval risk_score_user=count*risk_rule | table user risk_score_user count?

Re: How to extract and calculate the sum of a field from different searches?

papemalik — Mon, 20 Feb 2017 08:53:09 GMT

oohh, i have a search with a token, so i can search by user. and the result is the according score to each user.
when i don't give a user, it becomes then a multivalue result, so it won't work, but i put in entry a username it becomes a single value

Re: How to extract and calculate the sum of a field from different searches?

papemalik — Tue, 29 Sep 2020 12:55:59 GMT

I found it.

It's probably a bug, but "| table user risk_score_user count" was the problem. when i remove it, it works or i have to put commas between my fields.

Thanks a lot for your time and effort