Reporting
Highlighted

How to use pivot (unpivot) command

Communicator

Hi all!

I have a table like this:

ServerName Metric1 Metric2 

Server1    1       0
Server2    1       1
Server3    1       1
Server4    0       1

And i need a table like this:

Column   Rows     Count

Metric1  Server1  1
Metric2  Server1  0
Metric1  Server2  1
Metric2  Server2  1
Metric1  Server3  1
Metric2  Server3  1
Metric1  Server4  0
Metric2  Server4  1

This was the simple case.


The difficult case is:
i need a table like this:

Column   Rows      Col_type  Parent_col Count

Metric1  Server1   Sub       Metric3    1   
Metric2  Server1   Sub       Metric3    0
Metric3  Server1                        (Metric1+Metric2)
Metric1  Server2   Sub       Metric3    1
Metric2  Server2   Sub       Metric3    1
Metric3  Server2                        (Metric1+Metric2)
Metric1  Server3   Sub       Metric3    1
Metric2  Server3   Sub       Metric3    1
Metric3  Server3                        (Metric1+Metric2)
Metric1  Server4   Sub       Metric3    0
Metric2  Server4   Sub       Metric3    1
Metric3  Server4                        (Metric1+Metric2)

Thanks all!

Highlighted

Re: How to use pivot (unpivot) command

Champion

The community can best help you if you share some of the SPL commands you are using to generate the tables. For example, are you actually using pivot for this? Please share as much as possible.

0 Karma
Highlighted

Re: How to use pivot (unpivot) command

SplunkTrust
SplunkTrust

I have replicated your sample table with a csv and developed the following, which I understand it's exactly what you are looking for based on your description:

| inputcsv mycsv.csv 
| untable ServerName Metrics Count
| rename Metrics as Column, ServerName as Rows
| sort -limit=0 Rows, Column
| eval Col_type = "Sub"
| appendpipe [ | stats sum(Count) as Count by Rows | eval Column = "Metric3" ]
| sort -limit=0 Rows, Column
| eventstats last(Column) as Parent_col by Rows
| eval Parent_col = if (Col_type = "Sub", Parent_col, null())
| table Column, Rows, Col_type, Parent_col, Count

Output (see picture below):

alt text

And this is the CSV I used:

ServerName,Metric1,Metric2 
Server1,1,0
Server2,1,1
Server3,1,1
Server4,0,1

Thanks,
J

View solution in original post

Highlighted

Re: How to use pivot (unpivot) command

Communicator

Simply perfect!
Thank you!

0 Karma