Overview

This guide covers issue related to tracking EMERSE usage. 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.

General 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

User-Specfic SQL queries

The following queries may be useful for compliance/auditing purposes for individuals.

For the following examples, it is necessary to replace username with an actual username in the system.

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

select * from emerse.RESEARCH_STUDY_MEMBER
where user_id='username'

General login information about a specific user

select la.user_id, start_time, end_time, DECODE(ext_app_code, NULL, 'EMERSE', ext_app_code) as accessed_from from emerse.user_session sess
inner join emerse.login_account la on sess.LOGIN_ID=la.id
where sess.login_id=(select id from emerse.login_account where user_id = 'username')
order by sess.start_time

Searches done by a user from an All Patient Search, Overview Results, and Document Summary

Not all parameters are available for each search type. For example:

  • Result count only applies to All Patient search

  • ptlistsizeatsearch only applies to Overview search type

  • MRN only applies to Document Summaries

with sessions as(
select id from emerse.user_session sess where sess.login_id=(select id from emerse.login_account where user_id='username')
)
select coalesce (decode(heatmapsearch.id, null, null, 'overview search'),
                 decode(allptsearch.id, null, null,'all pt search'),
                 decode(dss.id, null, null, 'doc summary')) as search_type,
                usr_srch.id as search_id, usr_srch.search_date, usr_srch.session_id,
                date_range_start filtered_start_date, date_range_end filtered_end_date,
                search_terms_string, search_query,
                heatmapsearch.patient_list_id, heatmapsearch.bundle_id,
                result_count as allpatientsearch_result_count,
                heatmapsearch.list_size_at_search ptlistsizeatsearch,
                patient.external_id as MRN
from emerse.user_search usr_srch
inner join emerse.sessions on usr_srch.session_id=sessions.id
left outer join emerse.heatmap_search heatmapsearch on usr_srch.id=heatmapsearch.id
left outer join emerse.all_patient_search allptsearch on usr_srch.id=allptsearch.id
left outer join emerse.doc_summary_search dss on usr_srch.id=dss.id
left outer join emerse.patient on dss.patient_id=patient.id
order by search_date

Search results from the Overview page, for a Specific User

Pagination within the EMERSE application for the Overview page may make num rows less than patients on a patient list from the prior query ("Searches done by a user from an All Patient Search, Overview Results, and Document Summary"). For example: Patient count on the Overview/heatmap is 500 per page. The ptlistsizeatsearch from the prior report is 520 (there were 520 patients in the list overall). If the user does not go to the next page, the number of the patients from the search on this report is 500,since the query only reports on the patients that the user actually had viewed at the level of the Overview.

with sessions as(
select id from emerse.user_session sess where sess.login_id=(select id from emerse.login_account where user_id='username')
)
select usr_srch.id as search_id, patient.external_id as MRN, patient.first_name, patient.last_name,
       usr_srch.search_date, usr_srch.session_id,
       date_range_start filtered_start_date, date_range_end filtered_end_date,
       search_terms_string, search_query,
       heatmapsearch.patient_list_id, heatmapsearch.bundle_id
from emerse.user_search usr_srch
inner join emerse.sessions on usr_srch.session_id=sessions.id
inner join emerse.heatmap_search heatmapsearch on usr_srch.id=heatmapsearch.id
inner join emerse.heatmap_search_items items on heatmapsearch.id=items.search_id and items.event='SPLT'
inner join emerse.patient on items.patient_id=patient.id
order by search_date

Specific Documents that were Viewed by an Individual User

with sessions as(
select id from emerse.user_session sess where sess.login_id=(select id from emerse.login_account where user_id ='username')
)
select dv.view_datetime, dv.doc_type_code as doc_source, dv.document_pk as doc_source_id,
       pt.external_id as MRN, pt.first_name, pt.last_name
from emerse.document_view dv
inner join emerse.sessions on dv.USER_SESSION_ID=sessions.id
inner join emerse.patient pt on dv.patient_id=pt.id
order by dv.view_datetime

Patients Lists Shared with a Specific User

with party_id as(
select pty.id from emerse.party pty
inner join emerse.person psn on psn.id=pty.id
inner join emerse.login_account la on la.id=psn.login_id
where la.user_id='username'
)
select plist.id patient_list_id, plist.description patient_list_name,
       plist.owner_party_id owner_id, party_owner.name owner_name,
       pt.external_id MRN, pt.first_name, pt.last_name
from emerse.pt_list_proxy_users proxy
inner join emerse.party_id pty on pty.id=proxy.proxied_party_id
inner join emerse.patient_list plist on plist.id = proxy.patient_list_id
left join  emerse.patient_list_entry pe ON pe.patient_list_id = plist.id
inner join  emerse.party party_owner ON party_owner.id = plist.owner_party_id
inner join emerse.patient pt ON pt.id = pe.patient_id
order by plist.id, pt.external_id