Im trying to find out how streamstats work, but the documentation is way off compared to the actual results in Splunk. At first I thought it was my data so I tried out some queries on testdata. Im trying to get the previous alert_level for each itsi_service
My query to test how streamstats works:
index="non-existingageawgew"
| append
[| makeresults
| eval itsi_service="DCOS Masters"
| eval kpi="AHM"
| eval alert_level=1
| eval entity_title=foo_title
| eval _time=now() - 600]
| append
[| makeresults
| eval itsi_service="DCOS Masters"
| eval kpi="AHM"
| eval alert_level=2
| eval entity_title=foo_title
| eval _time=now() - 1200]
| append
[| makeresults
| eval itsi_service="DCOS Masters"
| eval kpi="AHM"
| eval alert_level=3
| eval entity_title=foo_title
| eval _time=now() - 1800]
| append
[| makeresults
| eval itsi_service="DCOS Masters"
| eval kpi="AHM"
| eval alert_level=4
| eval entity_title=foo_title
| eval _time=now() - 2400]
| append
[| makeresults
| eval itsi_service="DCOS Masters"
| eval kpi="Hardware"
| eval alert_level=5
| eval entity_title=ics024047057
| eval _time=now() - 3000]
| append
[| makeresults
| eval itsi_service="DCOS Masters"
| eval kpi="AHM"
| eval alert_level=6
| eval entity_title=foo_title
| eval _time=now() - 3600]
| append
[| makeresults
| eval itsi_service="DCOS Masters"
| eval kpi="Hardware"
| eval alert_level=7
| eval entity_title=ics024047057
| eval _time=now() - 4200]
| append
[| makeresults
| eval itsi_service="DCOS Masters"
| eval kpi="AHM"
| eval alert_level=7
| eval entity_title=foo_title
| eval _time=now() - 4800]
| append
[| makeresults
| eval itsi_service="DCOS Masters"
| eval kpi="AHM"
| eval alert_level=9
| eval entity_title=foo_title
| eval _time=now() - 5600]
| sort _time
| streamstats window=1 current=false latest(alert_level) by itsi_service, kpi
| sort - _time
| table *
After reading the docs 10 times I expected:
alert_level itsi_service kpi latest(alert_level) _time
1 DCOS Masters AHM 2 2018-11-28 13:10:57
2 DCOS Masters AHM 3 2018-11-28 13:00:57
3 DCOS Masters AHM 4 2018-11-28 12:50:57
4 DCOS Masters AHM 6 2018-11-28 12:40:57
5 DCOS Masters Hardware 7 2018-11-28 12:30:57
6 DCOS Masters AHM 7 2018-11-28 12:20:57
7 DCOS Masters Hardware - 2018-11-28 12:10:57
7 DCOS Masters AHM 9 2018-11-28 12:00:57
9 DCOS Masters AHM - 2018-11-28 11:47:37
Actual result:
alert_level itsi_service kpi latest(alert_level) _time
1 DCOS Masters AHM 2 2018-11-28 13:10:57
2 DCOS Masters AHM 3 2018-11-28 13:00:57
3 DCOS Masters AHM 4 2018-11-28 12:50:57
4 DCOS Masters AHM - 2018-11-28 12:40:57
5 DCOS Masters Hardware - 2018-11-28 12:30:57
6 DCOS Masters AHM - 2018-11-28 12:20:57
7 DCOS Masters Hardware - 2018-11-28 12:10:57
7 DCOS Masters AHM 9 2018-11-28 12:00:57
9 DCOS Masters AHM - 2018-11-28 11:47:37
For each value I want to have the previous_value in a field if it has the same value for other fields e.g. kpi, itsi_service, entity are equal. Which command should be used to get the previous value? Most commands don't allow a by so i'm thinking of writing my own commands (like a head that can do a by, why the hell is that not possible anyway..) But I assume what im asking is done a million times so hopefully i'm just overlooking the obvious.
I know I can just do | sort itsi_service, kpi, _time but if why is that needed? Why is it not matching on the by fields for all events
@sboogaar ,
Either remove window=1
or add global=false
and you should get the result what you expected. When you include window=1, it looks only in the last event and by default global=true. Since you are using by clause, it checks last event but is not able to find a matching KPI.
global
Syntax: global=<boolean>
Description: Used only when the window argument is set. Defines whether to use a single window, global=true, or to use separate windows based on the by clause. If global=false and window is set to a non-zero value, a separate window is used for each group of values of the field specified in the by clause.
So below should work
| streamstats window=1 current=false global=false latest(alert_level) by itsi_service, kpi
@sboogaar ,
Either remove window=1
or add global=false
and you should get the result what you expected. When you include window=1, it looks only in the last event and by default global=true. Since you are using by clause, it checks last event but is not able to find a matching KPI.
global
Syntax: global=<boolean>
Description: Used only when the window argument is set. Defines whether to use a single window, global=true, or to use separate windows based on the by clause. If global=false and window is set to a non-zero value, a separate window is used for each group of values of the field specified in the by clause.
So below should work
| streamstats window=1 current=false global=false latest(alert_level) by itsi_service, kpi