Looking for usage within a single hour block. Is that possible?
This lists the logins within the last 30 days. You'd just need to change the timestamp to be something like
AND aa.TIMESTAMP between to_date('09/01/2017 00:00:00','mm/dd/yyyy HH24:MI:SS') AND to_date('09/03/2017 23:59:59','mm/dd/yyyy HH24:MI:SS')
to be more specific....
-- From Jeff Kelley
-- Wanted to share this as I think it is a helpful root query for other stuff you might want to do with Activity Accumulator.
-- As is, this simply provides a count of unique logins by date for the past 30 days, but you could tweak it for other purposes
-- if you were trying to get certain daily stats.
-- If you want all logins, not just unique, simply remove unique from line 2. (both places)
-- TO_DATE (aa.TIMESTAMP) is used to collapse the timestamp to just the date - no hours/minutes, etc.
-- I joined the users table so as to ensure that I was not counting the 'bbsupport' user that is used for monitoring blackboard hosted systems.
-- aa.DATA like ('Login succeeded.') isolates all the successful login attempts.
count (unique aa.USER_PK1) as " unique logins",
TO_DATE (aa.TIMESTAMP) AS "day"
from ACTIVITY_ACCUMULATOR aa
join USERS uu on aa.USER_PK1 = uu.PK1
where aa.DATA like ('Login succeeded.')
and aa.TIMESTAMP > sysdate-30
and uu.USER_ID not like 'bbsupport'
group by TO_DATE (aa.TIMESTAMP)
Thanks! I've passed this on. Hopefully it'll work.
Exactly what we needed, thank you.
A few years ago, we manually audit login times in our online courses and then aggregated that data into a neat little image. However, this was manually downloading the course reports from many, many sections. I think with Bb Analytics there's a way to pull similar information from an SSRS dashboard.
Retrieving data ...