topic Re: Excel Like Pivot Table in Splunk Search

Excel Like Pivot Table

jperry_intact — Thu, 26 Apr 2018 23:03:01 GMT

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

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!

Re: Excel Like Pivot Table

FrankVl — Fri, 27 Apr 2018 08:36:46 GMT

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.

Re: Excel Like Pivot Table

jperry_intact — Mon, 30 Apr 2018 14:53:28 GMT

I was afraid of that. Thanks for your help!