topic Re: how to combine 2 rows into 1 in a statistic results (table) ? in Splunk Search

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

jjofret — Fri, 15 May 2020 13:35:50 GMT

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:

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

PavelP — Fri, 15 May 2020 14:02:28 GMT

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

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

vnravikumar — Fri, 15 May 2020 14:07:35 GMT

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

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

niketn — Fri, 15 May 2020 14:16:19 GMT

@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>

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

jjofret — Fri, 15 May 2020 17:25:55 GMT

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