I would like to figure this out as well. I have a few users that I would like to remove from our file vault if they don't need access, i.e. if they haven't logged into EPDM in a while. I know where to find the log files for my local computer, and I can see the time stamp for other users if they're currently logged in, but I can't find anything for locating a time stamp for logged-out users.
I've done this for many of my clients by a very simple EPDM addin. This addin wakes up when a user logs in and records a date, time, computer, and username of login. This data is recorded in a secondary database in SQL server and then a SQL report server reads that data to graphically display charts showing the login trends.
Hope this helps,
is it possible to get more informations?
Unfortunately Mario, we discontinued that product for various reasons. The last login time is not "officially" supported by SOLIDWORKS and our product took advantage of some SQL code that would void a customer's support contract.
Feel free to look at our PDM Auditor product. All add-ins in our store is on sale now thru year end for $19.99 per month.
Believe in The Q!
I solved that via SQL without an add-in.
The possibility of a logging is given, the reporting must be done by yourself. I hope it helps.
Thanks Ulf. This was our approach as well, but we learned that adding tables to the SOLIDWORKS PDM vault database IS NOT supported by SOLIDWORKS so USE AT YOUR OWN RISK!!
As mentioned, our query did the same thing but here are a few things to keep in mind and our recommendations:
- Querying the LoggedInUsers table of the ConisioMasterDb is only a snapshot of the logged in users at the moment the query is executed
- Remember to convert the server time to your local UTC time
- When users log out of PDM, their user, computer, and vault drop out of the LoggedInUsers table
- I recommend creating a SQL agent job that runs every 2-3 minutes provides a very granular picture of the logins without performance degradation to the PDM users experience
- Create that secondary database table OUTSIDE your PDM vault and that way, you can query that table with a select MAX statement to get the latest login recorded
- The table can continue to grow for years without a storage concern. After running for 3 years, our table contains 200k records and is about 3mb of storage space
Hope this helps!
I absolutely agree with you that third-party databases must not be manipulated. Therefore, this installation guide is included in the header of my SQL script.
* MIT licensed
* Copyright (c) 2014 Ulf-Dirk Stockburger
* Database access and the authorization to create databases and SQL Server Agent jobs.
* Knowledge about the SQL Server and queries
* 1. Create a table used to log user logins into a database. I would suggest that you create a separate database that is only used for this purpose.
* 2. Create the Stored Procedure, which compares the table [ConisioMasterDb].[dbo].[LoggedInUsers] with the log table, and appends new users to it.
* 3. Create a job that is executed in the specified interval to check the logged-in users in EPDM. In the jobstep the following settings have to be set: @subsystem=N'TSQL' and @command=N'EXECUTE [YourDataBase].[dbo].[LogEpdmUserLogin]'.
* 4. Start the newly created job.
* Normally I would do this via DML trigger. As far as I know, however, the EPDM database is not allowed to be manipulated, as otherwise problems with support may occur. Therefore I solve the problem via polling with the SQL Server Agent.
The different time zones are not relevant for our company. There are enough queries on the Internet to do this, if necessary.
Of course, you're right about the growing database. If it's required to keep logins in a defined period of time, e.g. 180 days, I would insert this SQL statement
DELETE FROM [YourDataBase].[dbo].[EpdmUserLog] WHERE CAST([login_time] AS DATETIME) <= DATEADD(DAY, -180, GETDATE());
in the stored procedure after
SET NOCOUNT ON.
I hope it helps.
This can be accomplished without using SQL by using the GetAddinInfo method. It's called whenever a user logs in.