How to create a report on the hourly count, min, max, and avg of certain strings found in my log file?

[runiyal]

In my log file, I have lot of messages saying upload or search got completed in x seconds. Like:

Upload executed in x seconds
Search completed in x seconds

We need a report that tells us the total count on an hourly basis, but with it, it should also calculate Min/Max/Avg time (second) spent for each of that operation. Result should be like:

Activity Count Min Max Avg
Upload

Search

[rphillips_splk]

<form>
  <label>sample dash for runiyal</label>
  <fieldset submitButton="false">
    <input type="time">
      <label></label>
      <default>
        <earliest>-24h@h</earliest>
          <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Stats of Activity</title>
      <chart>
        <search>
          <query>source=*test.log | rex field=_raw "(?<Activity>\w+)(\s+executed in|\s+completed in)\s+(?<seconds>\w+)\s+seconds"  | timechart span=1h avg(seconds) as avg_sec  min(seconds) as min_sec max(seconds) as max_sec by Activity</query>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">line</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <title>Count of Activity</title>
      <chart>
        <search>
          <query>source=*test.log | rex field=_raw "(?<Activity>\w+)(\s+executed in|\s+completed in)\s+(?<seconds>\w+)\s+seconds"  | timechart span=1h count by Activity usenull=f</query>

        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">column</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
      </chart>
    </panel>
  </row>
</form>

[somesoni2 · Accepted solution]

Try something like this (runs for last hour, check the field extraction as per your actual log)

your base search earliest=-1h@h latest=@h | rex field=_raw "(?<Activity>\w+)\s(executed|completed) in (?<duration>\d+) seconds" | stats count as Count min(duration) as Min max(duration) as Max avg(duration) as Avg by Activity

[runiyal]

Hello,

Logfile got updated so that we get the dureation in milliseconds. So now the log file shows -

Search Completed successfully in 0.698 seconds
Upload Completed successfully in 2.529 seconds

How to incorporate this in the report?

Thanks!

[runiyal]

Thanks a lot. It helped!

One more thought - What if I need to have the count and Min/Max/Avg for each hour for a day?

Thanks in advance!