Single Sign On Stats

Here are a set of queries that provide statistics on logins to the system, I hope it helps, please let me know any comments or suggestions and I'll add them to the post.

Last 24 Hours

This Query gives you a hour by hour stat of succesful and fail logins to the
system within the last 24 hours.

select
A Hour,
count(decode(upper(message),'LOGIN FAILED','F')) FAILURE,
count(DECODE(upper(message),'LOGIN SUCCESSFUL','S')) SUCCESS ,
( count(decode(upper(message),'LOGIN FAILED','F')) +
count(DECODE(upper(message),'LOGIN SUCCESSFUL','S'))) TOTAL
from
( select to_char(LOG_DATE,'HH24') A , message
from orasso.wwsso_audit_log_table_t
where to_char(LOG_DATE,'DD,MM,YY') = to_char(sysdate,'DD,MM,YY')
and upper(message) like '%LOGIN%'
) group by A

Last X days

This Query gives you Day by Day stats of succesful and fail logins to the system within the last X days.

select
A Hour,
count(decode(upper(message),'LOGIN FAILED','F')) FAILURE,
count(DECODE(upper(message),'LOGIN SUCCESSFUL','S')) SUCCESS ,
( count(decode(upper(message),'LOGIN FAILED','F')) +
count(DECODE(upper(message),'LOGIN SUCCESSFUL','S'))) TOTAL
from
(
select to_char(LOG_DATE,'YYYY/MM/DD') A, message
from orasso.wwsso_audit_log_table_t
where to_date(LOG_DATE,'DD,MM,YY') > (sysdate-:days)
and upper(message) like '%LOGIN%'
) group by A

Hour by Hour between 2 dates

This Query gives you hour by hour stats of succesful and fail logins to the system within the 2 specified dates.

select
A Hour,
count(decode(upper(message),'LOGIN FAILED','F')) FAILURE,
count(DECODE(upper(message),'LOGIN SUCCESSFUL','S')) SUCCESS ,
( count(decode(upper(message),'LOGIN FAILED','F')) +
count(DECODE(upper(message),'LOGIN SUCCESSFUL','S'))) TOTAL
from
(
select to_char(LOG_DATE,'DD/MM/YYY HH24') A, message
from orasso.wwsso_audit_log_table_t
where to_char(LOG_DATE,'DD,MM,YY') BETWEEN '20,07,06' AND '25,07,06'
and upper(message) like '%LOGIN%'
) group by A

Minute by Minute between 2 Hours

This Query gives you minute by minute stats of succesful and fail logins to the system within the 2 specified hours on the same date.

select
A Hour,
count(decode(upper(message),'LOGIN FAILED','F')) FAILURE,
count(DECODE(upper(message),'LOGIN SUCCESSFUL','S')) SUCCESS ,
( count(decode(upper(message),'LOGIN FAILED','F')) +
count(DECODE(upper(message),'LOGIN SUCCESSFUL','S'))) TOTAL
from
(
select to_char(LOG_DATE,'HH24:MI') A, message
from orasso.wwsso_audit_log_table_t
where to_char(LOG_DATE,'DD/MM/YY HH24:MI')
between '20/07/04 11:00' AND '20/07/04 12:00'
and upper(message) like '%LOGIN%'
) group by A

User's Last Login Time

This Query returns the time stamp from the last successful and Fail Login of a given username.

select user_name, to_char(max(LOG_DATE),'DD/MM/YYYY HH24:MI') Loggin_date, message
from orasso.wwsso_audit_log_table_t
where user_name = :p_username and
upper(message) like '%LOGIN%'
group by user_name, message

Users that haven't Login for more than 1 Year

This Query returns the list of users and the login time stamp for the account that have not use SSO for more than 1 year.

select user_name, to_char(B,'DD/MM/YYYY HH24:MI') Loggin_date from
(select L.user_name, max(L.LOG_DATE) B
from orasso.wwsso_audit_log_table_t L
where upper(message) = 'LOGIN SUCCESSFUL'
group by L.user_name)
where B < sysdate-360