Splunk Search

Is it possible to display a table (similar to Excel's pivot table) with multiple values under one column?

spammenot66
Contributor

In Splunk, is there a way to format data that normally contains
user, month-year, hits, clicks to display multiple values per column as seen in the screen shot below.

for example, if my data is:

User    Month/Year  Hits    Clicks  
user1   2017-01   1     2   
user1   2017-02   3     4   
user1   2017-03   5     6   
user1   2017-04   7     8   
user2   2017-01   9     10  
user2   2017-02   11       12   
user2   2017-03   1     2   
user2   2017-04   4     6   

I would like to have it automatically display Total Clicks and Total Hits per month/year (as columns containing hits and clicks) for each user (with user being displayed per row)

alt text

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Splunk's table visualization doesn't support multi headers, so this may be the best workaround

your current search giving fields User,"Month/Year","Hits","Clicks" 
| chart sum(Hits) as "Total Hits", sum(Clicks) as "Total Clicks" over User by "Month/Year"

Output columns will be like

User  , Total Hits: 2017-01  , Total Clicks: 2017-01 , Total Hits: 2017-02  , Total Clicks: 2017-02....

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Splunk's table visualization doesn't support multi headers, so this may be the best workaround

your current search giving fields User,"Month/Year","Hits","Clicks" 
| chart sum(Hits) as "Total Hits", sum(Clicks) as "Total Clicks" over User by "Month/Year"

Output columns will be like

User  , Total Hits: 2017-01  , Total Clicks: 2017-01 , Total Hits: 2017-02  , Total Clicks: 2017-02....
0 Karma

spammenot66
Contributor

Using this method is not like the Excel table which has the time period (month/year) neatly over the two fields (total hits and clicks) directly under it. The SPLUNK output is a bit cumbersome as seen in my example below where i try to output a full year. .
alt text

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...