Splunk Search

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

Becherer
Explorer

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

HostAccount_NameGroupSession StartSession EndDuration
fdk-DC01jim.smithlogon1611665560  
fdk-DC01jim.smithlogoff1611774585  
      

 

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

HostAccount_NameGroupSession StartSession EndDuration
fdk-DC01jim.smithlogon1611665560  

 

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

HostAccount_NameGroupSession StartSession EndDuration
fdk-DC01jim.smithlogon1611665560161177458511.71 hr

 

Any pointers would be appreciated!

Labels (3)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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

 

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma

Becherer
Explorer

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

 

HostAccount_NameDurationSession_EndSession_Start
fdk-DC01jfrank 1612536779
1612558813
1612536778
1612558812
fdk-DC01ptom00:00:0216125636971612563695
fdk-Host01jfrank00:00:0516125393221612539317
fdk-Host03bhill 1612540329
1612543822
1612540323
1612543816

 

 

 

0 Karma

tread_splunk
Splunk Employee
Splunk Employee

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
Explorer

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

 

 

0 Karma

Becherer
Explorer

@tread_splunk,

That worked great! Thank you!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...