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... 1) 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. 2) 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" <[email protected]> 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: [email protected] > To: [email protected]; [email protected]; > [email protected] > Subject: RE: [Dhis2-users] SQL View Permissions > Date: Fri, 6 Sep 2013 10:28:10 +0000 > > 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= > [email protected]] *On Behalf Of *Gomez Phiri > *Sent:* Friday, September 06, 2013 11:03 AM > *To:* [email protected]; [email protected] > *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 : [email protected] > Unsubscribe : https://launchpad.net/~dhis2-devs > More help : https://help.launchpad.net/ListHelp > >
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
