eval case condition

[AdixitSplunk]

HI ,
I have this query where i want my data in a specific format .
Here under each POD there are some 3-4 hosts ,whose total event count is 5 ...(highlighted)
Base query|stats count by host|addcoltotals

Similarly for POD 2 i have some different set of hosts whose counts is 10

Expected output:

Message POD1 POD2 Total
XYZ ........ 5.............10.......... 15

I used below query:
index="River" sourcetype=river_logs host="XYZ" OR "host="ABC" OR host="LM" OR "host="NOP" Message="*" |eval host=upper(host)|eval env=case( host=="XYZ" OR "host="ABC","POD1",host=="LM" OR "host=="NOP","POD2",1==1,"NOT MATCHED")|stats count by host env | chart values(count) over host by env

Which is giving result as :
host POD1....POD2
XYZ 2
LM ...............5
ABC 3

NOP ............5

WHICH IS NO THE expected format of result .(". "are nothing but the spaces to show how exactly result is popping)
Please help me with this one

[niketn]

Is your expected output is host being first column or Message being first column?

I am not sure why have you used two statistics i.e. stats and chart when you are trying to just perform count.

Following should give you the results you are expecting.

<your base search>
| chart count by host env
| addtotals row=t col=f 

PS:
1) If you want to reverse Column/Row split just reverse the by sequence i.e. chart count by env host
2) If you want to show column totals also then use change from col=f to col=t labelfield=host

[AdixitSplunk]

Thank you it helped a lot .

[AdixitSplunk]

I have just used .....chart count by env |addcolstotals |fillnull value="Total" env
Its actually giving result as:

env Count
POD1 5
POD2 2
POD3 3
Total 10
I want it to be like
POD1 POD2 POD3 Total
5 2 3 10
I used transpose command but its giving result like:
column row1 row2 row 3
env POD1 POD2 POD3
Count 5 2 3

Is it possible to make "POD1" POD2 as table header instead of row 1, row 2 etc.
Please help me with this .