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

niketnilay
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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!