Splunk Search

Is there way to group columns of a table?

praveenkpatidar
Explorer

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      
0 Karma

woodcock
Esteemed Legend

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
0 Karma

rarangarajanspl
Explorer

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!

0 Karma

jkat54
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

&#x1f342; Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...