How do you make a table of matching emails and duration between events and duration?

[Skins]

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

[renjith_nair · Accepted solution]

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

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,

[renjith_nair]

Do you have some sample events to share ? please mask any sensitive data

[Skins]

yeah i will do that - they are big events tho .. so will do it a bit later .. thx

[Skins]

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"}}

1. table of 1st seen email, last seen email, duration.
2. then create an alarm where duration >10m or no email seen in the second event.

[renjith_nair]

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

[Skins]

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 ?