topic Re: How to separate rows as column? in Splunk Search

How to separate rows as column?

apple143 — Fri, 08 Jun 2018 22:53:35 GMT

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.

Re: How to separate rows as column?

jluo_splunk — Sat, 09 Jun 2018 00:43:51 GMT

Hi Apple143,

Does this work for you?

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

Re: How to separate rows as column?

niketn — Sat, 09 Jun 2018 15:48:01 GMT

@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

Re: How to separate rows as column?

jluo_splunk — Sun, 10 Jun 2018 19:31:48 GMT

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

Re: How to separate rows as column?

apple143 — Mon, 11 Jun 2018 01:23:52 GMT

It works! Thanks a lot!!

Re: How to separate rows as column?

apple143 — Mon, 11 Jun 2018 01:24:54 GMT

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

Re: How to separate rows as column?

apple143 — Wed, 13 Jun 2018 07:28:25 GMT

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

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

Re: How to separate rows as column?

jluo_splunk — Thu, 14 Jun 2018 03:32:24 GMT

Hi Apple143,

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