topic Re: How can I bring up the top 10 errors in Pivot view? in Splunk Search

How can I bring up the top 10 errors in Pivot view?

sarathipattam — Wed, 30 May 2018 16:55:01 GMT

Hi,

I'm trying to pull top 10 errors for last 7 days and I would like to show each error counts on each day. Pls see the query details below,

Below is my query for top 10 errors,

index=stores_servers hdr.level="Error"
| eval _time=strftime(_time,"%Y-%m-%d")
| rename bdy.msg AS msg
| chart count over _time by msg useother=f| head 10

Results for above query is below,

Expected Results:

Can someone help me on how to get the expected results in Splunk please?

Re: How can I bring up the top 10 errors in Pivot view?

niketn — Wed, 30 May 2018 18:07:18 GMT

@sarathipattam if you want to plot the top 10 errors that have occurred in last 7 days and break out their occurrence daily, you should try a different search. Your current search plots daily count of errors broken down daily and gives your first 10 days (if you are selecting last 7 days, you will always get 7 rows).

Try the following run anywhere search which finds out Splunk's _internal logs top 10 errors/warnings in last 7 days and breaks out the daily distribution of errors.

index=_internal sourcetype=splunkd 
    [search index=_internal sourcetype=splunkd log_level!=INFO earliest=-7d@d latest=now
|  top 10 message showperc=f showcount=f
|  table message] earliest=-7d@d latest=now
|  eval Time=strftime(_time,"%Y/%m/%d")
|  eval message=substr(message,0,100)
|  chart count as ErrorCount by message Time
|  addtotals row=t col=f
|  sort - Total

Following is the Simple XML dashboard code for the screen mock up:

<form>
  <label>Top 10 error messages in last 7 days</label>
  <fieldset submitButton="false">
    <input type="time" token="tokTime" searchWhenChanged="true">
      <label></label>
      <default>
        <earliest>-7d@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>index=_internal sourcetype=splunkd 
    [search index=_internal sourcetype=splunkd log_level!=INFO earliest=$tokTime.earliest$ latest=$tokTime.latest$
|  top 10 message showperc=f showcount=f
|  table message]
|  eval Time=strftime(_time,"%Y/%m/%d")
|  eval message=substr(message,0,100)
|  chart count as ErrorCount by message Time
|  addtotals row=t col=f
|  sort - Total</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</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="refresh.display">progressbar</option>
        <option name="rowNumbers">true</option>
        <option name="totalsRow">true</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</form>

Re: How can I bring up the top 10 errors in Pivot view?

sarathipattam — Tue, 29 Sep 2020 19:47:19 GMT

Hi niketnilay ,

Thank you for the response, I tried your query as below,

index=stores_servers sourcetype=json
[search index=stores_servers sourcetype=json hdr.level="Critical" earliest=-7d@d latest=now
| top 10 hdr.msg showperc=f showcount=f
| table hdr.msg] earliest=-7d@d latest=now
| eval Time=strftime(_time,"%Y/%m/%d")
| eval message=substr(hdr.msg,0,1000)
| chart count as ErrorCount by message Time
| addtotals row=t col=f
| sort - Total

and I ended up with blank results. Can you tell me where I'm missing please?

Re: How can I bring up the top 10 errors in Pivot view?

niketn — Wed, 30 May 2018 21:09:07 GMT

@sarathipattam, Try the following search. The field message in Splunk's _internal index is actually hdr.msg for your. Also the reason I used substr() in my example was to restrict the number of characters in message field as the same will be displayed as column header. If you wish to retain 1000 characters, it would be too long.

index=stores_servers sourcetype=json 
[search index=stores_servers sourcetype=json hdr.level="Critical" "hdr.msg"="*" earliest=-7d@d latest=now
| top 10 "hdr.msg" showperc=f showcount=f
| table "hdr.msg"] earliest=-7d@d latest=now
| eval Time=strftime(_time,"%Y/%m/%d")
| eval "hdr.msg" =substr('hdr.msg',0,100)
| chart count as ErrorCount by hdr.msg Time
| addtotals row=t col=f
| sort - Total

I have done a change in subsearch and added "hdr.msg"="*" condition to ensure that we get only the events with "hdr.msg" field in it.

Please try out and confirm!

Re: How can I bring up the top 10 errors in Pivot view?

sarathipattam — Thu, 31 May 2018 21:00:36 GMT

Thank you Niketnilay, it worked.