topic Re: Calculate average on two different times in Splunk Search

Calculate average on two different times

aohls — Wed, 18 Sep 2019 11:57:59 GMT

I want to get a 7 day and 30 day average in a single search.
sourcetype="businessService" OR sourcetype="bpmservice-2" JobName | eval host = lower(host) | lookup client-mapping.csv hostname as host OUTPUT clientename as clientName | transaction unifyends=true GUID maxspan=12h | eval duration=round(duration/60, 2) | lookup JobStatsData.csv codeName as codeName,JobName as JobName OUTPUT avgRunTimeMinutes as avgRunTimeMinutes, stdDevDuration as stdDevDuration, jobHasFTPDownload as FTPDownload | join JobName [search sourcetype="businessService" JobName earliest=-7d@d | eval host = lower(host) | lookup client-mapping.csv hostname as host OUTPUT clientname as clientName | transaction unifyends=true GUID maxspan=12h | eval duration=round(duration/60, 2) | stats avg(duration) as AverageRunTime7Days by JobName | fields + AverageRunTime7Days] | stats avg(duration) as AverageRunTime30Days, avg(avgRunTimeMinutes) as historicalRunTime, avg(stdDevDuration) as historicalStdDev,values(FTPDownload) as FTPDownload by JobName | eval OneStdDevOfAvg = historicalRunTime + (historicalStdDev) | eval Action = if(AverageRunTime30Days > OneStdDevOfAvg, if(FTPDownload=="Yes","Potential Review Needed","Review Needed"), "No Issues") | rename JobName as BPMJob | table BPMJob,FTPDownload, AverageRunTime30Days, AverageRunTime7Days, OneStdDevOfAvg, Action

I have a lookup with historical statistics data and I want to compare that to a 30 day and 7 day run time. I am not getting any results right now; but if I remove the above code I get the 30 day average. I want to pass the 7 day average and JobName into my main search, so I can include it in my table output.

Re: Calculate average on two different times

gcusello — Wed, 18 Sep 2019 12:26:47 GMT

Hi aohls,
At first, I suggest to insert always index=my_index in your searches because they are faster!

Then, there's a limit of 50,000 results in subsearches, are you sure that you don't exceed this limit in this case?

Then, join command isn't a very performant command, in addition you have also a transaction in subsearch, this means that probably you have a very very slow search.
Could you share the full search to try to rebuild it?

Bye.
Giuseppe

Re: Calculate average on two different times

aohls — Wed, 18 Sep 2019 12:34:10 GMT

@gcusello I added my full search. I also noticed I had a typo in why the search did not generate. I now have it working but, I am not getting data back for my 7 day average in the subsearch. I am also unsure of if there is a better way to do this.

Re: Calculate average on two different times

woodcock — Sat, 26 Oct 2019 00:57:19 GMT

Like this:

index="YouShouldAlwaysSpeciryAnIndex" AND (sourcetype="businessService" OR sourcetype="bpmservice-2") AND JobName  earliest=-30d
| eval host = lower(host) 
| lookup client-mapping.csv hostname AS host OUTPUT clientename AS clientName
| stats list(*) AS * count range(_time) AS duration BY GUID
| eval duration=round(duration/60, 2) 
| lookup JobStatsData.csv codeName AS codeName, JobName AS JobName OUTPUT avgRunTimeMinutes AS avgRunTimeMinutes, stdDevDuration AS stdDevDuration, jobHasFTPDownload AS FTPDownload 
| eval which=if(_time>=relative_time(now(), "-7d@d"), mvappend("last7days", "last30days"), "last30days")
| stats avg(duration) as AverageRunTime30Days, avg(avgRunTimeMinutes) as historicalRunTime, avg(stdDevDuration) as historicalStdDev,values(FTPDownload) as FTPDownload by JobName which

You will have to do some more stuff after this, but this solves the essence of your problem.