topic Re: How do you search to return a table of only fields that change between events? in Splunk Search

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

smahone11 — Tue, 29 Sep 2020 21:19:25 GMT

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.

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

somesoni2 — Mon, 17 Sep 2018 20:13:04 GMT

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

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

smahone11 — Tue, 29 Sep 2020 21:19:32 GMT

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.

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

DalJeanis — Mon, 17 Sep 2018 22:25:16 GMT

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

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

smahone11 — Tue, 29 Sep 2020 21:16:32 GMT

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.

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

DalJeanis — Tue, 18 Sep 2018 00:39:01 GMT

@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)
     ]