I have 2 fields in 2 different events — the matching field is the email. When one email is seen in one event, there should be another event also containing the same corresponding email.
In event 1, the email is in a field called 'user.name'.
In the corresponding event, the email is in another field called 'email'.
I want to match the same email and show the duration when the first was seen and the corresponding last event.
I then want to alert if the duration is either missing (no corresponding event or over a certain time frame)
(index=A OR index=B) (user.name=* OR email=*) ...
gratzi
@Skins ,
Since you have a condition on the "time frame" , try using transaction
(index=A OR index=B) |eval emailId=coalesce('user.name',email)
|transaction emailId startswith="string to identify starting of event"
endswith="string to identify end of event"
maxspan="time frame" keepevicted=true
| where closed_txn = 0
Reference : https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transaction
@Skins ,
Since you have a condition on the "time frame" , try using transaction
(index=A OR index=B) |eval emailId=coalesce('user.name',email)
|transaction emailId startswith="string to identify starting of event"
endswith="string to identify end of event"
maxspan="time frame" keepevicted=true
| where closed_txn = 0
Reference : https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transaction
gratzi tried that ..
for some reason its grouping the events with different emails in the same transaction which all have two events in the transaction.
I guess what i'd like is :
1st time seen, first email, 2nd time seen, second email, duration,
Do you have some sample events to share ? please mask any sensitive data
yeah i will do that - they are big events tho .. so will do it a bit later .. thx
i have got the out put of the events using this search - but still struggling grouping the events together to show :
(index=A OR index=B) (message.type=email OR "*Email Called*") earliest=-10m | spath input=msg | table _time message.details.email email | sort _time
{"preview":false,"result":{"_time":"2019-02-02T22:02:11.185+1100","message.details.email":"email_1@hotmail.com"}}
{"preview":false,"result":{"_time":"2019-02-02T22:02:18.000+1100","email":"email_1@hotmail.com"}}
{"preview":false,"result":{"_time":"2019-02-02T22:05:18.297+1100","message.details.email":"email_2@gmail.com"}}
{"preview":false,"result":{"_time":"2019-02-02T22:07:11.119+1100","message.details.email":"email_3@yahoo.com"}}
{"preview":false,"result":{"_time":"2019-02-02T22:07:20.000+1100","email":"email_2@gmail.com"}}
{"preview":false,"lastrow":true,"result":{"_time":"2019-02-02T22:07:20.000+1100","email":"email_3@yahoo.com"}}
@Skins ,
Try this
(index=A OR index=B) (message.type=email OR "*Email Called*") earliest=-10m
| spath input=msg | table _time message.details.email email | sort _time
|eval emails=coalesce('message.details.email',email)
|stats earliest(_time) as firstSeen,latest(eval(if(isnull(email),null(),_time))) as lastSeen by emails
|eval duration=round((lastSeen-firstSeen)/60,2)
You can alert |where duration>10 OR isnull(duration)
I can work - with that - thankyou gratzi
i had something similer on this line - but couldn't get it working
| stats earliest(_time) as firstSeen,latest(eval(if(isnull(email),null(),_time))) as lastSeen by email
what is latest(eval(if(isnull(email),null(),_time)))
this section saying ?