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