EMERSE Reporting and Auditing Guide

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.

These queries were all written for the Oracle database. If you are using a different database it is likely that these queries will need to be modified.

General SQL queries

Here is a base query that goes through all the tables for who logged in when, for what reason (the attestation) and what searches they did, what patients they viewed, and what documents they viewed.

SELECT la.USER_ID,                           -- user
       sess.START_TIME,                      -- logged in at this time
       sa.*, ca.*, cr.*, fta.*, rsa.*, rs.*, -- attested for this reason
       s.SEARCH,                             -- made this search
       s.SEARCH_TIME,                        -- at this time
       p.EXTERNAL_ID,                        -- viewing this patient MRN, or
       sas.DOCUMENT_ID                       -- viewing this document
  FROM USER_SESSION sess
  JOIN LOGIN_ACCOUNT la
    ON la.ID = sess.LOGIN_ID
  JOIN SESSION_ATTESTATION sa
    ON sa.USER_SESSION_ID = sess.ID
  LEFT
  JOIN COMMON_ATTESTATION ca
    ON sa.TYPE = 'COM'
   AND ca.SESSION_ATTESTATION_ID = sa.ID
  LEFT
  JOIN COMMON_REASON cr
    ON cr.ID = ca.COMMON_REASON_ID
  LEFT
  JOIN FREE_TEXT_ATTESTATION fta
    ON sa.TYPE = 'FREE'
   AND fta.SESSION_ATTESTATION_ID = sa.ID
  LEFT
  JOIN RESEARCH_STUDY_ATTESTATION rsa
    ON sa.TYPE = 'RSA'
   AND rsa.SESSION_ATTESTATION_ID = sa.id
  LEFT
  JOIN RESEARCH_STUDY rs
    ON rs.ID = rsa.RESEARCH_STUDY_ID
  JOIN SEARCH_AUDIT s
    ON sess.ID = s.SESSION_ID
  LEFT
  JOIN SEARCH_AUDIT_SPECIFIC sas
    ON s.ID = sas.SEARCH_AUDIT_ID
  LEFT
  JOIN PATIENT p
    ON p.ID = sas.PATIENT_ID
;

This should contain in one way or another all the information you might want. We start at the user session table, which gets a row each time someone logs in. We jump over to the login account to get their user name, then get their session attestation information. This is split over five tables. SESSION_ATTESTATION describes what type of attestation they did: either it has type "RSA", meaning a research study was picked, in which case, the RESEARCH_STUDY_ATTESTATION table and RESEARCH_TABLE have matching rows in the query, which describe which research study was chosen; or it has type FREE, meaning the user typed in a reason for using EMERSE themself, in which case the COMMON_ATTESTATION table has a matching row; or finally it has type COM, meaning the user chose a common use-case described in COMMON_REASON, in which case a matching row is present in COMMON_ATTESTATION.

Finally, if the user made a search in that session, there will be a row in the SEARCH_AUDIT table. The search is described in a JSON object, which should be relatively readable by a human, but if you have questions let us know. Since there’s both terms and filters in a search, it’s a bit complex to record the search, which is why we used JSON there. It may change in the future since it’s a rather large format.

The type of search they did is described in the RESULT_TYPE column. It is an enumeration, described here:

Table 1. `SEARCH_AUDIT.RESULT_TYPE` Enumeration Explanation
`RESULT_TYPE`	Search Action Done
0	A specific patient or document viewed; described in the `SEARCH_AUDIT_SPECIFIC` table
1	Ran an all-patient search, and got a count back.
2	Viewed the all-patient demographics page for this search.
3	Viewed the all-patient trends graph for this search.

If in connection to a search, they viewed a specific patient’s metadata or document, a row will appear in the SEARCH_AUDIT_SPECIFIC table with the patient id viewed or the patient id and document id viewed. All-patient search does not count as viewing any patient at all. Each row of the overview page counts as viewing that patient, but no specific document, and viewing a document of the patient of course counts as viewing that document.

Specifically what information they saw related to the patient or document is described in the RESULT_TYPE column, which is an enumeration, described here:

