How do I run a SQL view to show all the users in each user group?

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.

Thanks
Anna

4 Likes

hi Anna,

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.

best,

pau.

1 Like

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?

Anna

1 Like

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?

I hope it works this time…

best,

pau.

1 Like

@adownie

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.

Regards
Calle

4 Likes

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:

/api/userGroups?&fields=name,users%5BdisplayName,userCredentials%5Busername%5D%5D&paging=false

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.

Anna

2 Likes

@adownie

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…

Regards
Calle

1 Like

Thanks for this; your API URL was exactly what i was looking for!

Hi @adowniem,

Using your API, how can I download file data in csv format as it is in xml format.

Thanks

@hernandezmachava you should be able save this as xml and import it into excel Import XML data - Excel and from there save in csv if you so choose

Hi @Matthew_Boddie,

Using https://play.dhis2.org/2.34.4/api/userGroups?&fields=name,users[displayName,userCredentials[username]]&paging=false, I have the following xml file

But I am unable to download this xml file in dhis 2 so that I can import it into excel.

Thanks

@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?

Hi @Matthew_Boddie,

I was able to save the file by pressing Ctrl+S but I can not see attributes. Here are the steps I have taken:

  1. Open the Excel file
  2. Click the Data tab
  3. In the ‘Get & Transform’ data group, clicked on the ‘Get Data’ option
  4. Go to the ‘From file’ option
  5. Click on ‘From XML’
  6. In the input data dialog box that opens up, I located the XML file that I want to import and selected it
  7. 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

    .
    8, clicking on load or transform data does not alter anything. I am unable to extract usernames

Thanks

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.


The link I provided previously should help with any troubleshooting with importing through the developer tab Import XML data - Excel

1 Like

Hi @Matthew_Boddie,

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.

Thank you very very much for your help.

1 Like

@hernandezmachava fantastic! Glad to hear that.