How to annotate when a transition happens?

[MScottFoley]

I want to add an annotation to a dashboard every time we switch from blue servers to green servers or green to blue.  There is no event for this, but I can calculate the active color by comparing the count of each type of server.  If I look two minutes ago and compare it to one minute ago I can see if the active color changed.  So if two minutes ago there were more blue servers than green servers, but now there are more green than blue I know the active color changed.      

This query will show a transition if I give it two time frames (two minutes ago compared to one minute ago).  It works, but I want the query to show me all color transitions over a specific time period, such as 24 hours.      

index=...
earliest=-3m latest=-2m
| stats count(eval(match(colortag,"Blue"))) as BlueCount, count(eval(match(colortag,"Green"))) as GreenCount
| eval activePreviously=if(BlueCount > GreenCount, "BLUE", "GREEN")
| fields activePreviously
| join [search index=...
    earliest=-2m latest=-1m
    | stats count(eval(match(colortag,"Blue"))) as BlueCount, 
     count(eval(match(colortag,"Green"))) as GreenCount
    | eval activeNow=if(BlueCount > GreenCount, "BLUE", "GREEN")
| fields activeNow]
| eval transition=if(activePreviously=activeNow, "no", "yes")
| where transition="yes"
| table transition activeNow activePreviously

This search will show me the active color in 2 minute period periods over a given time frame. 

Index=...
| bin _time span=2m
| stats count(eval(match(colortag,"Blue"))) as BlueCount, count(eval(match(colortag,"Green"))) as GreenCount by _time
| eval active=if(BlueCount > GreenCount, "BLUE", "GREEN")  

This is what I see

_time                                       BlueCount           GreenCount          active
2022-11-15 11:15:00      1561                      143                           BLUE
2022-11-15 11:16:00      1506                      140                           BLUE
2022-11-15 11:17:00      1627                      154                           BLUE
2022-11-15 11:18:00      1542                      148                           BLUE
2022-11-15 11:19:00      1199                      553                           BLUE
2022-11-15 11:20:00        255                    1584                           GREEN
2022-11-15 11:21:00             3                     1721                          GREEN
2022-11-15 11:22:00             0                     1733                          GREEN
2022-11-15 11:23:00             0                     1780                          GREEN
2022-11-15 11:24:00             0                     1802                          GREEN

I want to add a field that indicates if the color changed from the previous _time.  I will then only show (annotate) the time and color where change=yes.

_time                                       BlueCount           GreenCount          active             change
2022-11-15 11:15:00      1561                      143                           BLUE                 N/A 
2022-11-15 11:16:00      1506                      140                           BLUE                 No
2022-11-15 11:17:00      1627                      154                           BLUE                 No
2022-11-15 11:18:00      1542                      148                           BLUE                 No
2022-11-15 11:19:00      1199                      553                           BLUE                 No
2022-11-15 11:20:00        255                    1584                           GREEN             Yes
2022-11-15 11:21:00             3                     1721                           GREEN             No
2022-11-15 11:22:00             0                     1733                           GREEN             No
2022-11-15 11:23:00             0                     1780                           GREEN             No
2022-11-15 11:24:00             0                     1802                           GREEN             No

I can't see how to reference the previous active color from the current bin/bucket.  That is probably not the way to do it, but that is where I go to before asking for help.  

In short, I want to annotate whenever the count of two fields changes so that one is now larger than the other one and show the name of the larger field.  

Thanks.

[ITWhisperer · Accepted solution]

You can use streamsats to track the previous value of a field, then you can compare with the current value

| streamstats window=1 current=f latest(active) as previous_active