Join multiple events and separate timestamp fields

[cmccartneyocto]

I've been having difficulty with this for a while and looking for some help. 

I'm attempting to find users logging and whether they are using username/password or smart card. 

I search for 4768 and return the user, ip, preauthentication type, and timestamp from indexA. Then eval the timestamp for each of those events to Logon_Timestamp. The search time is the last 30 days.

I then search indexB for each result of the main search for action="Request" events around the same timeframe as the main search, get the timestamp, xuser, zuser, and then eval the timestamp for all of those events to Request_Time.

I search indexB again for each result of the main search for action="Connect" events around the same timeframe as the main search, get the timestamp, xuser, xhost, and eval the timestamp for all of those events to Connect_Time.

After getting all of that information and renaming Account_Name and xuser to Admin_User, I need to create a table joining all of them with the following columns:

Logon_Timestamp, Logon_Method, Request_Time, Connection_Time, Admin_User, Requesting_User, ipaddress, Environment, hostname, group

I seem to be able to get this partially working using different methods like join, multisearch, and append but trying to join all of these events, renaming each of their timestamps, and joining them together by Admin_User is showing as quite a challenge. 

Join gets the information I want, but matching up timestamps between the three events doesn't seem possible and it takes a while.

Append seems to return most of what I need and I can organize it by making the two indexB queries subsearches, but I can't eval the timestamp fields for them and put them into their own columns.

Multisearch looked really promising, but only the results of the first search are returned.

I've had a good deal of success using stats and putting all of them into one search using OR, but once again I can't successfully eval the timestamp fields.

Below is one of my 20 or so queries that I've been using that provides me with the closest results, but I can't get the event timestamps matched up to ones that occur around the same time for the user.

index=indexA sourcetype=WinEventLog:Security "EventCode=4768" ("-admin" OR "-service" OR "-user")(Pre_Authentication_Type != -) Client_Address != "10.x.x.x" Client_Address != "10.x.x.x" 
| eval Logon_Timestamp = strftime(_time, "%m-%d-%Y %H:%M:%S") 
| rename Account_Name as xuser
| join max=0 type=left xuser [search index=indexB host="10.x.x.x" ("device=10.x.x.x" OR "device=10.x.x.x") action="Request" | rename zuser as "Requesting_User" |eval Request_Time = strftime(_time, "%m-%d-%Y %H:%M:%S")]
| join max=0 type=left xuser [search index=indexB action="Connect" | eval Connection_Time = strftime(_time, "%m-%d-%Y %H:%M:%S")]
| rename xuser as "Admin_User" | eval Admin_User=lower(Admin_User) 
| eval ipaddress=replace(Client_Address,"::ffff:","")
| eval Logon_Method=case(Pre_Authentication_Type < 14, "Username/Password", Pre_Authentication_Type > 14, "PIV")
| eval Request_Time = if(isnull(Request_Time), "No Request Found", Request_Time)
| eval Connection_Time = if(isnull(Connection_Time), "N/A", Connection_Time)
| lookup assetlist.csv ipaddress OUTPUTNEW Environment, hostname, group
| where like(hostname, "%-%-%") AND !like(hostname, "ex-host-name-%")
| table Logon_Timestamp, Logon_Method, Request_Time, Connection_Time, Admin_User, Requesting_User, ipaddress, Environment, hostname, group

Anyone have any experience with something like this?