Splunk Search

summing values from stats

dbcase
Motivator

Hi,

I have a query that produces a stats table that looks like this

    company          count
    testco                100
    testcoa              200
    testcob              100
    testcoc              500
    nottestco          50
    otherco             150
    someco             300

I need to sum up the counts for each company. In this example testco, testcoa, testcob and testcoc are all the same company just different divisions. I need to return a value of 900 for testco, 50 for nottestco, 150 for otherco and 300 for someco.

Seems easy but my brain isn't working today......

Tags (1)
0 Karma
1 Solution

dbcase
Motivator

well I found a way to make it work, not sure if it is the best way though

index=mso_statistics sourcetype=ic_connectivity_5min-too_small  stat_name=subscribers |rex "\d+\s(?<mso>\w+)"|stats max(stat_val) as Subscribers by mso|where like(mso,"%testco%") |stats sum(Subscribers) as count

In the query testco is actually a token value, I substituted it just to illustrate

View solution in original post

0 Karma

elliotproebstel
Champion

If you literally have the table in the original post and just need the output you specified, I'd use this:

[ your search] 
| eval company=if(like(company, "testco%"), "testco", company) 
| stats sum(count) AS count BY company 
0 Karma

Lamar
Splunk Employee
Splunk Employee

You may want to use a lookup to federate this information and then search on that field.

http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Addfieldsfromexternaldatasources

You may already have this information in the form of a DB or CMDB.

0 Karma

dbcase
Motivator

Hi Lamar,

I'm not sure I understand what you are trying to say....

0 Karma

Lamar
Splunk Employee
Splunk Employee

What using a lookup provides is the efficiency of updating one source of information rather than have an incredibly monolithic macro (or god forbid, searches) that include the logic for handling all of the different companies in your data. You could even go so far as include other types of information about said company in the lookup that could return things like address, type of company, contact information...etc.

0 Karma

Lamar
Splunk Employee
Splunk Employee

With a lookup, what you can do is something similar to this:

lookup_file.csv

mso|company
testco|Company1
testcoa|Company1
testcob|Company1
testcoc|Company1
otherco|Company2
someotherco|Company3

search

index=mso_statistics sourcetype=ic_connectivity_5min-too_small  stat_name=subscribers | lookup lookup_file mso OUTPUT company | eventstats count by company | stats sum(count) as totals by company

EDIT: a letter

0 Karma

dbcase
Motivator

well I found a way to make it work, not sure if it is the best way though

index=mso_statistics sourcetype=ic_connectivity_5min-too_small  stat_name=subscribers |rex "\d+\s(?<mso>\w+)"|stats max(stat_val) as Subscribers by mso|where like(mso,"%testco%") |stats sum(Subscribers) as count

In the query testco is actually a token value, I substituted it just to illustrate

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...