Table 2. `SEARCH_AUDIT_SPECIFIC.RESULT_TYPE` Enumeration Explanation
`RESULT_TYPE`	Data Viewed
0	Viewed a Snippet of this document
1	Viewed an overview table cell for this patient in numeric mode. (Count of matching documents over total count.)
2	Viewed an overview table cell for this patient in mosaic mode. (Shows the color swatches that indicate the sets of terms that match.)
3	Viewed the complete text of the indicated document (identified by the value of the Solr field that the `DOC_FIELD_EMR_INTENT` `RPT_ID` is mapped to.

All these joins (especially the joins to the audit-specific table) will make for a huge number of rows in this query, so depending on what you want to see, you should cut out any joins you aren’t interested in. For instance, if you’re just interested in logins, stop at the LOGIN_ACCOUNT table. If you’re just interested in classifying logs by attestation, stop after the attestation tables. By adding a GROUP BY clause and COUNT(1) to the select, you can get the database to give you the data for a histogram or other graphs you may want, depending on what you group by.

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 la.USER_ID,                           -- user
       sess.START_TIME,                      -- logged in at this time
       sa.*, ca.*, cr.*, fta.*, rsa.*, rs.* -- attested for this reason
  FROM USER_SESSION sess
  JOIN LOGIN_ACCOUNT la
    ON la.ID = sess.LOGIN_ID
  JOIN SESSION_ATTESTATION sa
    ON sa.USER_SESSION_ID = sess.ID
  LEFT
  JOIN COMMON_ATTESTATION ca
    ON sa.TYPE = 'COM'
   AND ca.SESSION_ATTESTATION_ID = sa.ID
  LEFT
  JOIN COMMON_REASON cr
    ON cr.ID = ca.COMMON_REASON_ID
  LEFT
  JOIN FREE_TEXT_ATTESTATION fta
    ON sa.TYPE = 'FREE'
   AND fta.SESSION_ATTESTATION_ID = sa.ID
  LEFT
  JOIN RESEARCH_STUDY_ATTESTATION rsa
    ON sa.TYPE = 'RSA'
   AND rsa.SESSION_ATTESTATION_ID = sa.id
  LEFT
  JOIN RESEARCH_STUDY rs
    ON rs.ID = rsa.RESEARCH_STUDY_ID
 WHERE sess.START_TIME > sysdate-7

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

SELECT distinct
       la.USER_ID,       -- User ID
       rs.EXTERNAL_ID    -- IRB Number
  FROM USER_SESSION sess
  JOIN LOGIN_ACCOUNT la
    ON la.ID = sess.LOGIN_ID
  JOIN SESSION_ATTESTATION sa
    ON sa.USER_SESSION_ID = sess.ID
  JOIN RESEARCH_STUDY_ATTESTATION rsa
    ON rsa.SESSION_ATTESTATION_ID = sa.id
  JOIN RESEARCH_STUDY rs
    ON rs.ID = rsa.RESEARCH_STUDY_ID
 WHERE sess.START_TIME > sysdate - 365
   AND sa.TYPE = 'RSA'
 ORDER BY rs.EXTERNAL_ID, la.USER_ID

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'

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

All Patient Lists that a User can Access

SELECT pl."ID"                                AS "LIST_ID",
       pl."NAME"                              AS "LIST_NAME",
       o."ID"                                 AS "OWNER_PERSON_ID",
       o."FIRST_NAME" || ' ' || o."LAST_NAME" AS "OWNER"
  FROM emerse."PATIENT_LIST"  pl
       JOIN emerse."PERSON"   o ON o."ID" = pl."OWNER_PARTY_ID"
     , emerse."LOGIN_ACCOUNT" la
       JOIN emerse."PERSON"   p ON p."LOGIN_ID" = la."ID"
 WHERE (pl."OWNER_PARTY_ID" = p."ID" OR EXISTS(SELECT 1
                                                 FROM emerse."PT_LIST_PROXY_USERS" pp
                                                WHERE pp."PROXIED_PARTY_ID" = p."ID"
                                                  AND pp."PATIENT_LIST_ID" = pl."ID"))
   AND la."USER_ID" = 'emerse'
;