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