Splunk Search

List top 3 forwarders by volume sent to each indexer

Motivator

I'd like to see for each indexer in my environment the top 3 forwarders that have sent data. I've created the following search but the top command isn't giving me the correct results. I've sorted the data after the stats command which allows me to quickly spot check the results. This search does show three forwarders per indexer but for example a check I just ran (by cutting out the top command) lists result numbers 18, 321, and 322 from the results of just the stats command.

index=_internal source=*metrics.log group=tcpin_connections | eval sourceHost=if(isnull(hostname), sourceHost,hostname) | stats sum(kb) as total_kb by host sourceHost | sort -total_kb | top limit=3 total_kb sourceHost by host | sort host -total_kb
Tags (1)
0 Karma

Motivator

Thanks for taking a look at the question linu and MuS. Based on other threads I looked at top / rare are based on number of events and given the stats command prior to top there is only one event per indexer/forwarder pair. I had hoped it was field value aware.

At the end of the day I'm working with support to address issues where forwarders are heavily favoring one or just a few indexers. This issue is basically a combination of using a forwarder prior to 5.0.5 and where there is a high event volume. Over a reasonable period of time I would expect a high volume forwarder to make the top 3 list on multiple indexers so finding cases where it showed up on only a couple would be significant. Since this avenue wasn't working for me (still hope to pursue) I figured I would compare the median volume of data indexed across all indexers to the max value from all (10x in this case). On my own I would have created a macro but since I'm working with support and we are passing queries back and forth I figured this would be an easier solution. Feel free to provide comments! The data is in everyone's Splunk install

index=_internal source=*metrics.log group=tcpin_connections | eval version=if(isnull(version),"pre 4.2",version) | eval connectType=case(fwdType=="uf","univ fwder", fwdType=="lwf", "lightwt fwder",fwdType=="full", "heavy fwder", connectType=="cooked" or connectType=="cookedSSL","Splunk fwder", connectType=="raw" or connectType=="rawSSL","legacy fwder") | eval sourceHost=if(isnull(hostname), sourceHost,hostname) | stats sum(kb) as total_kb by host sourceHost sourceIp os connectType version | stats sum(total_kb) as total max(total_kb) as max avg(total_kb) as avg median(total_kb) as med min(total_kb) as min by sourceHost sourceIp os connectType version | where max > (med*10) | sort -total | eval total = case((total/1048576)>=1, round(total/1048576,0). " GB", (total/1024)>=1, round(total/1024,0)." MB", 1=1, round(total,0)." kb") | eval avg = case((avg/1048576)>=1, round(avg/1048576,0). " GB", (avg/1024)>=1, round(avg/1024,0)." MB", 1=1, round(avg,0)." kb") | eval med = case((med/1048576)>=1, round(med/1048576,0). " GB", (med/1024)>=1, round(med/1024,0)." MB", 1=1, round(med,0)." kb") | eval max = case((max/1048576)>=1, round(max/1048576,0). " GB", (max/1024)>=1, round(max/1024,0)." MB", 1=1, round(max,0)." kb") | eval min = case((min/1048576)>=1, round(min/1048576,0). " GB", (min/1024)>=1, round(min/1024,0)." MB", 1=1, round(min,0)." kb") 

As an example my top result is a 4.3.4 universal forwarder that in a 24hr period sent 189GB to one indexer but across all indexers has a median volume of only 500MB. The best option is upgrade the forwarder but could probably use forceTimebasedAutoLB=true on the outputs.conf for this forwarder since the events are mostly single line. Support calls this issue 'Indexer Affinity'

SplunkTrust
SplunkTrust

cloud you please provide more details? Because basically you say, it works but then again you say the stats command provides different results??

0 Karma

Champion

is sourceHost a numeric field?

you may want to try only with total_kb, sorts are also not required.

 index=_internal source=*metrics.log group=tcpin_connections | eval sourceHost=if(isnull(hostname), sourceHost,hostname) | stats sum(kb) as total_kb by host sourceHost |top limit=3 total_kb by host | sort host -total_kb
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!