topic Re: How to use pivot (unpivot) command in Reporting

How to use pivot (unpivot) command

andreafebbo — Thu, 02 Feb 2017 11:16:18 GMT

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!

Re: How to use pivot (unpivot) command

rjthibod — Thu, 02 Feb 2017 11:48:07 GMT

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.

Re: How to use pivot (unpivot) command

javiergn — Thu, 02 Feb 2017 11:59:54 GMT

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):

And this is the CSV I used:

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

Thanks,
J

Re: How to use pivot (unpivot) command

andreafebbo — Thu, 02 Feb 2017 13:50:54 GMT

Simply perfect!
Thank you!