Report on changes to a field over a specified time

[bandit]

I would like to display a table of all occurrences of a change to the value of a field over a period of time. i.e. last 30 days. Assumption that the field contents are a string value and a time stamp is available for each event.

example events where I would want to detect changes to Grade field:
 1. Student=Rob Subject=Math Grade=C 9/31/2013 08:15
 2. Student=Rob Subject=Math Grade=A 9/24/2013 18:01
 3. Student=Rob Subject=Math Grade=B 9/20/2013 13:00
 4. Student=Rob Subject=Math Grade=B 9/17/2013 14:30
 5. Student=Rob Subject=Math Grade=B 9/15/2013 06:05
 6. Student=Rob Subject=Math Grade=C 9/01/2013 13:00

Ideally I would display a report like so which shows changes and times:

Student     Subject     Old_Grade   New_Grade   Time
Rob     Math        A       C       9/31/2013 08:15
Rob     Math        B       A       9/24/2013 18:01
Rob     Math        C       B       9/15/2013 06:05

Thanks,

Rob

[gkanapathy]

You could do it this way:

... | streamstats global=f current=t window=2 
        first(Grade) as New_Grade
        last(Grade) as Old_Grade
        first(_time) as _time
      by Student Subject
    | where New_Grade != Old_Grade

assuming you might have this for multiple students and subjects.

[bandit]

Great! This is exactly what I was looking for.

[ringbbg]

The one above displays the field only after the value changes. How do we display the last value before it changes?

[lukejadamec]

You’re looking for a way to create a field based on a change in the field's value. Is that right?
If so, then you can't do it so far as I know.

Another way to check for a state change is with the dedup command.
If Grade is the field you are interested in, this will work:

Search Student=”*” Subject=”*” Grade=”*” | dedup Grade | table Student, Subject, Grade, _time

It will give you a table with one entry per state, but it will only give the most recent occurrence of each particular state: If the grade goes from A to B and then back to A then you will only see the most recent occurrence of the change from B to A.

If 'grades' are only changed within specific time intervals, then there may be other options...

[bandit]

Yes, thanks for the ideas. In my case, I have to show all changes, not just the last change. The reporting period could be 5 minutes or 3 months based on event data available. Only Grade value is the events. Old_Grade and New_Grade must be derived from Grade field. To answer your other question, grades could be updated at any time, not at a set interval. The grades example above is meant to serve as an example, however I plan to use this on many different reports to track changes to a field over time.

Thanks,

Rob

[bandit]

Looking to detect when the event changed and display current(new) and previous(old value) and time of the change over a short or long time frame. I'm looking for more of an audit report rather than an alert on change. So don't want to print an event if there wasn't a change.

Thanks,

Rob

[lukejadamec]

You’re looking for a way to create a field based on a change in the field's value. Is that right?