Splunk Search

How to Remove Outliers - values greater than 90 percentile

sabari80
Explorer

Looking help to remove outliers (values greater than 90 percentile responses). For Ex: 

Response Time 

--------------------

1 Second

2 Seconds

3 Seconds

4 Seconds

5 Seconds

6 Seconds

7 Seconds

8 Seconds

9 Seconds

10 Seconds

90 percentile for the above values is 9 Seconds. want to remove the outlier 10 Seconds and get the average response for remaining values. My expected Avg Response (after Removing the outlier) = 5 Seconds

====================================================

My Query is 

index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}"
| stats count by user_actions
| spath output=pp_user_action_application input=user_actions path=application
| where pp_user_action_application="******" | spath output=User_Action_Name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| eval User_Action_Name=substr(User_Action_Name,0,150)
| eventstats avg(pp_user_action_response) AS "Avg_Response" by Proper_User_Action
| stats count(pp_user_action_response) As "Total_Calls",perc90(pp_user_action_response) AS "Perc90_Response" by User_Action_Name Avg_Response
| eval Perc90_Response=round(Perc90_Response,0)/1000
| eval Avg_Response=round(Avg_Response,0)/1000
| table Proper_User_Action,Total_Calls,Perc90_Response
Labels (3)
0 Karma

tscroggins
Influencer

Hi @sabari80,

Performance will vary with the size of the result set, but you can use eventstats and where to remove outlier events based on percentile:

| eventstats p90(pp_user_action_response) as p90_pp_user_action_response
| where pp_user_action_response<=p90_pp_user_action_response

The placement of the commands depends on how you want to calculate the average response time. To calculate the average response time for all requests below or equal to the 90th percentile, try this (untested):

index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}.visuallyCompleteTime"
| spath output=pp_user_action_application input=user_actions path=application
| where pp_user_action_application="******"
| spath output=user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| eval proper_user_action=substr(user_action_name, 0, 150) ``` did you mean to extract the "proper" user action here? ```
| eventstats p90(pp_user_action_response) as p90_pp_user_action_response by proper_user_action
| where pp_user_action_response<=p90_pp_user_action_response
| stats count as total_calls avg(pp_user_action_response) as avg_pp_user_action_response values(p90_pp_user_action_response) as p90_pp_user_action_response by proper_user_action
0 Karma

sabari80
Explorer

This is nice idea. I have come up with this query for 2 different time frames. Its retrieving/calculating the data for shorter timeframes (Ex: up to 3hours range). But for longer time frame, getting partial data for the fields 'p90Avg_PageRenderingTime' or 'p90Avg_PageRenderingTime1'. PFA image. 

index="dynatrace" sourcetype="dynatrace:usersession" earliest=-50h@h latest=-46h@h
| spath output=user_actions path="userActions{}"
| stats count by user_actions
| spath output=pp_user_action_application input=user_actions path=application
| where pp_user_action_application="*****"
| spath output=pp_user_action_name input=user_actions path=name
| where pp_user_action_name in ("")
| eval pp_user_action_name=substr(pp_user_action_name,0,60)
| spath output=pp_user_action_response_VCT input=user_actions path=visuallyCompleteTime
| stats count(pp_user_action_response_VCT) As "Count",avg(pp_user_action_response_VCT) AS "Avg_PageRenderingTime" by pp_user_action_name
| join type=left
[search index="dynatrace" sourcetype="dynatrace:usersession" earliest=-50h@h latest=-46h@h
| spath output=user_actions path="userActions{}"
| stats count by user_actions
| spath output=pp_user_action_application input=user_actions path=application
| where pp_user_action_application="*****"
| spath output=pp_user_action_name input=user_actions path=name
| where pp_user_action_name in ("")
| eval pp_user_action_name=substr(pp_user_action_name,0,60)
| spath output=pp_user_action_response_VCT input=user_actions path=visuallyCompleteTime
| eventstats p90(pp_user_action_response_VCT) AS "p90_PageRenderingTime" by pp_user_action_name
| where pp_user_action_response_VCT<=p90_PageRenderingTime
| stats count(pp_user_action_response_VCT) As "Count1",avg(pp_user_action_response_VCT) AS "p90Avg_PageRenderingTime" values(p90_PageRenderingTime) by pp_user_action_name
]
| join type=left
[search index="dynatrace" sourcetype="dynatrace:usersession" earliest=-74h@h latest=-70h@h
| spath output=user_actions path="userActions{}"
| stats count by user_actions
| spath output=pp_user_action_application input=user_actions path=application
| where pp_user_action_application="*****"
| spath output=pp_user_action_name input=user_actions path=name
| where pp_user_action_name in ("")
| eval pp_user_action_name=substr(pp_user_action_name,0,60)
| spath output=pp_user_action_response_VCT input=user_actions path=visuallyCompleteTime
| stats count(pp_user_action_response_VCT) As "Count2",avg(pp_user_action_response_VCT) AS "Avg_PageRenderingTime1" by pp_user_action_name ]
| join type=left
[search index="dynatrace" sourcetype="dynatrace:usersession" earliest=-74h@h latest=-70h@h
| spath output=user_actions path="userActions{}"
| stats count by user_actions
| spath output=pp_user_action_application input=user_actions path=application
| where pp_user_action_application="*****"
| spath output=pp_user_action_name input=user_actions path=name
| where pp_user_action_name in ("")
| eval pp_user_action_name=substr(pp_user_action_name,0,60)
| spath output=pp_user_action_response_VCT input=user_actions path=visuallyCompleteTime
| eventstats p90(pp_user_action_response_VCT) AS "p90_PageRenderingTime1" by pp_user_action_name
| where pp_user_action_response_VCT<=p90_PageRenderingTime1
| stats count(pp_user_action_response_VCT) As "Count3",avg(pp_user_action_response_VCT) AS "p90Avg_PageRenderingTime1" values(p90_PageRenderingTime1) by pp_user_action_name ]
| eval Avg_PageRenderingTime=round(Avg_PageRenderingTime,0)/1000
| eval p90Avg_PageRenderingTime=round(p90Avg_PageRenderingTime,0)/1000
| eval Avg_PageRenderingTime1=round(Avg_PageRenderingTime1,0)/1000
| eval p90Avg_PageRenderingTime1=round(p90Avg_PageRenderingTime1,0)/1000
| table pp_user_action_name, Count,Avg_PageRenderingTime,p90Avg_PageRenderingTime,Count2,Avg_PageRenderingTime1,p90Avg_PageRenderingTime1

Any suggestions? 

thanks in advance. 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...