Solved: how to combine 2 rows into 1 in a statistic result...

jjofret · ‎05-15-2020

Hi everyone, I would like to know if there is any way to merge or combine the results of two or more rows into one single in a table, for example:

If there any posibility to change that result for something like this:

niketn · ‎05-15-2020

@jjofret can you try the following

<yourCurrentSearch>
| stats list("tipo de red") as "tipo de red" list(FW) as FW by Entorno

If you want Table Formatting also the way in your example you can try the following CSS Style override where table_multivalue_with_format is the id of the table

  <html depends="$alwaysHideCSS$">
    <style>
      #table_multivalue_with_format table tbody tr td {
        padding: 1px !important;
        text-align: center !important;
      }
      #table_multivalue_with_format table tbody tr td div.multivalue-subcell{
        border-top-style: solid;
        border-left-style: solid;
        border-right-style: solid;
        text-align: center !important;
        border-width: thin;
      }
      #table_multivalue_with_format table tbody tr td div.multivalue-subcell:last-child{
        border-bottom-style: solid;
      }
    </style>
  </html>

Following is a run anywhere example based on Sample Data Provided:

<dashboard>
  <label>Table with Multivalue</label>
  <row>
    <panel>
      <html depends="$alwaysHideCSS$">
        <style>
          #table_multivalue_with_format table tbody tr td {
            padding: 1px !important;
            text-align: center !important;
          }
          #table_multivalue_with_format table tbody tr td div.multivalue-subcell{
            border-top-style: solid;
            border-left-style: solid;
            border-right-style: solid;
            text-align: center !important;
            border-width: thin;
          }
          #table_multivalue_with_format table tbody tr td div.multivalue-subcell:last-child{
            border-bottom-style: solid;
          }
        </style>
      </html>
      <table id="table_multivalue_with_format">
        <search>
          <query>| makeresults 
| eval data="test,dmz,yes;test,dmz,no;test,ian,yes;production,dmz,yes;production,dmz,no;production,ian,yes;production,ian,no;" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="," 
| eval Entorno=mvindex(data,0),"tipo de red"=mvindex(data,1),FW=mvindex(data,2) 
| table Entorno "tipo de red" FW
| stats list("tipo de red") as "tipo de red" list(FW) as FW by Entorno</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</dashboard>

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn · ‎05-15-2020

@jjofret can you try the following

<yourCurrentSearch>
| stats list("tipo de red") as "tipo de red" list(FW) as FW by Entorno

If you want Table Formatting also the way in your example you can try the following CSS Style override where table_multivalue_with_format is the id of the table

  <html depends="$alwaysHideCSS$">
    <style>
      #table_multivalue_with_format table tbody tr td {
        padding: 1px !important;
        text-align: center !important;
      }
      #table_multivalue_with_format table tbody tr td div.multivalue-subcell{
        border-top-style: solid;
        border-left-style: solid;
        border-right-style: solid;
        text-align: center !important;
        border-width: thin;
      }
      #table_multivalue_with_format table tbody tr td div.multivalue-subcell:last-child{
        border-bottom-style: solid;
      }
    </style>
  </html>

Following is a run anywhere example based on Sample Data Provided:

<dashboard>
  <label>Table with Multivalue</label>
  <row>
    <panel>
      <html depends="$alwaysHideCSS$">
        <style>
          #table_multivalue_with_format table tbody tr td {
            padding: 1px !important;
            text-align: center !important;
          }
          #table_multivalue_with_format table tbody tr td div.multivalue-subcell{
            border-top-style: solid;
            border-left-style: solid;
            border-right-style: solid;
            text-align: center !important;
            border-width: thin;
          }
          #table_multivalue_with_format table tbody tr td div.multivalue-subcell:last-child{
            border-bottom-style: solid;
          }
        </style>
      </html>
      <table id="table_multivalue_with_format">
        <search>
          <query>| makeresults 
| eval data="test,dmz,yes;test,dmz,no;test,ian,yes;production,dmz,yes;production,dmz,no;production,ian,yes;production,ian,no;" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="," 
| eval Entorno=mvindex(data,0),"tipo de red"=mvindex(data,1),FW=mvindex(data,2) 
| table Entorno "tipo de red" FW
| stats list("tipo de red") as "tipo de red" list(FW) as FW by Entorno</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</dashboard>

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

jjofret · ‎05-15-2020

Thanks so much @niketnilay , that´s the idea, I´m thinking how to apply the same to the second and others columms whit CSS.

vnravikumar · ‎05-15-2020

Hi

Check this

| makeresults 
| eval Entorno="test","tip de red"="dmz",FW="yes" 
| append 
    [| makeresults 
    | eval Entorno="test","tip de red"="dmz",FW="no" ] 
| append 
    [| makeresults 
    | eval Entorno="test","tip de red"="lan",FW="yes" ] 
| append 
    [| makeresults 
    | eval Entorno="production","tip de red"="dmz",FW="yes" ] 
| append 
    [| makeresults 
    | eval Entorno="production","tip de red"="dmz",FW="no" ] 
| append 
    [| makeresults 
    | eval Entorno="production","tip de red"="lan",FW="yes" ] 
| append 
    [| makeresults 
    | eval Entorno="production","tip de red"="lan",FW="no" ] 
| eval temp='tip de red'.",".'FW' 
| stats list(temp) as temp by Entorno 
| rex field=temp "(?P<tip_de_red>[^,]+),(?P<FW>\S+)" 
| table Entorno "tip_de_red" FW

PavelP · ‎05-15-2020

Hello @jjofret,

something like this:

| makeresults count=7 
| streamstats count 
| eval Entorno = if((count % 2)=1, "test", "production")
| eval "tipo de red" = if((random() % 2)=1, "dmz", "lan") 
| eval "FW" = if((random() % 2)=1, "yes", "no") 
| fields - _time count
| stats list("tipo de red"), list("FW") by Entorno

how to combine 2 rows into 1 in a statistic results (table) ?

Thanks for the Memories! Splunk University, .conf25, and our Community

Data Persistence in the OpenTelemetry Collector

Introducing Splunk 10.0: Smarter, Faster, and More Powerful Than Ever

Are you a member of the Splunk Community?

how to combine 2 rows into 1 in a statistic results (table) ?

Thanks for the Memories! Splunk University, .conf25, and our Community

Data Persistence in the OpenTelemetry Collector

Introducing Splunk 10.0: Smarter, Faster, and More Powerful Than Ever