Splunk Search

Report on changes to a field over a specified time

Motivator
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

Tags (3)

Splunk Employee
Splunk Employee

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.

Motivator

Great! This is exactly what I was looking for.

0 Karma

Engager

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

0 Karma

Super Champion

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...

0 Karma

Motivator

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.

Thanks,

Rob

0 Karma

Motivator

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

0 Karma

Super Champion

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

0 Karma