So that we can ensure we are GDPR compliant, we will need to do a regular tidy up of our users in our database, for example to be able to disable all the users when a project comes to an end. But we are struggling to find a way of easily viewing who is a member of a usergroup in a form that is easy to download.
Can we run a SQL query / view to see all the usernames and/or surnames of every member of each usergroup (recognising that some people are members of more than one group)? We are on v2.30.
Without being any DHIS2 expert, Iād say you want to look at users, userrole and userrolemembers tables. These should give you relationship between users and their roles. Then you have an extra table userinfo with data about the user (surname, firstname et al.) but, without a deeper look, I couldnāt find the actual link with users tableā¦
A quick query I ran in my local (and test) machine, with a 2.31 version (I assume user related tables are similar in your 2.30):
SELECT ur.name AS group_name, u.username FROM users u, userrole ur, userrolemembers urm WHERE urm.userid=u.userid AND urm.userroleid=ur.userroleid ORDER BY ur.name ; -- could be sorted by u.username instead
displays a list of roles and their usernames (not surnames), repeating the role as many times as users belong to it. This would be a first and quick approach. Iām sure query could be fancier and tuned to include relevant userinfo data and Iām also curious to see how userinfo is connected to users table, so hopefully someone else will come up with a better solution.
Thanks so much for your quick feedback Pau! Just having the list of usernames against the user groups would be a good start- i donāt think surnames are needed.
Iāve tried running this as you suggest and I just get an error message saying ācould not execute SQLā.
So sadly back to the drawing board- anyone else have any ideas?
hmm, try removing the comment from the query (anything after the -- ), and also the quotes (``) from the markdown (it seems you carry them when copying quoted text). Something like:
SELECT ur.name AS group_name, u.username FROM users u, userrole
ur, userrolemembers urm WHERE urm.userid=u.userid AND
urm.userroleid=ur.userroleid ORDER BY ur.name ;
Iām using sierra-leoneās demo database, and looking at the one for 2.30, it seems tables are thereā¦
Iām assuming youāre using an standard psql client (console, pgadmin3 or similar), right?
You have two main challenges with regard to keeping you user-list āup-to-dateā:
Firstly, several of the user-related tables are protected in the sense that you cannot access them via SQL View. In order to bypass that, you will have to add one or more functions in the back-end that you can call from e.g. SQL View. That way, you can easily pull out lists of user groups with their members, disabled users, users and their last logins, users with specific roles (e.g. superusers), etc. OR you access what you need directly from the back end (probably more secure since it require admin access to the back end)
Secondly, and this can be a bit of a nightmare in terms of removing no-longer-relevant users: The āuserā parts of the DHIS2 data model is a ramshackle, birdās nest type collection of objects, with an incoherent set of tools operating on them in various versions. So you will not only regularly find you are blocked from deleting specific users, you might also find that users are partially deleted (like I have had a number of cases where a user record is deleted from the primary āusersā table but not from the secondary āuserinfoā table) which cause further problems down the line. When features are added - like the storing of historical passwords - you might also find that such foreign-constraint tables are not included in the business logic used to delete usersā¦
It is also important to understand that the data model is designed to block the removal of users that are linked to actual DATA. That is how it should be - if you want to know which tables contain a foreign-key-constrained āuseridā or ālastupdatedbyā column, then run the following script (directly in the db or view SQL View):
SELECT table_name, column_name from information_schema.columns
WHERE column_name IN (āuseridā, ālastupdatedbyā)
;
You can see how many tables that do have any of the two standard āuserā columns that relate to the users or userinfo tables (NOTE that the list is NOT an exhaustive list of foreign keys - getting that is more complicated - google e.g. ālist-all-foreign-keys-postgresqlā for details and script examples). So while one problem is that new tables have been added without necessary adding those tables to the ādelete userā process - meaning any entries in such tables will block the deletion of a user - another problem is that whatever core developer work on maintaining the ādelete userā process might not have the same understanding of which tables SHOULD form a block as e.g. practical users.
Notwithstanding the need to block the deletion of users linked to relevant data, most experienced users will also argue that it should be possible to get rid of such users if they clearly are of no interest whatsoever OR (very typical!!) there are duplicate users. But to clean out such utterly irrelevant historical or duplicate users, you basically need to āmergeā or āshiftā their data - either to the correct username (for duplicated usernames) or maybe to a pool user called āOld_Usersā or something. In DHIS1.4, this was easily done with a tool that listed all users and enabled any userās data to be āmergedā with another and then deleted, but doing the same in DHIS2 is not easy.
So to cut a long story short, the only simple way to deal with outdated users in DHIS2 is to DISABLE them. That functionality works well, and such users are locked out. From a data audit perspective it might also be preferable, since the users are still in the system and data auditor can see what they were up to while active.
Thanks @Calle_Hedberg and @pauv for all your help and advice on this. After a bit of experimentation, one of our developers pointed us towards a solution. We simply add the following onto the URL of our site and then imported the resulting xml into Excel:
From this we can easily see which users we might need to disable when a programme comes to an end, and see which users are the members of multiple user groups (and so should just be removed from the user group rather than disabled).
This seems to work for us- obviously we would still have to disable each user individually, but this gives us a much quicker way to see the information we need.
Great that you have found a solution that works for you. And disabling users are straightforward, itās removing outdated users that can be a real missionā¦
@hernandezmachava could be missing something, and if so apologies. For me, I can use your link, view the info, and press Ctrl+S or right click and save and the xml will be saved to your computer. You can use this to import the information into excel. Is that not your experience?
I was able to save the file by pressing Ctrl+S but I can not see attributes. Here are the steps I have taken:
Open the Excel file
Click the Data tab
In the āGet & Transformā data group, clicked on the āGet Dataā option
Go to the āFrom fileā option
Click on āFrom XMLā
In the input data dialog box that opens up, I located the XML file that I want to import and selected it
Clicked Import. the XML file is imported into power query and Navigator dialog box is opened, But see the data that is displayed in the preview window. . There are no display name and username as shown in the XML. it only shows user groups
saving your link, and then importing in Excel by using āDeveloperā and āImportā, selecting the file and importing should get you what you are looking for.
Everything is working well now. Using developer and import in excel file, I was able to convert the xml file and view the three columns shown in your post.
