Overview

Tracking EMERSE usage can be important for a variety of reasons. Here we provide several SQL queries that can be run to provide some usage stats about the system. They can be changed to suit local needs.

SQL queries

Total number of users in the system

select count(*) from emerse.PARTY party
inner join emerse.person person on party.ID=person.ID
inner join EMERSE.LOGIN_ACCOUNT la on la.ID=person.LOGIN_ID

Total number of users who logged in within the past year

select count(*) from emerse.PARTY party
inner join emerse.person person on party.ID=person.ID
inner join EMERSE.LOGIN_ACCOUNT la on la.ID=person.LOGIN_ID
inner join (select login_id from emerse.user_session where user_session.start_time>sysdate-365 group by login_id) sessions
on sessions.login_id=la.id

Username and User IDs of those who logged in within the last year

select name, user_id, last_login from emerse.PARTY party
inner join emerse.person person on party.ID=person.ID
inner join EMERSE.LOGIN_ACCOUNT la on la.ID=person.LOGIN_ID
inner join (select login_id from emerse.user_session where user_session.start_time>sysdate-365 group by login_id) sessions
on sessions.login_id=la.id

Number of new users added each month

select  count(*) ,to_char(la.creation_Date, 'yyyy/mm') from emerse.PARTY party
inner join emerse.person person on party.ID=person.ID
inner join EMERSE.LOGIN_ACCOUNT la on la.ID=person.LOGIN_ID
group by to_char(la.creation_Date, 'yyyy/mm')

Average and median number of logins per day (non-distinct users)

select avg(dailycount), median(dailycount) from (
select count(*) dailycount,to_char(start_time, 'mm-dd-yyyy') from emerse.USER_SESSION sess
inner join emerse.login_account la on sess.LOGIN_ID=la.ID
inner join EMERSE.PERSON pers on pers.LOGIN_Id=la.id
where user_id not in ('emerse','hanauer','rituk', 'i2b2-monitor','jlaw', 'datadirect')
and to_char(start_time, 'D') not in (1,7)
and start_time>sysdate-365
group by to_char(start_time, 'mm-dd-yyyy')
)
the where user_id not in clause is in the query to exclude reporting on individuals who support EMERSE so that it does not influence the numbers. This would have to be locally modified.

Average and median number of logins per day (distinct users)

select avg(distinctusercount), median(distinctusercount) from (
select count(*) as distinctusercount,day from(
select la.id, count(*) as dailycount, to_char(start_time, 'mm-dd-yyyy') as day from emerse.USER_SESSION sess
inner join emerse.login_account la on sess.LOGIN_ID=la.ID
inner join EMERSE.PERSON pers on pers.LOGIN_Id=la.id
where user_id not in ('emerse','hanauer','rituk', 'i2b2-monitor','jlaw',  'datadirect')
and to_char(start_time, 'D') not in (1,7)
and start_time>sysdate-365
group by to_char(start_time, 'mm-dd-yyyy'),la.id
)group by day
)
the where user_id not in clause is in the query to exclude reporting on individuals who support EMERSE so that it does not influence the numbers. This would have to be locally modified.

All Attestation info for the last 7 days

select * from emerse.SESSION_ATTESTATION att
inner join emerse.user_session sess on att.USER_SESSION_ID=sess.id
inner join EMERSE.LOGIN_ACCOUNT login on sess.LOGIN_ID=login.id
left outer join EMERSE.RESEARCH_STUDY_ATTESTATION resatt on att.ID=resatt.SESSION_ATTESTATION_ID
left outer join emerse.RESEARCH_STUDY study on resatt.RESEARCH_STUDY_ID=study.ID
left outer join emerse.ATTESTATION_OTHER othatt on att.ID=othatt.SESSION_ATTESTATION_ID
left outer join emerse.other_attestation_reason attreason on othatt.OTHER_ATTEST_REASON_KEY=attreason.USER_KEY
where sess.START_TIME>sysdate-7

List of users and associated IRB numbers for research use within the past year

select type,  user_id, external_id from emerse.SESSION_ATTESTATION att
inner join emerse.user_session sess on att.USER_SESSION_ID=sess.id
inner join EMERSE.LOGIN_ACCOUNT login on sess.LOGIN_ID=login.id
left outer join EMERSE.RESEARCH_STUDY_ATTESTATION resatt on att.ID=resatt.SESSION_ATTESTATION_ID
left outer join emerse.RESEARCH_STUDY study on resatt.RESEARCH_STUDY_ID=study.ID
left outer join emerse.ATTESTATION_OTHER othatt on att.ID=othatt.SESSION_ATTESTATION_ID
left outer join emerse.other_attestation_reason attreason on othatt.OTHER_ATTEST_REASON_KEY=attreason.USER_KEY
where sess.START_TIME>sysdate-365
AND type = 'RSA'

Unique list of studies, by IRB number and principal investigator (PI)

select DISTINCT EXTERNAL_ID, PRINCIPAL_INVESTIGATOR_NAME from emerse.SESSION_ATTESTATION att
inner join emerse.user_session sess on att.USER_SESSION_ID=sess.id
inner join EMERSE.LOGIN_ACCOUNT login on sess.LOGIN_ID=login.id
left outer join EMERSE.RESEARCH_STUDY_ATTESTATION resatt on att.ID=resatt.SESSION_ATTESTATION_ID
left outer join emerse.RESEARCH_STUDY study on resatt.RESEARCH_STUDY_ID=study.ID
left outer join emerse.ATTESTATION_OTHER othatt on att.ID=othatt.SESSION_ATTESTATION_ID
left outer join emerse.other_attestation_reason attreason on othatt.OTHER_ATTEST_REASON_KEY=attreason.USER_KEY
where
EXTERNAL_ID IS NOT NULL

All search terms entered within the past 7 days

select distinct string from emerse.search_term_history  hist
inner join emerse.revision_list revlist on hist.revision=revlist.revision
where revlist.revision_timestamp>sysdate-7
and string is not null group by string

Count of all active (not deleted) Search Term Bundles, including associated metadata such as term counts

select emerse.bundle.*, (select count(*) from EMERSE.SEARCH_TERM where bundle_id=bundle.id) from emerse.bundle where ADHOC_FLAG=0 and deleted_flag=0
and emerse.bundle.OWNER_PARTY_ID not in
(select id from emerse.party where party.NAME like 'emerse')
and emerse.bundle.term_count>0

Details about a specific user and studies they have been working on within EMERSE

select * from emerse.RESEARCH_STUDY_MEMBER
where user_id='username'
Need to replace username with an actual username in the system.