How do I get separate count for one field and then show total count in the other column

[mehwishw]

Hi : I want a table something like :

API Code Count Total

API1 404 2 11

500 3
303 6
API2 404 4 14
500 10

my query is :

index=apigee sourcetype=facade channelId=* responseStatusCode>"399" | eval API=case(match(Referer,"./abc$") , "API1" , match(Referer,"./xyz$") , "API2" , match(Referer,".*/uvw$") , "API3") , response_failed=case(responseStatusCode>"399","failed")|stats values(responseStatusCode) as "Code" , count(responseStatusCode) as "Count",count(response_failed) as "Total" by API

I want separate count for values of responsetatusCode.

but currently I am getting :

API Code Count Total
API1 404 11 11
500

303

API2 404 14 14
500

[sundareshr]

See if this works for you

index=apigee sourcetype=facade channelId= responseStatusCode>"399" 
| eval API=case(match(Referer,"./abc$") , "API1" , match(Referer,"./xyz$") , "API2" , match(Referer,"./uvw$") , "API3") , response_failed=case(responseStatusCode>"399","failed")
| eventstats count as Total by API
| stats count max(Total) as Total by API Code 
| stats values(Code) as Code values(count) as Count values(Total) as Total by API
| table API Code Count Total

[mehwishw]

I have changed a bit of your solution and now my code looks like :

index=apigee sourcetype=facade channelId= responseStatusCode>"399"
| eval API=case(match(Referer,"./abc$") , "API1" , match(Referer,"./xyz$") , "API2" , match(Referer,"./uvw$") , "API3") , response_failed=case(responseStatusCode>"399","failed")
|stats count(responseStatusCode) as "rescount" by API responseStatusCode | stats values(responseStatusCode) as "Code",count(response_failed) as "Total",values(rescount) as "Count" by API | table API Code Count Total

I am getting the separate count but getting 0 in the total tab

[mehwishw]

I am getting the desired value now here is my query :

index=apigee sourcetype=facade channelId= responseStatusCode>"399"
| eval API=case(match(Referer,"./abc$") , "API1" , match(Referer,"./xyz$") , "API2" , match(Referer,"./uvw$") , "API3") , response_failed=case(responseStatusCode>"399","failed")
stats count as "rescount" by API responseStatusCode
| stats values(responseStatusCode) as "Code",values(rescount) as "Count", sum(rescount) as "Total" by API

Thanks for the help 🙂

[renjith_nair]

Try this

index=apigee sourcetype=facade channelId= responseStatusCode>"399" | eval API=case(match(Referer,"./abc$") , "API1" , match(Referer,"./xyz$") , "API2" , match(Referer,"./uvw$") , "API3") , response_failed=case(responseStatusCode>"399","failed")|stats  count as "Count",count(response_failed) as "failedTotal" by API,responseStatusCode|eventstats sum(failedTotal) by API|fields - failedTotal

You might need to tune a bit,sorry no system to test at the moment

[mehwishw]

I am getting the desired value now here is my query :

index=apigee sourcetype=facade channelId= responseStatusCode>"399"
| eval API=case(match(Referer,"./abc$") , "API1" , match(Referer,"./xyz$") , "API2" , match(Referer,"./uvw$") , "API3") , response_failed=case(responseStatusCode>"399","failed")
stats count as "rescount" by API responseStatusCode
| stats values(responseStatusCode) as "Code",values(rescount) as "Count", sum(rescount) as "Total" by API

Thanks for the help 🙂