Reporting

How to make Pivot-Like Transposed Table?

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!