User activity statistics from ELN (SQL)

Q:

We need certain statistics pulled from the E-Notebook system regarding users still adding/editing experiments in E-Notebook.
Who made changes in certain dates?
Is there any possibility to check/track/export report who is logged into E-Notebook from now on until certain date?

A:

Please try following scripts:

select p.primary_key user_key, p.username owner,
c.section_set_key, c.name collection_name,  max(v.action_date) last_modified_date

from eln_collections c,

ena_versions v,

--env_collection_modified_dates md,

eln_people p

where v.collection_key=c.section_set_key

and p.primary_key=c.owner_key

--and p.username='EN1200'

group by p.primary_key, p.username, c.section_set_key, c.name

having max(v.action_date) between
to_date('01/01/2020','MM/DD/YYYY') and to_date('09/01/2021','MM/DD/YYYY')

order by  p.primary_key , p.username ,
c.section_set_key, c.name

-------------------------------------------------------------------------------------------------------------------- 

 

select * from

(select owner_key, p.username,max(start_time)
last_login_date

from eln_sessions s,

eln_people p

where p.primary_key=s.owner_key

group by owner_key, p.username

) a

where last_login_date between
to_date('01/01/2010','MM/DD/YYYY') and to_date('09/01/2021','MM/DD/YYYY');