topic Need to combine nearest _time values for each field in Splunk Search

Need to combine nearest _time values for each field

SathyaNarayanan — Thu, 23 Jan 2020 06:02:12 GMT

Hi Splunker,

In my application when there is action, 3 events will be created for it.

Eg :

      _time                    field_1           old_value             new_value             user                name
2020-01-19 13:28:55              ABC                                  C51R                6191                 355767013
2020-01-19 13:28:55              DEF                                  C51R                                     355767013
2020-01-19 13:28:55              DEF             C53R                                                     355767013
2020-01-19 12:09:58              ABC                                  C54L                6567                 355767013
2020-01-19 12:09:58              DEF                                  C54L                                     355767013
2020-01-19 12:09:57              DEF             C54R                                                     355767013

From the above event, u can see 3 different rows created for each action, some time there might be 5 to 10 seconds gap in _time in the events.

name is common and unique in the events, i wanted to know the old value, new value and the user chagned with the _time in single row.

My expectations.

      _time                      old_valu       new_value             user                name
2020-01-19 13:28:55               C53R           C51R                6191              355767013

2020-01-19 12:09:58               C54R           C54L                6567              355767013

Thanks in advance.

Note: i tried | transaction span=1m _time , | bucket span=1m

Re: Need to combine nearest _time values for each field

kamlesh_vaghela — Thu, 23 Jan 2020 08:03:11 GMT

@SathyaNarayanan

Can you please try this?

YOUR_SEARCH
| eval t=_time | bucket _time span=1m | stats latest(t) as t values(old_value) as old_value values(new_value) as new_value values(user) as user by _time, name | eval _time=t | table _time old_value new_value user name

Sample:

| makeresults 
| eval _raw="       time                    field_1           old_value             new_value             user                name
 2020-01-19 13:28:55              ABC                                  C51R                6191                 355767013
 2020-01-19 13:28:55              DEF                                  C51R                                     355767013
 2020-01-19 13:28:55              DEF             C53R                                                         355767013
 2020-01-19 12:09:58              ABC                                  C54L                6567                 355767013
 2020-01-19 12:09:58              DEF                                  C54L                                     355767013
 2020-01-19 12:09:57              DEF             C54R                                                         355767013" 
| multikv forceheader=1 
| eval _time=strptime(time,"%Y-%m-%d %H:%M:%S") 
| table _time field_1 old_value new_value user name
| rename comment as "Upto this is for data generation only" 
| eval t=_time | bucket _time span=1m | stats latest(t) as t values(old_value) as old_value values(new_value) as new_value values(user) as user by _time, name | eval _time=t | table _time old_value new_value user name

Re: Need to combine nearest _time values for each field

to4kawa — Thu, 23 Jan 2020 11:49:39 GMT

| makeresults 
| eval _raw="time,field_1,old_value,new_value,user,name
2020-01-19 13:28:55,ABC,,C51R,6191,355767013
2020-01-19 13:28:55,DEF,,C51R,,355767013
2020-01-19 13:28:55,DEF,C53R,,,355767013
2020-01-19 12:09:58,ABC,,C54L,6567,355767013
2020-01-19 12:09:58,DEF,,C54L,,355767013
2020-01-19 12:09:57,DEF,C54R,,,355767013" 
| multikv forceheader=1
| eval _time=strptime(time,"%Y-%m-%d %H:%M:%S") 
| table _time field_1 old_value new_value user name
    `comment("above is your sample. from here, the logic")` 
| transaction maxspan=10s name 
| table _time old_value new_value user name

Hi, @SathyaNarayanan
try transaction maxspan= .

Re: Need to combine nearest _time values for each field

SathyaNarayanan — Wed, 30 Sep 2020 03:49:07 GMT

Hi @kamlesh_vaghela

As per your query will show only one change, in my scenario there will be several changes.

for example.

OLD_VALUE NEW_VALUE
C53R C56
C51 C53R
C54 R C51

Thanks