Reporting on Time Logged In for a User

Interesting approach @Neil Cantral. I didn’t think about using Audit History. That would work if there were enough Audit History events being captured. I suppose I could even use transactions created.

Your Salesperson example brings up a broader point that I hadn’t considered, view only users. The kind of trail they leave is different from the kind of trail left by a data entry user.

@Neil Cantral and @TimRodman I put together a blog post that covers some of these points.  As you both determined, there are some data holes. 

https://www.swktech.com/acumatica-user-activity-performance-monitoring/

Look forward to your feedback.

@jgress   Your post is fantastic. It did a good job of highlighting the uses and limitations of the data that Acumatica collects.

With the data available I found that the audit history access screen data has been the most useful for me. User adoption of a particular screen/report could be easily gauged with a quick pivot on the Access Screen audit history. It really helped highlight gaps in training since I could see how often a user was accessing a screen and could see if a user wasn’t aware of a screen that their peers were using regularly.

Really wish I knew about this when we were implementing. Would have made the transition much smoother since we had a lot of users getting lost/frustrated/creative when a solution to their problem already existed.

I created a SQL view for this, it has some performance issues depending on number of users and how long the company has been in operation so I added a date in the where clause.
 

WITH CTE AS (SELECT        CompanyID, username, date, Operation, CONVERT(Date, Date, 102) AS NewDate, sum(CASE WHEN Operation = '1' THEN 1 ELSE 0 END) OVER (partition BY Username
ORDER BY Date) AS group_id
FROM            Dbo.LoginTrace), B AS
    (SELECT        CompanyID, Username, Newdate, group_id, Min(Date) AS FirstDate, Max(Date) AS LastDate, DATEDIFF(Second, Min(Date), Max(Date)) AS TotalSeconds
      FROM            CTE
	  WHERE CompanyID >1
      GROUP BY CompanyID, Username, Newdate, group_id)
    SELECT        CompanyID, Username, CAST(NewDate AS DATETIme) AS NewDate , SUM(TotalSeconds) / 60.00 AS TotalMinutes, CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as int) AS RowN
     FROM            B
	 WHERE  CAST(NewDate AS DATETIME) >='2021-01-01'
     GROUP BY CompanyID, Username, NewDate

The first part of the view looks for the login operation in the LoginTrace table which is 1 and groups all activities/navigation to other screens as part of that login.  Users don’t always logout so there won’t always be a logout in the LoginTrace table so I reset after each login and order by date. Think the screenshot below will make more sense.

The second part of the view does a datdif on the seconds between the login time and last activity time for that group.

The last part of the view converts the seconds to minutes and I added a row number that can be used as the IsKey = true when adding the view as a DAC in a customization project.

Below is a screenshot of what the output is. I have probably missed some logic somewhere but this is reasonably accurate if you want to look at time spent in the system per user per day.

@TimRodman @jgress @Neil Cantral @frederickodendaal77 

Let see what we know and what we have:

We know in Users table we have the user LastLoginDate and LastActivityDate.

We know either user logs out using logout button that system will create a record in LoginTrace table or platform will kick the user out after the set duration of inactivity based on the WebConfig.

We need to track users login duration. So we can create a simple table let say LoginHistory with columns CompanyID, UserID, LoginDate, LogoutDate, Duration

Then we will create a FieldUpdated event handler that happens on user login time:

1- Update Previous Login’s Record LogoutDate:

We will find the users last record from LoginHistory that doesn’t have LogoutDate and update conditionally as follows:

If there is Logout record in LoginTrace that falls between the previous LoginDate and current LoginDate, we will get that date and update the LogoutDate and Duration.

Otherwise, the platform has terminated  the user session because of inactivity so we get the LastActivityDate + WebConfig Timeout (either a fix value or we can add a field to GeneralPreferences) and update the LogoutDate and Duration.

2- Insert current login record with null LogoutDate into LoginHistory

That would totally work. I think the web.config file timeout would have to be set to a fairly low value which would kick users out more frequently and be annoying to them, but it could be worth it for a company that wants a very accurate measurement of time that employees spend in Acumatica.

@TimRodman Did you ever develop this and make it work?