Tracking Daily User Login Hours Through VPN Logs

Scenario:

Tracking employee wise daily login duration via VPN connection logs.

Requirement:

Our objective is to determine how long users have been connected to the VPN for assessing attendance and daily working duration.

About the Use case
Let’s analyse the input data that we have:

Employees VPN Logs Table: It contains all the VPN connection logs of all the employee of the organization.

Expected Output: We need employee-wise working duration.

Solution:

The following are the logical steps:

• Extract "Status" and "User" from the "Message" column using Split column function
• Remove unwanted columns.
• Filter out only the connected and disconnected status in case the text contains any other status message.
• Derive the next status and its time.
• We will filter the status column to only include rows where the status is "connected"
• Calculate working hours.
• Aggregate the connect time to minimum and disconnect time to maximum along with summing up the duration, as there might be case of multiple connection and disconnection entries for a user in a single day.
• Rename the columns.

We will obtain the expected result.

Now we will implement same in Smarten

Employees VPN Logs Details : This is the list of all the users with their VPN connection details.

Step 1 : Derive "Status" and "User" columns from the "Message" column

In our data we are getting the connection and disconnection status of the user in a single string logged in the “Message” columns.

So we would require to extract the User name and its connection and disconnection status with the help of Split function available in Smarten.

For instance, Here we have “SSL VPN User ‘bhumi’ connected” so first we will split Status connected with the help of space as a separator and then employee name bhumi considering single quote as a separator.

Right-click on the "Message" column, select an option to split, and specify the separator to use for splitting.

Split the "Message" column based on separator "space" from the right side. After splitting we will get 2 columns one named Message_1 and another named Message_2. 
Here the column named Message_2 will have connection and disconnection status. So we will rename column names Message_2 as "Status".

Below is the resultant dataset after splitting Message column to extract Status column.

Now to get the employee name split above derived Message_1 on the basis of “single-quote (‘)” from the right.

After splitting we will get 2 columns one named Message_1 and another named Message_1_1. 
Here the column named Message_1_1 will have User name. So we will rename column named Message_1_1 as "Employee Name".

Step 2: Remove the unwanted columns.

Keep only Time, Status, and User columns and remove all other unwanted columns.

Below is the resultant dataset after splitting Message column to extract Status and User columns.

For instance to remove column Message_1_1, Right click on the column, there we have an option to Remove and then select this column option.

After removing unwanted columns we will get below resultant dataset.

Step 3: Filter out only connected and disconnected status.

For filtering right click on the Status column.

Then add condition in filter of only considering the records where the Status is connected or disconnected.

Below is the resultant dataset after filtering, we will obtain only the connected and disconnected times of each user.

Step 4: Find the next status and the time of the next status using Custom SQL.

In order to know the next status of any user we will use below query, this will help us in calculating the working hours.

For instance, if we have data where the connected status for the user "Bhumi" is recorded at 10/05/2024 10:00:00am and the disconnected status is recorded in the next row at 10/05/2024 12:00:00pm.
To calculate the time difference between connected and disconnected time we would require two different columns one for connection time and another for disconnection time.

To get this we can use the lag window function to get the record of disconnection in the the next status and its time adjacent to connected time.

Query - SELECT *,

lag(a.Status,-1) OVER (PARTITION BY a.User ORDER BY a.Time) as NextStatus,

lag(a.Time,-1) OVER (PARTITION BY a.User ORDER BY a.Time) as NextStatusTime

FROM VPN Connection Details a

Below is the resultant dataset.

Step 5: Filter the status column with Status "connected".

We filter the status column to only include rows where the status is "connected" because we only want to calculate the connection hours, what happens between the duration when the user disconnects and connects again is not something we need to take into consideration.
So we are only going to consider the records which will help us derive the time between connection and the disconnection/connection after that.

For filtering out rows with only connected status, Right click on the Status column and filter it out with the function “Rows with this column value” available in the “Filter” function.

Step 6: Add a custom column to calculate the login hours

Now we will calculate the login hours which will be the difference between “Time” and “Next Status Time” columns having the connection time and disconnected time respectively.

Expression - truncate((dateDiff( "s", NextStatusTime, Time )/3600),2)

For instance, for the user "Bhumi" the connection time is recorded of 10/05/2024 10:00:00am and the disconnect time is recorded of 10/05/2024 12:00:00pm.
Hence the login Time is of 2hrs.

Below is the resultant dataset.

Step 7: Aggregate the dataset.

We will do The final aggregation to derive the total working hours of a user by summing up the working duration against the first login and last logout time.

Step 8: Renaming the columns.

Rename Time as Time_IN and NextStatusTime as Time_OUT

Below is the resultant data wherein we get the total working hours.