How to append tp99 as a new column in the existing query?

[VikhyathMaiya]

Hello Splunk community. I have a query that is running currently as shown below:

index=myIndex* api.metaData.pid="my_plugin_id"
| rename api.p as apiName
| chart count BY apiName "api.metaData.status"
| multikv forceheader=1 
| table apiName success error NULL 
| eval line=printf("%-85s% 10s% 10s% 7s",apiName, success, error, NULL) 
| stats list(line) as line 
| eval headers=printf("%-85s% 10s% 10s% 7s","API Name","Success","Error", "NULL") 
| eval line=mvappend(headers,line) 
| fields - headers Which displays a table with "API Name","Success","Error", "NULL" counts.

This works as expected. Now i want to add a new column in the table which displays the latency value (tp95 and tp99) for each apiName . The time taken by each api is in the field api.metadata.tt. How can i achieve this ?
I am new to splunk and I am literally stuck at this point. Could someone please help me. Thank you 🙂

Info: Just to let you guys know, my query has these additional logic to format things because of related question here

[ITWhisperer · Accepted solution]

Assuming you want the p95 for the whole apiName, not by the apiName and status, you need to calculate p95 before you chart and pass the value through in the apiName, then split it off again.

| makeresults count=100 
| eval apiName="Api ".(random()%3+1)
| eval result=mvindex(split("success|error","|"),random()%3)
| eval tt=random()%20+1
| eventstats p95(tt) as tp95 by apiName
| eval apiName=apiName."|".round(tp95,2)
| chart count by apiName result
| eval tp95=mvindex(split(apiName,"|"),1)
| eval apiName=mvindex(split(apiName,"|"),0)
| eval line=printf("%-85s% 10s% 10s% 7s% 8s",apiName, success, error, NULL, tp95) 
| stats list(line) as line 
| eval headers=printf("%-85s% 10s% 10s% 7s% 8s","API Name","Success","Error", "NULL", "TP95") 
| eval line=mvappend(headers,line) 
| fields - headers

[VikhyathMaiya]

When i use eventstats command as you mentioned above, the query doesn't return any results. This is the query i am running

index=aws* api.metaData.pid="aws-*" api.p!=phs-*
| rename api.p as apiName
| eventstats p95(api.measures.tt) as RT_p95 by apiName
| eval RT_p95 = round(RT_p95/1000,2)
| eval apiName=apiName."|".RT_p95
| chart count BY apiName "api.metaData.status"

But if i change eventstats to stats or chart the result is displayed something like below

apiName                     NULL
Test1|0.34                      1
Test2|0.12                      1
Test3|0.24                      1

The second column is always null and count is 1.

What am i missing here . Why is it not working with eventstats.

PS: The example you mentioned works perfectly. I am not sure why my query is failing.

[ITWhisperer]

If api.measures.tt doesn't exist (or is misspelt), you won't get any results

You need to use eventstats not stats or chart so that the p95 can be added to all the events before the chart.

[VikhyathMaiya]

Even after excluding null values the table is not showing up. I ended up combing the charts as suggested here . You have been a great help. Thanks for your support. 🙂