Splunk Search

Average and Diff per host

GadgetGeek
Path Finder

Given I have multiple hosts, I'd like the host total within a bucketed time span, average of the totals across all hosts, and the percentage difference per host compared to the average. E.g.

For:
index=main "processed" | bucket _time span=5m | rex "processed\s(?[\d]+)\smessages" | eval processed=tonumber(NumProcessed) | stats sum(processed) by _time host

Giving:
_time | host1 | host2 | host3
2018-05-24 08:00:00 | 99 | 101 | 100
2018-05-24 08:05:00 | 100| 99| 101

I'd like to see:

_time               | host1 | host2 | host3 | Average | host1diff% | host2diff% | host3diff%
2018-05-24 08:00:00 | 99    | 101   | 100   | 100     | -x%        | +y%        | 0.0%
2018-05-24 08:05:00 | 100   | 99    | 101   | 100     | 0.0%       | -x%        | +y%

(whatever x and y would be...and the number of host diff columns will depend on the number of hosts returned in the search)

0 Karma
1 Solution

niketn
Legend

@GadgetGeek, as per the details and sample data, please try the following run anywhere search. The command from | makeresults till | fields - data generate dummy data as per question. It uses foreach command to iterate through host columns to get count of hosts and calculate their difference %. The addtotals command is used to get the Total of Hosts for calculating average. Remaining part of the search can be plugged in to your existing search.

| makeresults 
| eval data="2018-05-24 08:00:00|99|101|100;2018-05-24 08:05:00|100|99|101" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="|" 
| eval _time=strptime(mvindex(data,0),"%Y-%m-%d %H:%M:%S"), host1=mvindex(data,1), host2=mvindex(data,2), host3=mvindex(data,3) 
| fields - data 
| rename "*" as "count*"
| rename "count_*" as "_*"
| addtotals row=t col=f labelfield=Total
| eval HostCount=0 
| foreach count* 
    [ eval HostCount=HostCount+1]
| eval Average=round(Total/HostCount,0) 
| foreach count* 
    [ eval "diff%<<MATCHSTR>>"=round(((Average-'<<FIELD>>')/Average)*100,0)]
| table _time "count*" "diff*"

Please try out and confirm!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@GadgetGeek, as per the details and sample data, please try the following run anywhere search. The command from | makeresults till | fields - data generate dummy data as per question. It uses foreach command to iterate through host columns to get count of hosts and calculate their difference %. The addtotals command is used to get the Total of Hosts for calculating average. Remaining part of the search can be plugged in to your existing search.

| makeresults 
| eval data="2018-05-24 08:00:00|99|101|100;2018-05-24 08:05:00|100|99|101" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="|" 
| eval _time=strptime(mvindex(data,0),"%Y-%m-%d %H:%M:%S"), host1=mvindex(data,1), host2=mvindex(data,2), host3=mvindex(data,3) 
| fields - data 
| rename "*" as "count*"
| rename "count_*" as "_*"
| addtotals row=t col=f labelfield=Total
| eval HostCount=0 
| foreach count* 
    [ eval HostCount=HostCount+1]
| eval Average=round(Total/HostCount,0) 
| foreach count* 
    [ eval "diff%<<MATCHSTR>>"=round(((Average-'<<FIELD>>')/Average)*100,0)]
| table _time "count*" "diff*"

Please try out and confirm!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

GadgetGeek
Path Finder

My original statement wasn't quite right. The output from the stated query gives:

_time | host | sum(processed)
2018-05-24 08:00:00 | host1 | 99
2018-05-24 08:00:00 | host2 | 100

I should have put the following instead of the 'stats' command:

| chart sum(processed) over _time by host

The desired output IS as stated:
_time | host1 | host2 | host3 | Average | host1diff% | host2diff% | host3diff%

From your response, taking everything from (and including) "| fields - data" and adding 'Average' to the results seems to work.

  1. What does the '| fields - data' do, and should I include it?
  2. If one of the hosts has no events in the time bucket, zero is not assumed - giving blank entries and no diff %, how can I get this to work?
0 Karma

niketn
Legend

1) Yes fields - data was only for my run anywhere example not for your search. You can remove that.

2) Add following command after timechart command:

| fillnull value=0
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

GadgetGeek
Path Finder

Brilliant thanks.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...