Splunk Search

How to separate rows as column?

apple143
Engager

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.

0 Karma
1 Solution

jluo_splunk
Splunk Employee
Splunk Employee

Hi Apple143,

Does this work for you?

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

View solution in original post

0 Karma

jluo_splunk
Splunk Employee
Splunk Employee

Hi Apple143,

Does this work for you?

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

apple143
Engager

It works! Thanks a lot!!

0 Karma

apple143
Engager

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

0 Karma

jluo_splunk
Splunk Employee
Splunk Employee

Hi Apple143,

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

0 Karma

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

apple143
Engager

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

0 Karma

jluo_splunk
Splunk Employee
Splunk Employee

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...