topic Re: need help with streamstats in Splunk Search

need help with streamstats

kiamco — Sat, 11 Aug 2018 22:50:55 GMT

I have this problem with streamstats maybe I am not understanding it right but my expected result didnt come out from the query that I wrote

index=summary source="summary_events" 
orig_source=some_source
ms_region=us-west-1
ms_level=E*
| stats sum(count) as totalEvents by _time event
| streamstats time_window=1d first(totalEvents) as day1 by event
| streamstats time_window=2d first(totalEvents) as day2 by event 
| streamstats time_window=3d first(totalEvents) as day3 by event
| streamstats time_window=4d first(totalEvents) as day4 by event
| streamstats time_window=5d first(totalEvents) as day5 by event
| streamstats time_window=6d first(totalEvents) as day6 by event
| streamstats time_window=7d first(totalEvents) as day7 by event

The sample result is:

_time   event   totalEvents day1    day2    day3    day4    day5    day6    day7
2018-08-04 22:00:00 no-event    217 217 217 217 217 217 217 217
2018-08-04 22:00:00 service-error   1   1   1   1   1   1   1   1
2018-08-04 22:00:00 bad-url 7   7   7   7   7   7   7   7

my expectation was that it is going to display the total count for each past day per 5min interval but I am just getting the same value for all of them.Any ideas what Im doing wrong?

as sample event that I am aggregating is

08/11/2018 22:15:00 +0000, search_name="Summary index: Error reports", search_now=1534026000.000, info_min_time=1534025400.000, info_max_time=1534026000.000, info_search_time=1534026007.707, count=3, event="some_event", ms_level=ERROR, ms_region="region", ms_version=version, orig_source="source"

Re: need help with streamstats

renjith_nair — Sun, 12 Aug 2018 06:46:02 GMT

@kiamco,
Try this and lets know what modification you require.

 index=summary source="summary_events"  orig_source=some_source ms_region=us-west-1 ms_level=E*
|bucket span=5m _time|stats sum(count) by _time,event|eval date=strftime(_time,"%d-%m-%Y")
|streamstats dc(date) as dayno|eval dayno="Day".dayno|fields - date

Re: need help with streamstats

kiamco — Sun, 12 Aug 2018 23:49:52 GMT

@renjith.nair so I tried the query above and this is what I got :
sample result

  _time event   sum(count)  dayno
    2018-08-05 23:00:00 idna-encode-failed  1   Day1
    2018-08-05 23:00:00 monitor-set-selection-error 4   Day1
    2018-08-05 23:00:00 no-event    53  Day1
    2018-08-05 23:00:00 srouter-browser-useragent-ie-valid-failed   8   Day1
    2018-08-05 23:00:00 tc-frame-load-timed-out 2   Day1
    2018-08-05 23:00:00 tc-pairing-unexpected-response  6   Day1
    2018-08-05 23:05:00 auth-password-reset-failed  1   Day1
    2018-08-05 23:05:00 no-event    60  Day1
    2018-08-05 23:05:00 srouter-bad-url 2   Day1
    2018-08-05 23:05:00 srouter-browser-useragent-ie-valid-failed   10  Day1
    2018-08-05 23:05:00 srouter-fetch-retry-failed  43  Day1
    2018-08-05 23:05:00 tc-cb-generated-exception   1   Day1
    2018-08-05 23:05:00 tc-pairing-unexpected-response  2   Day1
    2018-08-05 23:10:00 no-event    77  Day1
    2018-08-05 23:10:00 service-unavailable-error   1   Day1
    2018-08-05 23:10:00 srouter-bad-url 2   Day1
    2018-08-05 23:10:00 srouter-browser-useragent-ie-valid-failed   8   Day1
    2018-08-05 23:10:00 srouter-fetch-retry-failed  28  Day1
    2018-08-05 23:10:00 surrogate-unknown-fid   4   Day1
    2018-08-05 23:10:00 tc-pairing-unexpected-response  10  Day1
    2018-08-05 23:15:00 monitor-set-selection-error 1   Day1
    2018-08-05 23:15:00 no-event    58  Day1
    2018-08-05 23:15:00 srouter-browser-useragent-ie-valid-failed   6   Day1
    2018-08-05 23:15:00 srouter-fetch-retry-failed  1   Day1
    2018-08-05 23:15:00 srouter-popup-pair-failure  1   Day1
    2018-08-05 23:15:00 surrogate-unknown-fid   1   Day1
    2018-08-05 23:15:00 tc-pairing-unexpected-response  7   Day1
    2018-08-05 23:15:00 tc-uncaught-exception   1   Day1
    2018-08-05 23:20:00 channel-xhr-push-lost-message   3   Day1

it goes all the way to day 7

which is giving me the information that I wanted but not in the format that I was looking for

Re: need help with streamstats

renjith_nair — Mon, 13 Aug 2018 03:31:36 GMT

Do you want to display all 5 minutes interval count as field name (that will be a long list) or just sum/day?

Re: need help with streamstats

kiamco — Mon, 13 Aug 2018 16:57:41 GMT

@renjith.nair so what I wanted is for the column fields would be:

 _time    event    totalEvents    day1    day2    day3    day4    day5    day6    day7

There is a reason why I wanted it in this format, I was planning on running a foreach command and compare the past days by the total event and see whether or not it is an outlier stuff like that.

Re: need help with streamstats

renjith_nair — Tue, 14 Aug 2018 15:26:26 GMT

@kiamco,
Alright try this,

  index=summary source="summary_events"  orig_source=some_source ms_region=us-west-1 ms_level=E*
 |timechart span=5m sum(count)  as count by event|eval date=strftime(_time,"%d-%m-%Y"),time=strftime(_time,"%H:%M:%S")
|table time,event,count,date|eval temp=time."-".event
|chart values(count) over temp by date|rex field=temp "(?<time>[^-]+)-(?<event>[^-]+)"
|fields - temp