Hi all,
Is there a way I could get access to special tables in dhis2? I’m trying to create sql view with the statement “SELECT username,lastlogin FROM users order by lastlogin;” and when I try to save the sql view I’m getting the message “Not allowance to query in the special tables!” We want to monitor by name who frequently logs-in the system.
Gomez.
I believe those table are blocked to prevent non-system administrators from viewing user data.
If you have Pgadmin access or have installed PHPpgadmin on the server, you can run the queries there. SQL views in DHIS-2 are also problematic especially if you refer to any of the resource tables because the analytics and data mart processes try to drop them when running and then they fail.
We’ve created a function that creates a number of ‘materialized views’ that our users want to export for Excel pivot table analyses. This can be scheduled to run daily or we can run it on demand. If it is something you want to view regularly you might go that route. For example most people using pivot tables want to view data with all levels of the hierarchy in a single row (province, district, sector, facilityname, dataelementname, startdate, periodtype, categoryoptioncombo, value). Jason Pickering, a frequent contributor to this list is the master of materialized views and helped us develop ours.
Unfortunately there does not appear to be a way currently to expose those materialized views through the api. You can now create iReports with a JDBC datasource (i.e. not based on a DHIS-2 pivot table), so that might be a way to view the data from within DHIS-2.
Good luck!
From: Dhis2-users [mailto:dhis2-users-bounces+rwilson=msh.org@lists.launchpad.net] On Behalf Of Gomez Phiri
Sent: Friday, September 06, 2013 11:03 AM
To: dhis2-users@lists.launchpad.net; dhis2-devs@lists.launchpad.net
Subject: [Dhis2-users] SQL View Permissions
Hi all,
Is there a way I could get access to special tables in dhis2? I’m trying to create sql view with the statement “SELECT username,lastlogin FROM users order by lastlogin;” and when I try to save the sql view I’m getting the message “Not allowance to query in the special tables!” We want to monitor by name who frequently logs-in the system.
Gomez.
Hi Wilson,
I have pgadmin access and I can run all the queries from there, I just wanted to create the view for some non technical users who cannot query the database for user activity directly through pgadmin.
Thanks
Gomez.
I am not sure the restrictions on the user tables make much sense really. I think we should consider removing them,as they are easily circumvented.
Several approaches are possible as Randy outlines. Just to emphasize…
Encapsulation of the view in a function . This helps to circumvent the issues Randy outlines with direct views on the database. This could be coupled to a cron job to materialize the result set to a CSV file which could be linked to a resource. A function would allow you to create an SQL view as DHIS would not know what is going on inside the function.
Using a JDBC source and feeding this into a report.
(2) is probably the simplest approach.
This will only give you the last login however. If you want to knows who logs on frequently however,you would probably need to perform a more detailed analysis of the logs.
Regards,
Jason
–Sent from my mobile
On Sep 6, 2013 7:10 PM, “Gomez Phiri” gomezphiri@hotmail.com wrote:
Hi Wilson,
I have pgadmin access and I can run all the queries from there, I just wanted to create the view for some non technical users who cannot query the database for user activity directly through pgadmin.
Thanks
Gomez.
From: rwilson@msh.org
To: gomezphiri@hotmail.com; dhis2-users@lists.launchpad.net; dhis2-devs@lists.launchpad.netSubject: RE: [Dhis2-users] SQL View Permissions
Date: Fri, 6 Sep 2013 10:28:10 +0000I believe those table are blocked to prevent non-system administrators from viewing user data.
If you have Pgadmin access or have installed PHPpgadmin on the server, you can run the queries there. SQL views in DHIS-2 are also problematic especially if you refer to any of the resource tables because the analytics and data mart processes try to drop them when running and then they fail.
We’ve created a function that creates a number of ‘materialized views’ that our users want to export for Excel pivot table analyses. This can be scheduled to run daily or we can run it on demand. If it is something you want to view regularly you might go that route. For example most people using pivot tables want to view data with all levels of the hierarchy in a single row (province, district, sector, facilityname, dataelementname, startdate, periodtype, categoryoptioncombo, value). Jason Pickering, a frequent contributor to this list is the master of materialized views and helped us develop ours.
Unfortunately there does not appear to be a way currently to expose those materialized views through the api. You can now create iReports with a JDBC datasource (i.e. not based on a DHIS-2 pivot table), so that might be a way to view the data from within DHIS-2.
Good luck!
From: Dhis2-users [mailto:dhis2-users-bounces+rwilson=msh.org@lists.launchpad.net] On Behalf Of Gomez Phiri
Sent: Friday, September 06, 2013 11:03 AM
To: dhis2-users@lists.launchpad.net; dhis2-devs@lists.launchpad.net
Subject: [Dhis2-users] SQL View Permissions
Hi all,
Is there a way I could get access to special tables in dhis2? I’m trying to create sql view with the statement “SELECT username,lastlogin FROM users order by lastlogin;” and when I try to save the sql view I’m getting the message “Not allowance to query in the special tables!” We want to monitor by name who frequently logs-in the system.
Gomez.
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
Thanks,
This helps a lot. Now I know where to start from 