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
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.
Great! This is exactly what I was looking for.
The one above displays the field only after the value changes. How do we display the last value before it changes?
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. 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
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
You’re looking for a way to create a field based on a change in the field's value. Is that right?