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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...