Splunk Search

Need to combine nearest _time values for each field

SathyaNarayanan
Path Finder

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

Tags (2)
0 Karma

to4kawa
Ultra Champion
| 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= .

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
0 Karma

SathyaNarayanan
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...