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