Splunk Search

Stats function by multiple fields

byu168168
Path Finder

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.

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

niketn
Legend

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

alt text

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 &lt; and > needs to be escaped as &gt; 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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

somesoni2
Revered Legend

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
Get Updates on the Splunk Community!

Thanks for the Memories! Splunk University, .conf24, and Community Connections

Thank you to everyone in the Splunk Community who joined us for .conf24 – starting with Splunk University and ...

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...