How do I keep track of the SSO (Single Sign On) users' transactions in Oracle Portal?

I use the ORASSO.WWSSO_AUDIT_LOG_TABLE_T table to keep track of my SSO transactions in the Oracle Portal. I made some queries and use them most of the time to report any suspicious activities if the login failed to trigger or any other activities on my application server..

The WWSSO_AUDIT_LOG_TABLE_T  table is very useful and is owned by the ORASSO user. It contains very important information about the Single Sign On transactions. 

The following is a list of columns used by the ORASSO.WWSSO_AUDIT_LOG_TABLE_T table.

SQL> desc orasso.wwsso_audit_log_table_t
Name                                 Null                             Type
--------------------------- --------                        ----------
SUBSCRIBER_ID            NOT NULL                 NUMBER
LOG_ID                           NOT NULL                  NUMBER
USER_NAME                  NOT NULL                 VARCHAR2(256)
AUDIT_TYPE                  NOT NULL                 VARCHAR2(32)
ACTION_CODE              NOT NULL                 NUMBER
ACTION                          NOT NULL                  VARCHAR2(80)
IP_ADDRESS                  NOT NULL                  VARCHAR2(32)
APP_SITE                        NOT NULL                  VARCHAR2(80)
MESSAGE                       NOT NULL                  VARCHAR2(256)
LOG_DATE                     NOT NULL                  DATE
PROCESS_DATE                                                  DATE
EMAIL                                                                   VARCHAR2(80)
MAINTAINER_ID                                                 VARCHAR2(80)


You may not use much of the SUBSCRIBER_ID, LOG_ID, and MAINTAINER_ID columns. The SUBSCRIBER_ID and LOG_ID are generated by ORASSO. The SUBSCRIBER_ID is a unique number and most of the time is only 1. The LOG_ID is a unique number which will be assigned anytime a USER_NAME logs-in to the portal or application server.

The following are some of the most frequent queries that I use to retrieve data from the orasso.wwsso_audit_log_table_t table.

Assuming that you are login to sql*plus:
SQL> connect orasso/password
Notice that you should have password to the ORASSO user or SYS.

Then, I set the following SQL*PLUS environment options.
SQL> set linesize 100
SQL> col user_name format a25
SQL> col ip_address format a15
SQL> col message format a25
SQL> col email format a20
SQL> set pagesize 1000
SQL> set arraysize 15

You need to set the pagesize high so that you won't get too many headings. Also, set arraysize so that you can use the horizontal scroll bar.

The following query shows the first 100 records that list the users that have used different IP_ADDRESS or computer to access the application server. This gives me a clue as to who is using different computers to access the application server using SSO.

Query:
BREAK ON user_name SKIP 1
SELECT USER_NAME, IP_ADDRESS, MESSAGE, EMAIL
FROM orasso.wwsso_audit_log_table_t
where rownum < 101
ORDER BY 1, 2
/

USER_NAME IP_ADDRESS MESSAGE                     EMAIL
---------------- --------------- ------------------------- ------
PORTAL         186.33.206.77 pwd_exp_err                   John@iselfschooling
PORTAL                                 Login Successful              John@iselfschooling
PORTAL                                 Login Successful
PORTAL         196.33.200.88 Login Successful
PORTAL                                 Login failed
PORTAL                                 Login Successful
PORTAL                                 Login Successful

Most of the time, my boss wants to know who accessed the system with the most failed logins on a specific date sorted in descending order. We sort these in descending order so that we can see the user that has the most failures in the login first.

Query:
SELECT user_name, COUNT(*) "Login Failed Since July"
FROM orasso.wwsso_audit_log_table_t
WHERE message LIKE '%failed%' AND
log_date >= TO_DATE('01-JUL-06')
GROUP BY user_name
ORDER BY 2 DESC
/

USER_NAME               Login Failed Since July 
------------------------- ----------------------- 
USER1                                                       120 
USER2                                                         45 
USER3                                                         17 


To know who is working hard we can just reverse the query to a successful login with a specific date.

Query:
SELECT user_name, COUNT(*) "Login Successfully Since July"
FROM orasso.wwsso_audit_log_table_t
WHERE message LIKE '%Successful%' AND
log_date >= TO_DATE('01-JUL-06')
GROUP BY user_name
ORDER BY 2 DESC
/


