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

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!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...