How do you search to return a table of only fields that change between events?

[smahone11]

Lets say I have a query that returns all of the updates for a given bug ID. This returns a result set for the specified ID with timestamps for when each modification took place on the bug

index=bugs id=123

There are one or more fields that can change during each update(event)

I want a table that returns: _time, field_modified, modified_value(diff from previous event)

In the case of multiple fields changing in a single update, the output could be similar to using the stats(values) or list command.

[DalJeanis]

Something like this aircode should work. Test first on a very small amount of data, for example, a few records each with two different keys.

  your search that gets the various records

 | rename COMMENT as "Sort into key and time order" 
 | sort 0 YourKeyFields  _time

 | rename COMMENT as "Rename the fields so they can be identified automatically" 
 | rename * as *_New

 | rename COMMENT as "Put the keys back where they are supposed to be" 
 | rename YourKeyFields_New as  YourKeyFIelds

 | rename COMMENT as "Copy the fields forward by key" 
 | streamstats current=f last(*_New) as *_Old by YourKeyFields

 | rename COMMENT as "Compare the fields and delete all that have not changed or are not present" 
 | foreach *_New [ 
      eval <<MATCHSTR>>_Changed = case( <<FIELD>> == <<MATCHSTR>_Old,null(),  isnull( <<MATCHSTR>>_Old ), null(),  true() "Yep")
    | eval <<MATCHSTR>>_Old = case( <<MATCHSTR>>_Changed == "Yep", <<MATCHSTR>>_Old )
    | eval <<MATCHSTR>>_New = case( <<MATCHSTR>>_Changed == "Yep", <<MATCHSTR>>_New )
    ]

After you've verified that the above does work, you can optionally add a line that deletes *_Changed and/or a line that renames *_New back to *.

Updated to correct <<MATCH>> to <<MATCHSTR>>.

[smahone11]

Thanks, however I am not sure how the following is supposed to work

| rename COMMENT as "Put the keys back where they are supposed to be"
| rename YourKeyFIelds_New as YourKeyFIelds

| rename COMMENT as "Copy the fields forward by key"
| streamstats current=f last(*_New) as *_Old by YourKeyFIelds

When I rename my fields back, the streamstats has nothing to compare as there now are now no *New fields in the data.

[DalJeanis]

@smahone11 -

Here's a run-anywhere sample that does what the code above was intended to do.

As well, we've modified the code posted above to fix some minor syntax errors.

index=_audit action=add | head 10
  | rename COMMENT as "Sort into key and time order" 
  | sort 0 action  _time

  | table _time action date_s*

  | rename COMMENT as "Rename the fields so they can be identified automatically" 
  | rename * as *_New
  | rename COMMENT as "Put the keys back where they are supposed to be" 
  | rename action_New as  action
  | rename COMMENT as "Copy the fields forward by key" 
  | streamstats current=f last(*_New) as *_Old by action
  | rename COMMENT as "Compare the fields and delete all that have not changed or are not present" 
    | foreach *_New [ 
      eval <<MATCHSTR>>_Changed = case( <<MATCHSTR>>_New == <<MATCHSTR>>_Old, null(),  isnull( <<MATCHSTR>>_Old ), null(),   true(),"yep")
    |  eval <<MATCHSTR>>_New = case( <<MATCHSTR>>_Changed == "yep", <<MATCHSTR>>_New)
    |  eval <<MATCHSTR>>_Old = case( <<MATCHSTR>>_Changed == "yep", <<MATCHSTR>>_Old)
     ] 

[somesoni2]

Can we have some sample events which shows how the changed values are logged?

[smahone11]

Well basically, its as easy as the following

Taking the following event fields returned

timestamp, id, priority, assigned, team, status

(example return)

9/17/2018 8:57:00, 524441, 1, triage, web, new
9/17/2018 9:57:00, 524441, 1, tim, service, open
9/17/2018 10:57:00, 524441, 2, tim, service, open
9/17/2018 11:57:00, 524441, 2, tim, service, in_progress
9/17/2018 12:57:00, 524441, 2, joe, service, closed

I would like to display as

_time, modified_field, new_value
9/17/2018 9:57:00, id, 524441
priority, 1
assigned, triage
team, web
status, new
9/17/2018 9:57:00, assigned, tim
team, service
status, open
9/17/2018 10:57:00......etc.