Join Two Events- Showing log on and log off activity and duration

[Becherer]

I have a project that I am working on that will display when a user logs onto a server and logs out then calculates the duration of the two giving the session time. I have all of the events for both "log on" and "log off". 

Currently I have a table that shows

Host	Account_Name	Group	Session Start	Session End	Duration
fdk-DC01	jim.smith	logon	1611665560
fdk-DC01	jim.smith	logoff	1611774585

Because each event is one entry, both logon and logoff falls in the "session start" column. Is there a way to have a row get created if all "Host, Account_Name, Group and Time" are in the event and just append the latest logoff time to the entry that matches the same "Host, Account_Name, Group".

So in other words the table would show on Tuesday, January 26, 2021 12:52:40 PM

Host	Account_Name	Group	Session Start	Session End	Duration
fdk-DC01	jim.smith	logon	1611665560

And once jim.smith logs off on fdk-DC01 then it will add "_time" to Session End on Wednesday, January 27, 2021 7:09:45 PM

Host	Account_Name	Group	Session Start	Session End	Duration
fdk-DC01	jim.smith	logon	1611665560	1611774585	11.71 hr

Any pointers would be appreciated!

[bowesmana · Accepted solution]

@Becherer 

This is a way to combine the two events

| makeresults
| eval _raw="Host	Account_Name	Group	Session_Start	Session_End	Duration
fdk-DC01	jim.smith	logon	1611665560	 	 
fdk-DC01	jim.smith	logoff	1611774585"
| multikv forceheader=1
| table Host Account_Name Group Session_Start Session_End Duration
| eval Session_End=if(Group="logoff", Session_Start, null())
| eval Session_Start=if(Group="logoff", null(), Session_Start)
| fields - Group
| stats values(*) as * by Host Account_Name
| eval Duration=tostring(Session_End - Session_Start, "duration")

The if statements are just rearranging the columns and then the stats is joining the rows together.

Duration is formatted using the tostring method, you may want to represent that in a different way which you can do just by doing the maths on the resultant different between start and end

Note that this is a simple example to show how to do the query, but note that if your search shows logoff before logon, i.e your search window captures his previous logoff before his next logon, then this breaks, so more needs to be done to solve this type of basic query. Also, if there are multiple logon/logoff in your window, this does not work well.

There are ways to address this - using stats is the better way, as it doesn't suffer from limitations with other options, but requires some data munging and testing to evaluate these conditions.

The other option is the transaction command, which has some limitations, notably that it is resource hungry and using it to find long spanning 'transactions', i.e. logon/off in your case, means it has to potentially a lot of data in memory at one time. Side effects can be that it will look like it succeeds, but will not give you a true result set.

[Becherer]

@bowesmana,

I am very close. Currently I am running into an issue where if there is a person logs onto a server multiple times, it combines. Any ideas on how to split?

Here is sample data.

Host	Account_Name	Duration	Session_End	Session_Start
fdk-DC01	jfrank		1612536779 1612558813	1612536778 1612558812
fdk-DC01	ptom	00:00:02	1612563697	1612563695
fdk-Host01	jfrank	00:00:05	1612539322	1612539317
fdk-Host03	bhill		1612540329 1612543822	1612540323 1612543816

[tread_splunk]

Works with multiple log-on/log-off.  And where you have a log-on without a corresponding log-off.

| makeresults 
| eval _raw="Host	Account_Name	Group	Session_Start	Session_End	Duration
fdk-DC01	jim.smith	logon	1611665560	 	 
fdk-DC01	jim.smith	logoff	1611774585	 	 
fdk-DC01	jim.smith	logon	1611665570	 	 
fdk-DC01	jim.smith	logoff	1611774595" 
| multikv forceheader=1 
| table Host Account_Name Group Session_Start Session_End Duration 
| eval Session_End=if(Group="logoff", Session_Start, null()) 
| eval Session_Start=if(Group="logoff", null(), Session_Start) 
| fields - Group 
| stats values(*) as * by Host Account_Name 
| eval StartEnd=mvzip(Session_Start,Session_End,":") 
| mvexpand StartEnd 
| rex field=StartEnd "(?<Session_Start>.*):(?<Session_End>.*)" 
| eval Duration=tostring(Session_End - Session_Start, "duration")

[Becherer]

@tread_splunk 

What if I wanted to add a column of the "Session_Start" epoch time as readable time.

Maybe the table would be..
But for some reason, by doing this, it added more than one time.  Any Ideas?

| makeresults 
| eval _raw="Host	Account_Name	Group	Session_Start	Session_End	Duration
fdk-DC01	jim.smith	logon	1611665560	 	 
fdk-DC01	jim.smith	logoff	1611774585	 	 
fdk-DC01	jim.smith	logon	1611665570	 	 
fdk-DC01	jim.smith	logoff	1611774595" 
| multikv forceheader=1 
| table Time Host Account_Name Group Session_Start Session_End Duration 
| eval Session_End=if(Group="logoff", Session_Start, null()) 
| eval Session_Start=if(Group="logoff", null(), Session_Start) 

| eval Time = strftime(Session_Start, "%m/%d/%y %H:%M:%S")

| fields - Group 
| stats values(*) as * by Host Account_Name 
| eval StartEnd=mvzip(Session_Start,Session_End,":") 
| mvexpand StartEnd 
| rex field=StartEnd "(?<Session_Start>.*):(?<Session_End>.*)" 
| eval Duration=tostring(Session_End - Session_Start, "duration")

[Becherer]

@tread_splunk,

That worked great! Thank you!