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

[papemalik]

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

[martin_mueller · Accepted solution]

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>

[martin_mueller]

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?

[papemalik]

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

[papemalik]

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

[martin_mueller]

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

[martin_mueller]

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

The general flow is like this:

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

[papemalik]

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

[papemalik]

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

it works when i put an integer

[papemalik]

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

[somesoni2]

Can you share your dashboard xml?

[somesoni2]

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

[papemalik]

Ok. Thank you so much

[aaraneta_splunk]

@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.

[papemalik]

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

[skoelpin]

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)

[papemalik]

ok. thank you very much

[papemalik]

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