How to separate rows as column?

[apple143]

I have trouble in manipulating the table

Date contains (index, name, date).

name ..... date ................ count
a ............ 2018-06-07 ..... 500
a ............ 2018-06-08 ..... 600
b ............ 2018-06-07 ..... 700
b ............ 2018-06-08 ..... 800
c ............ 2018-06-07 ..... 900
c ............ 2018-06-08 ..... 1000

I want to make this table to below form

name ........ day1 ........ day2
a ............... 500 .......... 600
b ............... 700 .......... 800
c ............... 900 .......... 1000

or it doesn't matter if I can make below table directly(using tstats)

I have to use tstats. I already made an Alert that could show table like second table.
But, It takes too much time so I want to change search command using tstats

And here is the search query that I used when I made first table
| tstats count where index=* by name, _time span=1d)

How can I do?
Somebody help me please.

[jluo_splunk · Accepted solution]

Hi Apple143,

Does this work for you?

| tstats count where index=* by name, _time span=1d prestats=true
| chart count by name, _time

[apple143]

It works! Thanks a lot!!

[apple143]

Can I ask one more?
What if I want to 1 more field?
Like..
A-------xx-------06/07-------100
A-------xx-------06/08-------200
A-------yy-------06/07-------300
A-------yy-------06/08-------400
B-------xx-------06/07-------500
B-------xx-------06/08-------600

to

A-------xx-------100-------200
A-------yy-------300-------400
B-------xx-------500-------600

[jluo_splunk]

Hi Apple143,

Would you mind posting this as a new question since it is a separate question?

[niketn]

@jluo, small correction _time needs to be converted from epoch time to Epoch time of format YYYY-MM-DD

 | tstats count where index=* by name, _time span=1d prestats=true
 | eval Time=strftime(_time,"%Y/%m/%d")
 | chart count by name, Time

[apple143]

I checked it. Your correction makes it easier. Thank you!

[jluo_splunk]

Nice catch, Niketnilay 🙂 @apple143, if this works for you, can you accept the answer?