USER_NAME                Login Successfully Since July
------------------------- -----------------------------
PORTAL                                                             689
ORCLADMIN                                                      17
DIANE                                                                  12
ANNETTE                                                            11
DIANA                                                                   7

.. rows selected.


Sometimes, we need to know when was the last time a user logged-in to the application server. We don't care whether it was successful or not. 

Query:
SELECT user_name, MAX(log_date) "Last login date"
FROM orasso.wwsso_audit_log_table_t
WHERE log_date > TO_DATE('01-JUL-06')
GROUP BY user_name
/

USER_NAME               Last logi
------------------------- ---------
USER1                          08-AUG-06
USER2                          15-SEP-06
USER3                          11-AUG-06


To get statistics about daily access to our application server we use the following query.

Query:
SELECT TO_CHAR(log_date,'YYYY-MM-DD') "transaction date", COUNT(*) "Number of Login"
FROM orasso.wwsso_audit_log_table_t
GROUP BY TO_CHAR(log_date,'YYYY-MM-DD')
ORDER BY 1
/

transactio         Number of Login
----------        ---------------
2006-01-31                  1034
2006-02-03                  3453
2006-06-14                27650
2006-06-20            1234215
2006-06-29              564398



To get more detail on daily activities and how many of the logins of the users failed or were successful, we use the following query.

Query:
BREAK ON "transaction date" skip 1
SELECT TO_CHAR(log_date,'YYYY-MM-DD') "transaction date", message, COUNT(*) "Number of Login"
FROM orasso.wwsso_audit_log_table_t
GROUP BY TO_CHAR(log_date,'YYYY-MM-DD'), message
ORDER BY 1, 2
/

transactio      MESSAGE                    Number of Login
----------     ------------------------- ---------------
2006-01-31 Login Successful                                  1

2006-02-03 Login Successful                                  2
                    Login failed                                          1

2006-06-14 Login Successful                                  1
                    pwd_exp_err                                       1

2006-06-26 Login Successful                                10
                    Login failed                                          2



The following is a list of the users that logged-in with different IP addresses.

Query:
BREAK ON user_name skip 1
BREAK ON ip_address skip 1
SELECT user_name, ip_address, COUNT(*) "Number of login"
FROM orasso.wwsso_audit_log_table_t
GROUP BY user_name, ip_address
ORDER BY 1,3 DESC
/


USER_NAME IP_ADDRESS               Number of login
------------------------- --------------- ---------------
USER1            176.66.106.66                                    9
                        176.66.114.80                                    8
                        176.66.114.171                                   2

USER2             158.77.219.65                                    1
                         158.77.219.186                                  1




The following is a list that provides ip_addresses and user names that have so many failed log-ins within IP_ADDRESS since a specific date.

Query:
BREAK ON user_name skip 1
BREAK ON ip_address skip 1
SELECT ip_address, user_name, COUNT(*) "Login Failed Since July"
FROM orasso.wwsso_audit_log_table_t
WHERE message LIKE '%failed%' AND

                log_date > TO_DATE('01-JUL-06')
GROUP BY ip_address, user_name
ORDER BY 1,3 DESC
/

IP_ADDRESS USER_NAME                Login Failed Since July
--------------- ------------------------- -----------------------
145.45.106.66 PORTAL                                                     10
                        DIANA                                                          9
                        ANNETTE                                                     2

167.24.106.73 PORTALXX                                                  3
                        PORTAL                                                        2

The following is a list that provides you with a list of all logs plus the one was locked separated with ip_address for the PORTAL account on May 16, 2007.

Query:

SQL> set linesize 100
SQL> col user_name format a25
SQL> col ip_address format a15
SQL> col message format a45
SQL> col email format a20
SQL> set pagesize 1000
SQL> set arraysize 15

SQL> BREAK ON ip_address skip 1
SQL> SELECT user_name, ip_address, message,
                           to_char(log_date,'YYYY-MM-DD HH24:MI:SS')
            FROM orasso.wwsso_audit_log_table_t
            WHERE (log_date >= TO_DATE('16-MAY-07') AND 
                            log_date < TO_DATE('17-MAY-07')) AND
                             user_name LIKE 'PORTAL%' AND
                             (message LIKE '%lock%' OR

                              message LIKE '%fail%' OR 

                              message LIKE '%Login%')
            ORDER BY 2,4
/