12 Replies Latest reply on Feb 25, 2016 8:28 PM by andrew

    Getting a list of "old" users

    Mark Oliver Adventurer

      Does anyone know of a way (presumably using Pharos Reports program) to generate a list of users who have *not* had a transaction since a particular date?

      My goal is to generate a list of people who haven't printed / copied since Sept 1 2014 (as an example) and remove them all in bulk.

      Thanks!

        • Re: Getting a list of "old" users
          Steven English Guide

          Mark Oliver,

           

          Let me know if the following doesn't get it sorted out for you.  The query is going to look for the most recent PRINT transaction for each user (and for anonymous transactions - the NULL user).  Any users whose most recent transaction is older than the cutoff date will make the list.  You could get a lot fancier if you wanted, but I figured this would be a good start.  If you want to see any transaction (not just print transactions), just add a pair of hyphens to comment out the line that references the transaction type (t_type).

           

          Regards,

          Steven

           

          Easy cut-n-paste...

          ------------------------------------------------------------------------------------

          USE [pharos]

          GO


          DECLARE @CutOffDate DATETIME

          SET @CutOffDate = '2014-09-01 00:00:00.000'

           

          SELECT

                [Pharos Logon ID]      = t.user_id

               ,[Pharos Logon ID]      = u.id

               ,[Most Recent Trans]    = MAX(t.time)

          FROM transactions t

               LEFT JOIN users u ON u.user_id = t.user_id

          WHERE

               t.time < @CutOffDate

               AND t.ttype_id = 2

          GROUP BY t.user_id, u.id

          ORDER BY MAX(t.time) DESC

           

          ------------------------------------------------------------------------------------

           

           

          USE [pharos]
          GO
          
          DECLARE @CutOffDate DATETIME
          SET @CutOffDate = '2014-09-01 00:00:00.000'
          
          SELECT
                [Pharos Logon ID]      = t.user_id
               ,[Pharos Logon ID]      = u.id
               ,[Most Recent Trans]    = MAX(t.time)
          FROM transactions t
               LEFT JOIN users u ON u.user_id = t.user_id
          WHERE 
               t.time < @CutOffDate
               AND t.ttype_id = 2
          GROUP BY t.user_id, u.id
          ORDER BY MAX(t.time) DESC
          

           

            • Re: Getting a list of "old" users
              Paul LaFollette Guide

              After getting a list of “old” users (yeah, I’m also interested in this), before removing those “old” users, don’t you have to remove the transactions relating to those users (in order to be able to remove the “old” users)?

               

              Thanks,

              - Paul L.

                • Re: Getting a list of "old" users
                  Steven English Guide

                  One could expand on the query to generate of list of users that could be removed via the batch loader (useful if desiring an automated system that could be scheduled), or it could be modified to add a flag (text string) to the custom 2 field that would make it easy to use the Administrator to search/filter based on that flag and then ARCHIVE***** users from there, or it could move the users into a user group for even easier ARCHIVING*****.

                   

                  ****** WARNING/DISCLAIMER *****

                  Having had numerous people that archive users, computers, devices, servers, etc... and then call me asking what happened to their Pharos Reports.... Please please please, make sure that any reports you need to run are processed prior to archiving any thing.  Once the transactions are gone, well let's just say that there is not an easy way to put them back into the database, and the reports are based off of the transactions in the database at the time the report is run.

                  ****** END OF DISCLAIMER *****

                   

                  Getting even "fancier", one could disable the triggers on the transactions table and set the user_id to null, the re-enable the trigger.  It should be tested first, but off the top that sounds like it would do the trick of keeping the transactions in the db for reports, while allowing the removal of users.  I also thought about the possibilities of adding

                   

                  Now having said all that, maybe I should have started with, what is the need or desire to remove the users?  They really don't take up that much space, and I can't imagine them causing any humanly-noticeable slow down in performance.

                   

                  It sounds like someone should make a feature request to Pharos to add this in.  The SignUp product has the ability to anonymize data after 24 hours.  SignUp visitors and Print Center guests have an interval at which they get auto-purged (though Print Center guests are NOT purged if they carry a balance).  I imagine it would be relatively straight forward to add an option to strip the username from transactions after X number of days or to add an option to purge users after X number of days and anonymize their data - especially seeing as how there is already an option to automatically purge data after X number of days.

                   

                  Regards,

                  Steven

                   

                  Edit: Paul LaFollette, to answer your questions directly, you are correct that Pharos will not allow a user to be deleted if they have any transactions in the database.  However, instead of having to manually go find the transactions associated with that user, the archive feature will do that for you (see warning above).  Make sure you are only archiving the one user or the user group, not everyone!  A user can be set to inactive in Pharos, which is why I typically add a bit to my scripts that verify the user is active in Pharos before allowing the logon, even if AD/LDAP authenticates the user successfully.

                    • Re: Getting a list of "old" users
                      Nic Meadows Ranger

                      Just to compliment Stevens answer, we generate a "userload" format file to archive old users, this can then be automated using windows task scheduler. The query below will give you the required fields in the correct order to be used with batch userload in Pharos v8.2 and above. Just amend the where clause to suit your requirements. (Can also be used to export your current users and import into a clean system when upgrading)

                       

                      SELECT

                          u.last_name,

                          u.first_names,

                          u.id,

                          u.billing_option,

                          g.id as [group],

                          c.name as [access_level],

                          technology_purse as [Purse1],

                          tuition_purse as [Purse2],

                          user_purse as [Purse3],

                          u.address,

                          u.phone,

                          u.comment,

                          r.name as [Rate],

                          u.card_id,

                          u.middle_initial,

                          u.user_alias,

                          u.email,

                          u.custom1,

                          u.custom2

                      FROM users u

                      LEFT JOIN groups g               on u.group_id  = g.group_id

                      LEFT JOIN users_balances pb      on u.user_id   = pb.user_id

                      LEFT JOIN rates r                on u.rate_id   = r.rate_id

                      LEFT JOIN role c                 on u.role_id   = c.role_id

                      WHERE u.active = 0

                      ORDER BY u.user_id asc

                • Re: Getting a list of "old" users
                  Mark Oliver Adventurer

                  Steven - While I mostly understand the SQL code that you've written, I don't know how to execute it.

                    • Re: Getting a list of "old" users
                      Steven English Guide

                      Mark,

                       

                      I always use Microsoft SQL Server Management Studio to execute my queries because it is almost always already installed on the SQL server, and it does everything I need.  The process would consist of launching the program and connecting to a SQL server (instance), then opening a query window into which the query is pasted before executing it.  The first couple of minutes of the youtube video below will probably help you to see it in a more useful way and in a more concise timeframe than I could explain it.  You might want to talk to one of your SQL admins to help run the query in case your account doesn't have permissions to connect to SQL or the server on which SQL runs.  You can install Management Studio locally, but you might get blocked by the firewall if one exists between your machine and the SQL server.

                       

                      https://www.youtube.com/watch?v=V4hTLja7jU8

                       

                      Regards,

                      Steven

                        • Re: Getting a list of "old" users
                          andrew Tracker

                          Steven English wrote:

                           

                          what is the need or desire to remove the users?  They really don't take up that much space, and I can't imagine them causing any humanly-noticeable slow down in performance.

                           

                          Let me toss an answer to this out there. We desire to "sweep" all the "leftover" funds and users off our records. In other words, all the users with an unusable balance (say, $0.02 when the minimum cost is $0.05) to remove this from the reports where we run totals to see what "outstanding balances" exist. It's not a big deal, and why we still aren't that worried about implementing it. But it IS something which comes up virtually EVERY TIME we have a meeting where funds is discussed. I would like to say "we can do that" AND actually already have the solution in hand. Until now, it's always been "yeah, we can do that" and then forget about it because it always "goes away".

                          Another answer to this would include carrying forward such balances and users to a new system. I personally advocated the "clean slate" method, wherein a new system requires the users to make new accounts. However, that does not work well in the real world, This would be a nice way to drop some of the "fluff" before importing to a new environment.

                          A third answer might include: if you autoload your accounts, 3 or so times annually, at 5-10k accounts per load, there does end up being buckets of "garbage" in there. Although perhaps not large in storage, I have to think that if your active and useful account number is really only 1/3 of what is being stored, it would be handy to drop the fluff.


                          Thank you guys for the excellent question, excellent scripts, and excellent ideas on making it safer (including but not limited to not doing it). I hope I never feel the need to use them!

                        • Re: Getting a list of "old" users
                          Nic Meadows Ranger

                          You can use SQL Management studio GUI, osql or sqlcmd which are command line utilities. Alternatively the attached vbscript will use the Pharos database service credentials from the registry, run the sql in the file and write the results to a text file. This was written specifically to export users for use with userload. if you modify the where clause in the script with some date/time elements like in Stevens query it'll list the old users. Rename with a .vbs extension and run on the server that has the Pharos database server service running.

                          1 of 1 people found this helpful
                        • Re: Getting a list of "old" users
                          Richard Post Guide

                          As Steve pointed out, usually there is no need to remove accounts.  The storage space is nominal, deactivating accounts (in the directory or billing gateway) is equally effective for security, and the risk may be greater than the gain.  That said, should you decide that accounts must be purged, here comes the disclaimer...

                           

                          *** Please be extraordinarily careful in executing any SQL statements that modify the Pharos database. ***

                           

                          *** Always make and verify an additional backup of your database immediately before any changes. ***

                           

                          Steven English and Nic Meadows are remarkably talented engineers and versed in Pharos, SQL, scripting, as well as many other areas.  We are super thankful of all the assistance they continue to provide in the Community.   The information they have presented is accurate, but please be very mindful making any changes to your production databases, and only do so if you completely understand what you are doing.

                           

                          I guess the official statement from Pharos would be that account and transaction modification is only officially supported via the commands available with the Pharos Administrator and associated Pharos tools such as UserLoad.exe.

                          1 of 1 people found this helpful