Splunk Search

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

sarathipattam
New Member

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,
alt text

Expected Results:

alt text

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

0 Karma
1 Solution

niketn
Legend

@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

alt text

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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@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

alt text

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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

sarathipattam
New Member

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?

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

sarathipattam
New Member

Thank you Niketnilay, it worked.

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...