topic Re: Is there way to group columns of a table? in Splunk Search

Is there way to group columns of a table?

praveenkpatidar — Tue, 29 Sep 2020 09:44:44 GMT

Hello,

I have one requirement in which certain columns have to be grouped together on a table.
I have XSL sheet data as below. During indexing, I changed below columns as Prod_Sev1 Prod_Sev2 and so on, but is there way to show the same result with the format below?

Phase           Prod                  Dev                     Test
          Sev1  Sev2  Sev3      Sev1  Sev2   Sev3      Sev1   Sev2   Sev3

Phase1    4     5     4         3     4      0         2      3      2
Phase2    4     5     4         3     4      0         2      3      2      
Phase3    4     5     4         3     4      0         2      3      2

Re: Is there way to group columns of a table?

jkat54 — Fri, 20 May 2016 07:10:01 GMT

Sure...

...| rename Prod_Sev1 AS "1.P_Sev1", Prod_Sev2 AS "2.P_Sev2", Prod_Sev3 AS "3.P_Sev3", Dev_Sev1 as "4.D_Sev1", Dev_Sev2 as "5.D_Sev2", Dev_Sev3 as "6.D_Sev3" ...

That would put them in sequential order but not add the 1st header, and combine columns like your 1st row of data there.

Re: Is there way to group columns of a table?

woodcock — Wed, 25 May 2016 08:28:21 GMT

No, but it can be done transposed like this:

This fakes the base data (you have a base search for this):

|noop|stats count AS Environment
|eval Environment="Prod,Dev,Test"|makemv delim="," Environment|mvexpand Environment
|eval Phase="Phase1,Phase2,Phase3"|makemv delim="," Phase|mvexpand Phase
|eval Severity=case((Environment="Prod"), "Sev1,Sev1,Sev1,Sev1,Sev2,Sev2,Sev2,Sev2,Sev2,Sev3,Sev3,Sev3,Sev3",
                    (Environment="Dev"), "Sev1,Sev1,Sev1,Sev2,Sev2,Sev2,Sev2",
                    (Environment="Test"), "Sev1,Sev1,Sev2,Sev2,Sev2,Sev3,Sev3")|makemv delim="," Severity|mvexpand Severity

Next you have to ensure that every Severity has a value (fillnull for 0😞

| eval EnvironmentPhase=Environment . ":" . Phase
| chart count BY EnvironmentPhase Severity
| untable EnvironmentPhase Severity count
| rex field=EnvironmentPhase "(?<Environment>[^:]+):(?<Phase>[^:]+)" | fields - EnvironmentPhase
| sort 0 - Phase

Lastly, we build the transposed view that you desire:

| eval EnvironmentSeverity=Environment . ":" . Severity
| chart first(count) BY EnvironmentSeverity Phase
| rex field=EnvironmentSeverity "(?<Environment>[^:]+):(?<Severity>[^:]+)" | fields - EnvironmentSeverity
| stats list(*) AS * BY Environment
| fields Environment Severity Phase*

This results in a view like this:

Environment   Severity   Phase1   Phase2   Phase3
       Prod       Sev1        4        4        4
                  Sev2        5        5        5
                  Sev3        4        4        4
       Dev        Sev1        3        3        3
                  Sev2        4        4        4
                  Sev3        0        0        0
       Test       Sev1        2        2        2
                  Sev2        3        3        3
                  Sev3        2        2        2

Re: Is there way to group columns of a table?

rarangarajanspl — Thu, 16 Apr 2020 01:23:46 GMT

Hello woodcock
I have posted a query. I think it's similar to this one. Could you suggest?
https://answers.splunk.com/answers/817006/how-can-i-format-a-table-as-rows-into-columns.html
Thank You!