How to get previous field value where other fields are equal?

[sboogaar]

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

[renjith_nair · Accepted solution]

@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