Calculate average on two different times

[aohls]

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.

[woodcock]

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.

[gcusello]

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

[aohls]

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