Splunk Search

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

papemalik
Explorer

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

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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>

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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?

0 Karma

papemalik
Explorer

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

0 Karma

papemalik
Explorer

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

papemalik
Explorer

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

0 Karma

papemalik
Explorer

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

it works when i put an integer

0 Karma

papemalik
Explorer

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

0 Karma

somesoni2
Revered Legend

Can you share your dashboard xml?

0 Karma

somesoni2
Revered Legend

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
Explorer

Ok. Thank you so much

0 Karma

aaraneta_splunk
Splunk Employee
Splunk Employee

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

0 Karma

papemalik
Explorer

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

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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
Explorer

ok. thank you very much

0 Karma

papemalik
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...