Solved

Reporting on Time Logged In for a User

  • 22 January 2021
  • 8 replies
  • 757 views

Userlevel 6
Badge +1

Has anyone come up with a good way to report on “time logged in” for a user.

I think it’s a challenge with a web application like Acumatica because you aren’t really “connected”. As far as I know, Acumatica just knows the last time that you interacted with the system.

There is a “timeout” feature so Acumatica must somehow maintain the last time that you interacted with the system so it can require you to login if your last interaction was too long ago according to the timeout setting in the web.config file.

But I want to report on “minutes logged in” for each user.

It would be cool if Acumatica tracked when it “logs you out” according to a period of inactivity, but I don’t think that’s how it works. I think it only works according to the “timeout” feature described above.

Looking at the data in the Access History (SM201045) screen, I was initially thinking to count minutes between an Operation of Login and Logout, but it appears that Acumatica only records a Logout Operation if you specifically click SIGN OUT which makes sense according to my understanding of how the “timeout” feature works.

 

The best I can come up with is to use the data in the Access History (SM201045) screen for the Operation of Access Screen. My simplistic method is to look at the first Access Screen Operation for a day and compare it to the last Access Screen Operation, counting the minutes between. But there are problems with this method.

Does anyone have a better way for reporting on time logged in for a user?

icon

Best answer by Neil Cantral 23 January 2021, 00:00

View original

8 replies

Userlevel 5
Badge +3

To augment the LoginTrace data we could take an archeological approach and dig for user activity to fill in the gaps. If a user creates or modifies documents the Audit History or scraping various document LastModifiedDateTime values could provide additional info.

Here was a quick attempt that does an okay job of capturing activity from the Accounting team:

 

It still doesn’t do a good job of capturing the activity for my users that are primarily read-only. There a lot of holes for our salespeople who can work remotely and their activity inside of Acumatica isn’t very clear.

 

 

Userlevel 6
Badge +1

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.

Userlevel 4
Badge

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

Userlevel 5
Badge +3

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

Userlevel 3
Badge

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.

 

Userlevel 7
Badge +8

@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

 

Userlevel 6
Badge +1

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.

Userlevel 7
Badge +8

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

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved