jkelley_blackboard

Daily Logins

Discussion created by jkelley_blackboard on Nov 7, 2016

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.

 

[Note this uses only ASR available tables]

 

- 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.

 

 

select

  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)

Outcomes