Splunk Search

need help with streamstats

kiamco
Path Finder

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"
Tags (1)
0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!
0 Karma

kiamco
Path Finder

@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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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

Happy Splunking!
0 Karma

kiamco
Path Finder

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...