Splunk Search

Excel Like Pivot Table

jperry_intact
New Member

I'm trying to figure out how to build an excel-like pivot table using 3 or more columns. As example, I have this data:

customer1   project1    note1
customer1   project2    
customer1   project3    note2
customer2   project4    note3
customer2   project5    
customer2   project6    note4
customer2   project7    note5
customer3   project8    

And I want to build a report that looks like this:

customer1   project1    note1
             project2   
             project3   note2
customer2   project4    note3
             project5   
             project6   note4
             project7   note5
customer3   project8    

I've been playing with stats list() and values() and both lose the relationship between the second and third columns.

| makeresults 1 
| eval pickle="customer1,project1,note1|customer1,project2,|customer1,project3,note2|customer2,project4,note3|customer2,project5,|customer2,project6,note4|customer2,project7,note5|customer3,project8"
| eval pickle=split(pickle,"|")
| mvexpand pickle
| eval pickle=split(pickle,",")
| eval customer=mvindex(pickle,0)
| eval project=mvindex(pickle,1)
| eval note=mvindex(pickle,2)
| fields - _time pickle
| stats list(project) as project list(note) as note by customer

alt text

Notice the highlighted notes fall under the wrong project. I get the same behavior with stats values().

Is there a way to go about getting a multi column relational list without repeating the customer name in the output?

Any ideas anyone has are greatly appreciated!

0 Karma
1 Solution

FrankVl
Ultra Champion

I don't think what you are looking for is possible in Splunk in that way. You either have individual lines for each entry, with customer repeated on each line, or you have aggregated results, where there is 1 line per customer.

Of course you can cheat, by simply emptying the customer field for each non-first row per customer, by adding the following below your search.

 | streamstats count by customer
 | eval customer=if(count=1,customer,"")
 | fields - count

But that does mean you loose the customer field value for those rows, so if you want to do this because you like the way that looks in a report: do it all the way at the end of your search. And it will mean that if someone who opens the report starts sorting the customer column differently, things will also get messed up.

View solution in original post

0 Karma

FrankVl
Ultra Champion

I don't think what you are looking for is possible in Splunk in that way. You either have individual lines for each entry, with customer repeated on each line, or you have aggregated results, where there is 1 line per customer.

Of course you can cheat, by simply emptying the customer field for each non-first row per customer, by adding the following below your search.

 | streamstats count by customer
 | eval customer=if(count=1,customer,"")
 | fields - count

But that does mean you loose the customer field value for those rows, so if you want to do this because you like the way that looks in a report: do it all the way at the end of your search. And it will mean that if someone who opens the report starts sorting the customer column differently, things will also get messed up.

0 Karma

jperry_intact
New Member

I was afraid of that. Thanks for your help!

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 ...