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!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...