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
Legend

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
Get Updates on the Splunk Community!

Introducing Ingest Actions: Filter, Mask, Route, Repeat

WATCH NOW Ingest Actions (IA) is the best new way to easily filter, mask and route your data in Splunk® ...

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...