Stats function by multiple fields

byu168168 — Tue, 12 Sep 2017 16:54:44 GMT

I have a table of data like this

Time1         Time2   Time3 Total
36.650000   16.050000   0.133333    74
44.866667   40.016667   0.366667    107.366667
54.966667   17.483333   0.366667    90.716667
2.083333    57.950000   22.483333   98.550000
41.733333   14.150000   0.150000    80.116667
3.283333    28.083333   0.400000    54.516667
44.783333   27.733333   0.466667    88.933333

There are 4 times produced for each event. I want to do a stats median, p25, p75 by each of these to result in a table like

 Process      Median     p25     p75
    Time1      #          #      #
    Time2      #          #      #
    Time3      #          #      #
    Total      #          #      #

What would be the best way to accomplish the above table? More information on the query can be given if necessary. The goal is to produce a box plot with four plots, one for each Process.

Re: Stats function by multiple fields

somesoni2 — Tue, 12 Sep 2017 20:07:37 GMT

Try like this

your current search giving fields Time1, TIme2, Time3, Total
| eval temp=1 | untable temp Process Time
| stats median(Time) as Median, p25(Time) as p25, p75(Time) as p75 by Process

Re: Stats function by multiple fields

niketn — Tue, 12 Sep 2017 20:11:54 GMT

@byu168168, I am sure someone will come up with the answer to aggregate the data as per your requirement directly using SPL.

Until then please try out the following approach:

Step 1) Create all the required statistical aggregates as per your requirements for all four series i.e.

<YourBaseSearch>
| stats Median(T*) as T*_Median p25(T*) as T*_p25 p75(T*) as T*_p75
| foreach T*
      [eval <<FIELD>>=round(<<FIELD>>,1)]

PS: < needs to be escaped as < and > needs to be escaped as > in dashboard. In Search there is no need to escape.

Step 2: Set the token for each statistical aggregate as they all will be returned in the same/single row (using <done> Search Event Handler.

       <done>
         <set token="Time1_Median">$result.Time1_Median$</set>
         <set token="Time1_p25">$result.Time1_p25$</set>
         <set token="Time1_p75">$result.Time1_p75$</set>
         <set token="Time2_Median">$result.Time2_Median$</set>
         <set token="Time2_p25">$result.Time2_p25$</set>
         <set token="Time2_p75">$result.Time2_p75$</set>
         <set token="Time3_Median">$result.Time3_Median$</set>
         <set token="Time3_p25">$result.Time3_p25$</set>
         <set token="Time3_p75">$result.Time3_p75$</set>
         <set token="Total_Median">$result.Total_Median$</set>
         <set token="Total_p25">$result.Total_p25$</set>
         <set token="Total_p75">$result.Total_p75$</set>
       </done>
     </search>

Step 3: Print the result in required format using <html> panel in Splunk using <table> element with table formatting similar to what Splunk uses by default.

<dashboard>
  <label>Aggregate on multiple columns WIP</label>
  <row>
    <panel>
      <title>Table with Mocked Data</title>
      <table>
        <search>
          <query>| makeresults
| eval data=" 36.650000,16.050000,0.133333,74;44.866667,40.016667,0.366667,107.366667;54.966667,17.483333,0.366667,90.716667;2.083333,57.950000,22.483333,98.550000;41.733333,14.150000,0.150000,80.116667;3.283333,28.083333,0.400000,54.516667;44.783333,27.733333,0.466667,88.933333"
 | makemv delim=";" data
 | mvexpand data
 | eval data=split(data,",")
 | eval Time1=mvindex(data,0)
 | eval Time2=mvindex(data,1)
 | eval Time3=mvindex(data,2)
 | eval Total=mvindex(data,3)
 | fields - data _time
 | table T*</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>
  <row>
    <panel>
      <title>Table with SPL Query Result</title>
      <table>
        <search>
          <query>| makeresults
| eval data=" 36.650000,16.050000,0.133333,74;44.866667,40.016667,0.366667,107.366667;54.966667,17.483333,0.366667,90.716667;2.083333,57.950000,22.483333,98.550000;41.733333,14.150000,0.150000,80.116667;3.283333,28.083333,0.400000,54.516667;44.783333,27.733333,0.466667,88.933333"
 | makemv delim=";" data
 | mvexpand data
 | eval data=split(data,",")
 | eval Time1=mvindex(data,0)
 | eval Time2=mvindex(data,1)
 | eval Time3=mvindex(data,2)
 | eval Total=mvindex(data,3)
 | fields - data _time
 | table T*
 | stats Median(T*) as T*_Median p25(T*) as T*_p25 p75(T*) as T*_p75
 | foreach T*
     [eval <<FIELD>>=round(<<FIELD>>,1)]</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
          <done>
            <set token="Time1_Median">$result.Time1_Median$</set>
            <set token="Time1_p25">$result.Time1_p25$</set>
            <set token="Time1_p75">$result.Time1_p75$</set>
            <set token="Time2_Median">$result.Time2_Median$</set>
            <set token="Time2_p25">$result.Time2_p25$</set>
            <set token="Time2_p75">$result.Time2_p75$</set>
            <set token="Time3_Median">$result.Time3_Median$</set>
            <set token="Time3_p25">$result.Time3_p25$</set>
            <set token="Time3_p75">$result.Time3_p75$</set>
            <set token="Total_Median">$result.Total_Median$</set>
            <set token="Total_p25">$result.Total_p25$</set>
            <set token="Total_p75">$result.Total_p75$</set>
          </done>
        </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>
      <html>
        <h2>Table with result formatted in HTML</h2>
        <table class="table table-chrome table-striped wrapped-results">
          <thead>
            <tr>
              <th>Process</th>
              <th>Median</th>
              <th>p25</th>
              <th>p75</th>
            </tr>
          </thead>
          <tbody>
            <tr>
              <td>Time1</td>
              <td>$Time1_Median$</td>
              <td>$Time1_p25$</td>
              <td>$Time1_p75$</td>
            </tr>
            <tr>
              <td>Time2</td>
              <td>$Time2_Median$</td>
              <td>$Time2_p25$</td>
              <td>$Time2_p75$</td>
            </tr>
            <tr>
              <td>Time3</td>
              <td>$Time3_Median$</td>
              <td>$Time3_p25$</td>
              <td>$Time3_p75$</td>
            </tr>
            <tr>
              <td>Total</td>
              <td>$Total_Median$</td>
              <td>$Total_p25$</td>
              <td>$Total_p75$</td>
            </tr>
          </tbody>    
        </table>
      </html>
    </panel>
  </row>
</dashboard>

topic Stats function by multiple fields in Splunk Search

Stats function by multiple fields

Re: Stats function by multiple fields

Re: Stats function by multiple fields