Reporting

How to make Pivot-Like Transposed Table?

robettinger
Explorer

Hi,

I have the following search and table:

index=xxxx 
| bucket _time span=1h
| stats count as Access_Count by Account_Name, _time


Account_Name     _time                    Access_Count
abc              2018-01-30 08:00             10
def              2018-01-30 08:00              20
def              2018-01-30 09:00              40
ghi              2018-01-30 08:00              30


Is there a way to transpose this table to fit the following format?

Account_Name    2018-01-30 08:00   2018-01-30 09:00
abc                     10                   0
def                     20                  40
ghi                     0                    30

I tried the transpose command but it only shows one header.

Thank you.
R

Tags (3)
0 Karma

niketn
Legend

You can use the transpose command. However, you would need to convert epoch time to string time first.

<YourBaseSearch> 
| timechart span=1h count as Access_Count by Account_Name
| eval _time=strftime(_time,"%Y/%m/%d %H:%M:%S")
| transpose 0 header_field=_time column_name="Account_Name"
| search Account_Name!="_*"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...

Splunk AppDynamics Agents Webinar Series

Mark your calendars! On June 24th at 12PM PST, we’re going live with the second session of our Splunk ...

SplunkTrust Application Period is Officially OPEN!

It's that time, folks! The application/nomination period for the 2025 SplunkTrust is officially open! If you ...