Splunk Search

Breakdown _time into date and time and then transpose

ndaniel88
Explorer

Hello,

I have a table like this:

+---------------------+-------+
| _time | value |
+---------------------+-------+
| 2/11/2019 13:00 | 5 |

| 2/11/2019 14:00 | 4 |

| 2/11/2019 15:00 | 2 |
| 2/12/2019 13:00 | 3 |
| 2/12/2019 14:00 | 2 |
| 2/12/2019 15:00 | 1 |
| 2/13/2019 13:00 | 7 |
| 2/13/2019 14:00 | 6 |
| 2/13/2019 15:00 | 9 |
+------------------+---+-------+

And I need to transpose in something like this

+-------+-----------+-----------+-----------+
| _time | 2/11/19 | 2/12/19 | 2/13/19 |
+-------+-----------+-----------+-----------+
| 13:00 | 5 | 3 | 7 |
| 14:00 | 4 | 2 | 6 |
| 15:00 | 2 | 1 | 9 |
+-------+-----------+-----------+-----------+

I was able to transpose the row values as columns using:

| transpose 0 header_field=days

(days is a substr containing the date), but I don't have an idea on how to build the rest of the table. Any help is much appreciated.

Thanks in advance.

Tags (1)
0 Karma
1 Solution

vnravikumar
Champion

Hi @ndaniel88

Try like and let me know

index=_internal 
| timechart span=1h count 
| eval date=strftime(_time,"%m/%d/%Y") 
| eval time=strftime(_time,"%H:%M") 
| xyseries time date count

View solution in original post

vnravikumar
Champion

Hi @ndaniel88

Try like and let me know

index=_internal 
| timechart span=1h count 
| eval date=strftime(_time,"%m/%d/%Y") 
| eval time=strftime(_time,"%H:%M") 
| xyseries time date count

View solution in original post

ndaniel88
Explorer

Thanks! it works 🙂

xyseries is an interesting command, i had never used it before.

0 Karma

Vijeta
Influencer

@ndaniel88 Try this

index=<your index>| eval Date=strftime(_time,"%m/%d/%Y")|eval Time=strftime(_time,"%H:%M:%S")
|chart limit=0 values(value) over Date by Time

ndaniel88
Explorer

Thank you so much!, I just need to switch to "over Time by Date" and it works perfectly! 🙂

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.