Splunk Search

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

runiyal
Path Finder

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

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

0 Karma

rphillips_splk
Splunk Employee
Splunk Employee
<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>
0 Karma

somesoni2
Revered Legend

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
0 Karma

runiyal
Path Finder

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!

0 Karma

runiyal
Path Finder

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!

0 Karma
Get Updates on the Splunk Community!

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...

Updated Team Landing Page in Splunk Observability

We’re making some changes to the team landing page in Splunk Observability, based on your feedback. The ...

New! Splunk Observability Search Enhancements for Splunk APM Services/Traces and ...

Regardless of where you are in Splunk Observability, you can search for relevant APM targets including service ...