How to achieve 90 perccentile and average on the same query?

[sabari80]

How to calculate 90 percentile and average on the same query. following query is not providing 90 percentile values 

index="dynatrace" sourcetype="dynatrace:usersession" | spath output=pp_user_action_user path=userId
| 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="xxxx"
| spath output=pp_user_action_key input=user_actions path=keyUserAction
| where pp_user_action_key="true"| spath output=pp_user_action_name input=user_actions path=name | spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime | eval pp_user_action_name=substr(pp_user_action_name,0,150)
| eventstats perc90(pp_user_action_response) AS "90perc_User_Action_Response" by pp_user_action_name
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "Avg_User_Action_Response" by pp_user_action_name
| eval Avg_User_Action_Response=round(Avg_User_Action_Response,0)
```| eval 90perc_User_Action_Response=round(90perc_User_Action_Response,0) ```
| table pp_user_action_name,Total_Calls,Avg_User_Action_Response,90perc_User_Action_Response

[yuanliu]

The question is very confusing.  Forget the rest of search.  Have you separately tested

| stats perc90(pp_user_action_response) AS "90perc_User_Action_Response" by pp_user_action_name
``` replace eventstats with stats to make output more obvious ```

with this search

index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=pp_user_action_user path=userId
| 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="xxxx"
| spath output=pp_user_action_key input=user_actions path=keyUserAction
| where pp_user_action_key="true"
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| eval pp_user_action_name=substr(pp_user_action_name,0,150)

and see if "90perc_User_Action_Response" is correct for some pp_user_action_name that you can easily calculate by hand?

[sabari80]

yes one at a time is working. if i use just 90perc,i am getting 90% value. 

[yuanliu · Accepted solution]

In that case, Splunk is working as expected.  But your next search negates all that eventstats have done.

| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "Avg_User_Action_Response" by pp_user_action_name

Nowhere in this search does "90perc_User_Action_Response" appear.   The above can only give you three fields:  "Total Calls", "Avg_User_Action_Response", and pp_user_action_name.

You have the right idea to use eventsearch.  I think you meant to write

index="dynatrace" sourcetype="dynatrace:usersession" | spath output=pp_user_action_user path=userId
| 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="xxxx"
| spath output=pp_user_action_key input=user_actions path=keyUserAction
| where pp_user_action_key="true"| spath output=pp_user_action_name input=user_actions path=name | spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime | eval pp_user_action_name=substr(pp_user_action_name,0,150)
| eventstats perc90(pp_user_action_response) AS "90perc_User_Action_Response" by pp_user_action_name
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "Avg_User_Action_Response" by pp_user_action_name 90perc_User_Action_Response
| eval Avg_User_Action_Response=round(Avg_User_Action_Response,0)
```| eval 90perc_User_Action_Response=round(90perc_User_Action_Response,0) ```
| table pp_user_action_name,Total_Calls,Avg_User_Action_Response,90perc_User_Action_Response

[sabari80]

its working now.... thanks 

[sabari80]

Still 90 percentile value is coming as null