How to Remove Outliers - values greater than 90 percentile

[sabari80]

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

[tscroggins]

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

[sabari80]

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.