topic Re: Get Percentage of Network bandwidth in Splunk Search

Get Percentage of Network bandwidth

tysonjhayes — Wed, 06 May 2015 16:33:49 GMT

I'm looking to define a query that allows me to query the Network Interface for all my machines and create a percentage utilization for each interface. I'm having a bit of trouble with it though.

What I'm ultimately looking for is to take the TotalBytes being used on my Network Interface and divide by my current bandwidth. Basically: ((totalBytes*8)/CurrentBandwidth) * 100

I've come up with the following query but CurrentBandwidth doesn't come back with anything and I get an error that I'm interpreting to me an I'm dividing by zero.

index=index host=host object="Network Interface" counter="Bytes Total/sec"
    | bucket _time span=1m
    | stats avg(Value) as bytesByHost by _time,host
    | stats sum(bytesByHost) as totalBytes by _time
    | append [search index=index host=host object="Network Interface" counter="Current Bandwidth" instance!="isatap.*"
        | bucket _time span=1m 
        | stats avg(Value) as connsByHost by _time | stats sum(connsByHost) as CurrentBandwidth by _time ]
    | stats exact(((totalBytes*8)/CurrentBandwidth) * 100)

Error: Error in 'stats' command: The argument 'exact(((totalBytes*8)/CurrentBandwidth) * 100)' is invalid.

Any assistance would be greatly appreciated.

Re: Get Percentage of Network bandwidth

stephanefotso — Wed, 06 May 2015 17:18:26 GMT

Exact(X) is a function for Eval and Where

Try

 index=index host=host object="Network Interface" counter="Bytes Total/sec"
     | bucket _time span=1m
     | stats avg(Value) as bytesByHost by _time,host
     | stats sum(bytesByHost) as totalBytes by _time
     | append [search index=index host=host object="Network Interface" counter="Current Bandwidth" instance!="isatap.*"
         | bucket _time span=1m 
         | stats avg(Value) as connsByHost by _time | stats sum(connsByHost) as CurrentBandwidth by _time ]
     | eval total= exact(totalBytes*8/CurrentBandwidth * 100)
 | stats  values(total)

Re: Get Percentage of Network bandwidth

woodcock — Wed, 06 May 2015 17:20:58 GMT

"exact" is not a stats function:
http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/Stats

Re: Get Percentage of Network bandwidth

tysonjhayes — Wed, 06 May 2015 17:27:48 GMT

Tried your function and while I'm not getting the error anymore (thanks!) I'm not getting any data for total. It still looks like CurrentBandwidth is null.

When I run the appended search by itself I'm getting results but put it in the append I'm getting nothing...

Re: Get Percentage of Network bandwidth

tysonjhayes — Wed, 06 May 2015 17:28:09 GMT

Thanks! That's been corrected.

Re: Get Percentage of Network bandwidth

stephanefotso — Wed, 06 May 2015 17:55:38 GMT

Try this

  index=index host=host object="Network Interface" counter="Bytes Total/sec"
          | bucket _time span=1m
          | stats avg(Value) as bytesByHost by _time,host
          | stats sum(bytesByHost) as totalBytes by _time
          | append [search index=index host=host object="Network Interface" counter="Current Bandwidth" instance!="isatap.*"
              | bucket _time span=1m 
              | eventstats avg(Value) as connsByHost by _time | stats sum(connsByHost) as CurrentBandwidth by _time ]
          | eval total= exact(totalBytes*8/CurrentBandwidth * 100)
      | stats  values(total)

Re: Get Percentage of Network bandwidth

tysonjhayes — Wed, 06 May 2015 18:45:02 GMT

Still getting null or 0 on CurrentBandwidth. The query by itself is producing results though. I'm checking it by running the query in the brackets by itself (seeing the results), then I tried taking everything before the eval and doing a | table CurrentBandwidth (seeing rows with no data). Thanks for your assistance thus far!

Re: Get Percentage of Network bandwidth

stephanefotso — Wed, 06 May 2015 19:52:22 GMT

I now understand. I thing the problem should be the appen command. Change appen and try use apppencols or join. Something like this, with appendcols:

 index=index host=host object="Network Interface" counter="Bytes Total/sec"
      | bucket _time span=1m
      | stats avg(Value) as bytesByHost by _time,host
      | stats sum(bytesByHost) as totalBytes by _time
      | appendcols [search index=index host=host object="Network Interface" counter="Current Bandwidth" instance!="isatap.*"
          | bucket _time span=1m 
          | stats avg(Value) as connsByHost by _time | stats sum(connsByHost) as CurrentBandwidth by _time ]
      | eval total= exact(totalBytes*8/CurrentBandwidth * 100)
  | stats  values(total)

Re: Get Percentage of Network bandwidth

tysonjhayes — Wed, 06 May 2015 20:34:34 GMT

Brilliant! That works! Now, what is apppencols? I'm not seeing any documentation on it, or I'm missing something super obivous.

Re: Get Percentage of Network bandwidth

stephanefotso — Wed, 06 May 2015 21:01:56 GMT

here you go: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Appendcols