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

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
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 ...