8 Replies Latest reply on Oct 31, 2017 9:24 PM by Lee CS Young

    How to get last login time stamp

    Masayuki Honchi

      I'm using EPDM 2014. Is there any method to get user's last login time stamp?

      I would like to do the ID inventory check. For instance, If a user doesn't login for several months, the ID will be inactivated.

        • Re: How to get last login time stamp
          Austin Broeker

          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.

          • Re: How to get last login time stamp
            Tim Webb

            Hi guys,

            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,

            Tim CEPA

            http://www.equivaq.com

              • Re: How to get last login time stamp
                Mario Zahren

                Hi Tim,

                 

                is it possible to get more informations?

                 

                Cheers

                Mario

                  • Re: How to get last login time stamp
                    Tim Webb

                    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!

                    Tim CEPA

                    http://www.equivaq.com

                    • Re: How to get last login time stamp
                      Ulf Stockburger

                      Hi Mario

                       

                      I solved that via SQL without an add-in.

                      http://bii.ustockburger.de/download/sqlqueries/EpdmLoginHistory.7z

                      The possibility of a logging is given, the reporting must be done by yourself. I hope it helps.

                       

                      BiI SAP-PDM

                        • Re: How to get last login time stamp
                          Tim Webb

                          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!

                          Tim CEPA

                          Believe in The Q!

                            • Re: How to get last login time stamp
                              Ulf Stockburger

                              Hi Tim

                               

                              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

                              *

                              * Precondition:

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

                              *

                              * Remarks:

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

                               

                              BiI SAP-PDM