Lookup most recent login before event

[EliBildman]

Hi all,
I have created a table that will show all FireEye events logged that contain a certain MAC address. This is the query:

eventtype=fe product="Web MPS" (src_mac="00:26:99:bd:24:60" OR dest_mac="00:26:99:bd:24:60") *

| eval sig= coalesce(signature, sig_name), _time = strftime(_time, "%D %H:%M:%S")

| table src_ip, src_mac, dest_ip, dest_mac, category, sig, _time

| rename src_ip as Source, src_mac as "Source MAC", dest_ip as Destination, dest_mac as "Destination MAC", category as "Connection Type", sig as Malware, _time as Time 

The MAC address is passed in through the parameter %src_mac%. I'd like to add the user associated with %src_mac% to the end of the table. Users are not logged in the FireEye events, but they are contained in login authentication logs (eventtype = *_auth_*) as the field "user". The login event that needs to be read has to be the last event before the time that the FireEye event was recorded.

So I need to query logs where eventtype = *_auth_* AND MAC = %src_mac% from the same earliest time through the time of the FireEye event in question, take the "user" field from the most resent log, and append that onto the FireEye event. How should I go about doing this?

Thanks

[DalJeanis]

Here's one way...

| multisearch 
    [
    search eventtype=fe product="Web MPS" 
   (src_mac="00:26:99:bd:24:60" OR dest_mac="00:26:99:bd:24:60")
    | eval mac="00:26:99:bd:24:60"
    | eval Malware = coalesce(signature, sig_name)
    | fields _time, mac, src_ip, src_mac, dest_ip, dest_mac, category, Malware 
    ]

    [search eventtype = *_auth_* mac="00:26:99:bd:24:60" 
     | fields _time mac user  
    ]

| rename COMMENT as "Sort the records into order and then copy forward the user names"
| sort 0 _time
| streamstats last(user) as LastUser by mac

| rename COMMENT as "Now kill everything that isn't the desired FireEye events"
| where eventtype="fe"

| rename COMMENT as "AND FINALLY, pretty up the formats and names now that it won't muck with the process."
| eval  Time = strftime(_time, "%D %H:%M:%S")
| table  Time, src_ip, src_mac, dest_ip, dest_mac, category, Malware, LastUser
| rename src_ip as Source, src_mac as "Source MAC", dest_ip as Destination, dest_mac as "Destination MAC", category as "Connection Type"

Technically you don't have to have _time in the list for fields, but I like having it there to remind me where it's coming from. You could also replace the multisearch with a combined search like the following -

  eventtype = (*_auth_*  OR (eventtype=fe product="Web MPS")) "00:26:99:bd:24:60"
| search src_mac="00:26:99:bd:24:60" OR dest_mac="00:26:99:bd:24:60" OR mac="00:26:99:bd:24:60" 
| eval Malware = coalesce(signature, sig_name) 
| fields _time, src_ip, src_mac, dest_ip, dest_mac, category, Malware, mac, user 
| eval mac="00:26:99:bd:24:60" 

While I'd prefer to get the mac for the fe record from the src_mac or the dest_mac, any way that gets coded would look ugly. If you plan to run a large number of macs through this process at the same time, then that code will need to be revisited. The by mac in the streamstats command is redundant when you have only one mac, but I put it in because this kind of code is likely to get expanded to pull more than one at a time (for example with an input lookup table) at some point.