Notifications
Clear all
Topic starter
Hello,
I am interested in a report that shows how long a user is logged into the EMR. We are a residency program and need to get a sense of "extra" time the residents spend outside of the hours scheduled. We are interested in looking at usage for a period of time. I am sure this is well beyond my skill set!
Does anyone have one? Are you willing to share? We are on CPS12.
Thank you!
dh
Family Medicine Residency of Idaho
Posted : August 4, 2014 10:46 am
This should be a good approximation 
. Change the date filter just a few days at a time as the query will get expensive if you're looking for a big date range. There are a lot of audit events and it joins on a column that is not in an index. Might be a good idea to run it after hours.
--This script shows the amount of time spent in the EMR per user per day
--These results are imperfect as they ignore sessions without an audited logout event.
SELECT LOGINNAME
,TheDate
,max(LogOutTime) AS FinalLogoutTime
,sum(MinutesLoggedIn) AS TotalMinutesThisDay
,CAST(sum(MinutesLoggedIn) AS float)/60 AS TotalHoursThisDay
FROM (
SELECT
p.LOGINNAME
,CONVERT(DATE,eIn.EVENT_TIMESTAMP) AS TheDate
,eIn.EVENT_TIMESTAMP AS LogInTime
,CASE WHEN O.EVENT_TIMESTAMP < eInNext.EVENT_TIMESTAMP
THEN o.EVENT_TIMESTAMP
ELSE NULL
END AS LogOutTime
,CASE WHEN O.EVENT_TIMESTAMP < eInNext.EVENT_TIMESTAMP
THEN DATEDIFF(MI,eIn.EVENT_TIMESTAMP,o.EVENT_TIMESTAMP)
ELSE NULL
END AS MinutesLoggedIn
FROM [CentricityPS].[dbo].[AUDIT_EVENT] eIn
--Find next logout event
CROSS APPLY (SELECT TOP 1 eOut.EVENT_TIMESTAMP, eOut.EVENT_TYPE_ID
FROM AUDIT_EVENT eOut
WHERE eOut.EVENT_TYPE_ID in (103,105) --Log Out events
AND eOut.EVENT_TIMESTAMP > eIn.EVENT_TIMESTAMP
AND eout.PROFILE_ID = eIn.PROFILE_ID --Same user
ORDER BY eOut.EVENT_TIMESTAMP asc --Earliest first
) o
--Find next login event, use it to ignore incorrect logout events
CROSS APPLY (SELECT TOP 1 EVENT_TIMESTAMP
FROM AUDIT_EVENT
WHERE EVENT_TYPE_ID=100
AND EVENT_TIMESTAMP>eIn.EVENT_TIMESTAMP
AND PROFILE_ID = eIn.PROFILE_ID
ORDER BY EVENT_TIMESTAMP ASC
) eInNext
LEFT JOIN AUDIT_PROFILE p
ON eIn.PROFILE_ID=p.PROFILE_ID
WHERE eIn.EVENT_TYPE_ID=100 --Log In event
AND ein.EVENT_TIMESTAMP>'2014-09-07' --**Adjust as needed
) a
WHERE MinutesLoggedIn IS NOT NULL
GROUP BY LOGINNAME,TheDate
--HAVING (CAST(SUM(MinutesLoggedIn) AS float)/60)>8 --Show people logged in for more than 8 hours, ie.
ORDER BY TheDate DESC,LOGINNAME
-Justin
Posted : September 9, 2014 10:39 am
Looks like this forum doesn't like close parenthesis. I will email.
Posted : September 9, 2014 10:42 am
