Hi Ben,

Try looking under Admin Console->Audit Log->User Events. The folder-level auditing doesn't track logins/logouts, but it's available from the root container ("/") to Site Admins.

Thanks,
Josh

that's it, but can i write a query against that? we'd want to join this to core.groups and core.membership.

in case there isnt, I just figured out that I can point labkey to it's own pg tables as an external schema, which lets us directly query against the underlying postgres tables. this could be kinda useful whenever labkey stores information we need, but doesnt expose it...

querying the time of last study reload would be another example of storing the information, but not exposing it.

i may have spoken too soon...our server has two labkey schemas on it and it's only letting me point to the other one. it would be quite useful if that were not the case.

Can you explain the problem further? Not sure what it means to have two labkey schemas... do you mean two labkey databases?

If you're experiencing namespace collisions with external schemas (e.g., schemas with the same name in different databases) know that this has been fixed in 10.2.

careless word choice. on our production server we have two separate postgres databases, one called 'labkey' and one called 'labkey2'. not entirely sure why the first exists - probably an artifact from when tom setup or upgraded at some point, but it does hold the normal labkey schemas and tables. the database used by our site is 'labkey2'. when i try to define an external schema, I have the option of selecting 'labkey' as a data source (but not labkey2). I will double check this, but it appeared that when I selected 'labkey' i was viewing the non-used labkey database, instead of the labkey2 db which our production server uses. i forgot that we had two databases on our production server when i wrote my earlier post.

my interpretation was that labkey 10.1 allows us to point to any postgres database besides the one that's actively used by that labkey install. is that interpretation wrong?

Actually, you can define external schemas on any PostgreSQL (or SQL Server or SAS) data source you've defined in your labkey.xml file, including the core labkey data source, though for security and data integrity reasons we don't generally recommend exposing schemas in the core labkey data source as external schemas.

The data source drop-down shows a simplified version of the data source name as defined in labkey.xml, NOT the database name. So, in your case, I suspect your labkey.xml file defines a "labkeyDataSource" pointed at database "labkey2". This data source will appear as "labkey" in the drop-down list. If you haven't defined a data source for your "labkey" database then it won't appear in the list.

you are correct about labkey.xml, but I think there's a second thing happening here:

I defined two different external schemas in labkey. they both use the 'labkey' data source (which looks like it is the postgres db labkey2), then I pointed it to the 'Audit' schema and gave it the name 'audit'. I then did exactly the same thing, but named it 'audit_db'. links below:

https://xnight.primate.wisc.edu:8443/labkey/query/WNPRC/EHR/executeQuery.view?schemaName=audit&query.queryName=auditlog

https://xnight.primate.wisc.edu:8443/labkey/query/WNPRC/EHR/executeQuery.view?schemaName=audit_db&query.queryName=auditlog

these return a different number of rows. the one called 'audit' returns a couple thousand, while the one called 'audit_db' returns 5 million. does the name 'audit' collide with something in labkey? does the 'audit' db somehow get labkey's default filter-by-container behavior? at least as it is exposed in the schema browser, the audit table provided by labkey is labeled 'auditLog'.

by giving the external schema a different name, it appears to work properly now. i understand the rationale behind avoiding exposing the underlying postgres data, but since this should be read-only I dont see a whole lot of harm here.

If I'm understanding correctly, "audit" is a built-in labkey schema and "audit_db" is the same underlying database schema defined as an external schema. These will behave differently. The built-in schemas know how all the tables relate to each other... in particular, they enforce folder security through joins and foreign keys. External schemas are more primitive: if a table has a "container" column, it's filtered to the current container; if not, the entire table is visible -- they aren't able to trace foreign keys to locate the parent table that has the container column. This is what you're seeing and one reason we generally recommend not exposing the internal schemas.

i get that the underlying postgres table is not the same as what is exposed in labkey.

at least as it appears in the schema browser, the build-in table exposed by labkey is called auditlog, not audit. this would suggest that creating an external schema called 'audit' would not collide, but this does not seem to be true.

in the above post, I defined two external schemas, both of which pointed to the same underlying postgres table. for some reason, by giving one of them the name 'audit' it seems to inherit the behavior of the build-in auditlog table. I think this means that it probably should not be possible to define an external schema called 'audit'.