topic Re: summing values from stats in Splunk Search

summing values from stats

dbcase — Wed, 06 Dec 2017 20:28:37 GMT

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

Re: summing values from stats

dbcase — Wed, 06 Dec 2017 20:57:00 GMT

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

Re: summing values from stats

Lamar — Wed, 06 Dec 2017 21:17:32 GMT

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.

Re: summing values from stats

dbcase — Wed, 06 Dec 2017 21:37:40 GMT

Hi Lamar,

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

Re: summing values from stats

Lamar — Wed, 06 Dec 2017 21:50:22 GMT

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

Re: summing values from stats

Lamar — Wed, 06 Dec 2017 21:54:38 GMT

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.

Re: summing values from stats

elliotproebstel — Wed, 06 Dec 2017 21:56:10 GMT

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