Splunk Search

Calculate average on two different times

aohls
Contributor

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

aohls
Contributor

@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.

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!