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?
@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>
@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>
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?
@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!
Thank you Niketnilay, it worked.