Reporting

Comparing Standard Deviations

achudnoff
Explorer

I'm trying to write a report that returns any report types where the 1 hour average Duration has exceed the average + 2 Standard Deviations for a running 24 hours. How can I compare these values?

I have a query, but the 1 hour average isn't showing up:

index="prd_common_events" AppCode="MMX" EventName="ReportRun" earliest = -24h latest=now| stats avg(Duration) as avg stdev(Duration) as standdev by ReportType | eval two = 2* standdev | eval avgts = avg + two | append [ search index="prd_common_events" AppCode="MMX" EventName="ReportRun" earliest=-1h latest=now | stats avg(Duration) as nowavg ]

Tags (2)
0 Karma
1 Solution

lguinn2
Legend

First, I have a question: what do you get when you run the two searches independently? That is, what happens when you run

index="prd_common_events" AppCode="MMX" EventName="ReportRun" earliest = -24h latest=now| stats avg(Duration) as avg stdev(Duration) as standdev by ReportType | eval two = 2* standdev | eval avgts = avg + two 

And what happens when you run

index="prd_common_events" AppCode="MMX" EventName="ReportRun" earliest=-1h latest=now | stats avg(Duration) as nowavg

I think you will get a number of results for the first search (one for each ReportType) and only 1 result for the second search. I think I would do it this way

index="prd_common_events" AppCode="MMX" EventName="ReportRun" earliest = -24h@h latest=@h | 
fields Duration ReportType |
stats avg(Duration) as avg stdev(Duration) as standdev by ReportType |  
eval avgts = avg + ( 2* standdev ) | 
fields ReportType avgts |
join ReportType  [ search
index="prd_common_events" AppCode="MMX" EventName="ReportRun" earliest = -1h@h latest=@h | 
fields Duration ReportType |
stats avg(Duration) as nowavg by ReportType |
fields ReportType nowavg ] |
where nowavg > avgts

Hope this works for you!

View solution in original post

lguinn2
Legend

First, I have a question: what do you get when you run the two searches independently? That is, what happens when you run

index="prd_common_events" AppCode="MMX" EventName="ReportRun" earliest = -24h latest=now| stats avg(Duration) as avg stdev(Duration) as standdev by ReportType | eval two = 2* standdev | eval avgts = avg + two 

And what happens when you run

index="prd_common_events" AppCode="MMX" EventName="ReportRun" earliest=-1h latest=now | stats avg(Duration) as nowavg

I think you will get a number of results for the first search (one for each ReportType) and only 1 result for the second search. I think I would do it this way

index="prd_common_events" AppCode="MMX" EventName="ReportRun" earliest = -24h@h latest=@h | 
fields Duration ReportType |
stats avg(Duration) as avg stdev(Duration) as standdev by ReportType |  
eval avgts = avg + ( 2* standdev ) | 
fields ReportType avgts |
join ReportType  [ search
index="prd_common_events" AppCode="MMX" EventName="ReportRun" earliest = -1h@h latest=@h | 
fields Duration ReportType |
stats avg(Duration) as nowavg by ReportType |
fields ReportType nowavg ] |
where nowavg > avgts

Hope this works for you!

Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...