How to change the row value to column value ?

[JyotiP]

I have the following query

host=*localTest* sourcetype="perf" Path "/api/*/" cache="MISS" OR cache="HIT" 
| stats count by Path,cache 

And the output of the above query is

|--------------------------------------------------------------------------|
|Path                                              |cache   |count     |
|--------------------------------------------------------------------------|
|/api/trigger/v1/firmroute                        |MISS  |251         |
|/api/trigger/v1/route/                          |MISS   |6           |
|/api/testdata/basket/v1/baskets                    |HIT      |3           |
|/api/testdata/basket/v1/baskets                        |MISS   |6         |
|/api/testdata/reference/v1/CounterPartyAccount         |MISS   |364       |
|/api/testdata/reference/v1/CounterpartyMasterAccount   |MISS   |364       |
|/api/testdata/v1/graphql                               |MISS   |4350      |
|/api/testdata/v2/graphql                               |MISS   |1117      |
|/api/infrastructure/statuscloud/v1/status              |HIT    |6         |
|/api/infrastructure/statuscloud/v1/status              |MISS   |2         |
|/api/ipa/v1/settings/trading                           |MISS   |14        |
|/api/ipa/v1/settings/trading/                          |MISS   |2         |
|/api/session/v1/Health                                 |MISS   |2872      |
|/api/session/v1/alert                                  |MISS   |228       |
|/api/session/v1/health                                 |MISS   |2484      |
|/api/session/v1/session                                |HIT    |423003    |
|/api/session/v1/session                                |MISS   |516       |
|--------------------------------------------------------------------------|

But I want my output like

  --------------------------------------------------------------------------
Path                                                    MISS     HIT      
--------------------------------------------------------------------------
/api/trigger/v1/firmroute                               251           
/api/trigger/v1/route/                                  6                 
/api/testdata/basket/v1/baskets                         6       3            
/api/testdata/reference/v1/CounterPartyAccount          364              
/api/testdata/reference/v1/CounterpartyMasterAccount    364              
/api/testdata/v1/graphql                                4350             
/api/testdata/v2/graphql                                1117             
/api/infrastructure/statuscloud/v1/status               2       6         
/api/ipa/v1/settings/trading                            14               
/api/ipa/v1/settings/trading/                           2                
/api/session/v1/Health                                  2872             
/api/session/v1/alert                                   228              
/api/session/v1/health                                  2484             
/api/session/v1/session                                 516     423003         
--------------------------------------------------------------------------

[skrajkumar_splu · Accepted solution]

Try using chart instead of stats. like

host=localTest sourcetype="perf" Path "/api/*/" cache="MISS" OR cache="HIT" | chart count over cache by Path

[JyotiP]

@skrajkumar_splunk thanks for sharing the query, I have just modified the positions and it fulfills my requirement,
host=localTest sourcetype="perf" Path "/api/*/" cache="MISS" OR cache="HIT" | chart count over Path by cache

Or

host=localTest sourcetype="perf" Path "/api/*/" cache="MISS" OR cache="HIT" | stats count(eval(cache="MISS")) AS MISS count(eval(cache="HIT")) AS HIT by Path