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).
DECLARE @CutOffDate DATETIME
SET @CutOffDate = '2014-09-01 00:00:00.000'
[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
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
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)?
- Paul L.
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.
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.
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)
g.id as [group],
c.name as [access_level],
technology_purse as [Purse1],
tuition_purse as [Purse2],
user_purse as [Purse3],
r.name as [Rate],
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
Steven - While I mostly understand the SQL code that you've written, I don't know how to execute it.
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.
Steven English wrote:
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!
1 of 1 people found this helpful
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.
Will this work with Pharos 9.x? I see it references Pharos 8.x.
It works on 9.0 R2 without any issues. I am not aware of any database changes between 8.x and 9.0 R2 that would be a cause for suspicion that it would not work.
Edit: I tested it on 9.0 R2 prior to posting the response above.
1 of 1 people found this helpful
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.