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
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.
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...
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. OldGrade and NewGrade 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.
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.