Number of successful logins since day 1
SELECT user_name, COUNT(*) TOTAL
FROM orasso.wwsso_audit_log_table$
WHERE message LIKE '%Login successful%'
AND user_name LIKE '%.%'
GROUP BY user_name
ORDER BY COUNT(*) desc;
Usernames and email addresses of successful logins since day 1
SELECT user_name, ods.ct_mail.attrvalue EMAIL_ADDRESS, COUNT(*) SUCCESSFUL_LOGIN, MAX(TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI:SS')) TIME_OF_LOGIN
FROM orasso.wwsso_audit_log_table$, ods.ct_cn, ods.ct_mail
WHERE message LIKE '%Login successful%'
AND ods.ct_cn.entryid = ods.ct_mail.entryid
AND UPPER(ods.ct_cn.attrvalue) = user_name
GROUP BY user_name, ods.ct_mail.attrvalue
ORDER BY COUNT(*) DESC, user_name
Users that have never logged in
SELECT UPPER(ods.ct_cn.attrvalue) AS USER_NAME, ods.ct_mail.attrvalue EMAIL_ADDRESS
FROM ods.ct_cn, ods.ct_mail
WHERE ods.ct_cn.entryid = ods.ct_mail.entryid
AND UPPER(ods.ct_cn.attrvalue) NOT IN (SELECT user_name FROM orasso.wwsso_audit_log_table$ WHERE message LIKE '%Login successful%' OR user_name NOT LIKE '%.%')
ORDER BY USER_NAME
Number of successful password changes since day 1
SELECT user_name, COUNT(*) TOTAL
FROM orasso.wwsso_audit_log_table$
WHERE message LIKE '%Change password successful%'
AND user_name like '%.%'
GROUP BY user_name
ORDER BY COUNT(*) desc;
Users that have not logged on in the last 90 days
SELECT UPPER(ods.ct_cn.attrvalue) AS USER_NAME, ods.ct_mail.attrvalue EMAIL_ADDRESS
FROM ods.ct_cn, ods.ct_mail
WHERE ods.ct_cn.entryid = ods.ct_mail.entryid
AND UPPER(ods.ct_cn.attrvalue) NOT IN (SELECT user_name FROM orasso.wwsso_audit_log_table$ WHERE (message LIKE '%Login successful%' AND log_date > SYSDATE - 90) OR user_name NOT LIKE '%.%')
ORDER BY USER_NAME
| 
.com