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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...