How to create an eval column in a table that says "yes" or "no" if the value in another column has changed from the previous row?

[sjanwity]

I have a table which stores updates done on a database (see my previous questions for more details). I want to create a column which basically tells me if a column in a row has changed from the previous row. For example:

KEY    |     VALUE |    UPDATED?
abc    |     2     |    NO
abc    |     2     |    NO
abc    |     3     |    YES
def    |     4     |    NO
def    |     5     |   YES
def    |     5     |   NO

The UPDATED? column should default to 'NO' or empty at the start of every new key, however the key could be a compound of 2 or more different columns. Is this possible?

[gkanapathy · Accepted solution]

... | streamstats window=2 current=t global=f
         first(VALUE) as prev
         last(VALUE) as curr
      by KEY
    | eval UPDATED = if(prev==curr,"NO","YES")

[martin_mueller]

You can group by multiple fields, yeah - just like you do in stats.

[martin_mueller]

The streamstats walks over your events looking at two events at the same time, the "current" one and the previous one, grouped by KEY. As it walks along it creates two fields prev and curr that store the previous and current value of VALUE.

Using that, the eval can compare the two and produce YES or NO.

[sjanwity]

the key is actually a compound of 3 columns. Do I just add them in?

[sjanwity]

Could you provide an explanation please to what each command (roughly) is doing?