The Oracle Application Server 10g Infrastructure - Using the SSO Audit Log Tables
(Page 10 of 11 )
There is an important log table inside the iasdb instance in the orasso schema, called wsso_ audit_log_table_t, that you can use to extract SSO interaction information. This table contains many detailed metrics about SSO interaction:
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 can take the data from this table and create an SSO summary report for execution in SQL*Plus. Here is a common SSO activity report:
sso_audit_log.sql
set echo off
set feedback off
ttitle off
set heading on
set pages 999
set lines 80 prompt*************************************************** prompt SSO Activity summary Report prompt*************************************************** alter session set nls_date_format = 'YYYY MM DD'; col c0 heading 'date' format a15
col c1 heading 'action' format a20
col c2 heading 'Count' format 99,999 break on c0 skip 2
compute sum of c2 on c0
select
to_char(log_date,'yyyy-mm-dd hh24')c0,
action c1,
count(*) c2
from
ORASSO.WWSSO_AUDIT_LOG_TABLE_T
group by
to_char(log_date,'yyyy-mm-dd hh24'),
action; prompt *************************************************** prompt SSO Message summary Report prompt*************************************************** col c1 heading 'message' format a20 select to_char(log_date,'yyyy-mm-dd hh24') c0, message c1,
count(*) c2 from
ORASSO.WWSSO_AUDIT_LOG_TABLE_T
group by
to_char(log_date,'yyyy-mm-dd hh24'),
message; set lines 80 prompt ********************************************
prompt SSO Activity summary Report
prompt ********************************************
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'; | col c1 Heading 'Date' | format a20 |
| col c2 heading 'User' | format a10 |
col c3 heading 'Action | format a10 |
col c4 heading
'Message' format a20select
log_date c1,
user_name c2,
action c3,
message c4
from
ORASSO.WWSSO_AUDIT_LOG_TABLE_T
;
The following listing shows the output from this report. Here you see a summary of all login operations, summed by hour of the day. You also see counts of all SSO messages summed by hour of the day. The last report in this section shows all SSO details.
*************************************************** SSO Activity summary Report *************************************************** date action Count
----------------- -------------------- --------
2003-06-04 09 LOGIN 4
*************** ------ sum 4
2003-06-04 10 LOGIN 1
*************** ------
sum 1
2003-06-04 11 LOGIN 2
*************** -------
sum 2
2003-06-04 14 LOGIN 1
*************** -------
sum 1
2003-06-04 20 LOGIN 2
*************** -------
sum 2
2003-06-05 08 LOGIN 1
*************** -------
sum 1
2003-07-08 14 LOGIN 3
*************** -------
sum 3
2003-07-10 08 LOGIN 4
*************** -------
sum 4 *************************************************** SSO Message summary Report *************************************************** date message Count
-------------------- ---------------------------- --------
2003-06-04 09
| Login failed | 4 |
|---|
| *************** | | --------- |
| sum | | 4 |
2003-06-04 10 Login Successful 11
Login failed 4
*************** -------
sum 15
2003-06-04 11 Login Successful 334
*************** -------
sum 334
2003-06-04 14 Login Successful 432
*** Login failed 14 sum 446
2003-06-04 20 Login Successful 62
Login failed 3
*************** -------
sum 65
2003-06-05 08 Login Successful 433
Login failed 61
*************** -------
sum 494
2003-07-08 14 Login failed 3
*************** -------
sum 3
2003-07-10 08 Login failed 4
*************** -------
sum 4
************************************************ SSO Activity Detail Report *************************************************** Date User Action Message --------------- -------------- ------------ --------------
| 2003-06-04 09:45:42 | GARMANYJ | LOGIN | Login failed |
| 2003-06-04 11:46:27 | GARMANYJ | LOGIN | Login Successful |
| 2003-06-04 14:32:52 | GARMANYJ | LOGIN | Login Successful |
| 2003-06-04 20:58:44 | GARMANYJ | LOGIN | Login Successful |
| 2003-06-05 08:58:24 | GARMANYJ | LOGIN | Login Successful |
| 2003-07-08 14:28:20 | GARMANYJ | LOGIN | Login failed |
| 2003-07-08 14:28:26 | GARMANYJ | LOGIN | Login failed |
| 2003-07-08 14:28:37 | GARMANYJ | LOGIN | Login failed |
| 2003-07-10 08:29:49 | GARMANYJ | LOGIN | Login failed |
| 2003-07-10 08:29:53 | GARMANYJ | LOGIN | Login failed |
| 2003-07-10 08:30:00 | GARMANYJ | LOGIN | Login failed |
| 2003-07-10 08:30:05 | GARMANYJ | LOGIN | Login failed |
| 2003-06-04 09:42:24 | IAS_ADMIN | LOGIN | Login failed |
| 2003-06-04 09:42:12 | ORACLADMIN | LOGIN | Login failed |
| 2003-06-04 09:42:44 | ORACLADMIN | LOGIN | Login failed |
| 2003-06-04 10:22:18 | ORCLADMIN | LOGIN | Login Successful |
| 2003-06-04 11:39:45 | ORCLADMIN | LOGIN | Login Successful |
| 2003-06-04 20:53:24 | ORCLADMIN | LOGIN | Login Successful |
You can also write a script to check the availability of SSO. As noted earlier, if the infrastructure is down or SSO cannot accept connections, no users can access your system. Hence, frequently checking SSO connectivity is an important Application Server 10g administration task.
Here is a Perl script that you can use to check SSO availability. This script checks to see if the Single Sign-On (SSO) Server is accessible and is responding to HTTP requests.
check_sso.pl PERL5LIB=
$ORACLE_HOME/
perl/lib/
5.6.1:
$ORACLE_HOME/
perl/
lib/site_perl/
5.6.1 ; export PERL5LIB ;
$ORACLE_HOME/
perl/bin/perl -e '
$returncode = "NOK";
$oraclehome = $ENV{'ORACLE_HOME'};
use IO::Socket;
$url = $ARGV[0];
$host = $ARGV[1];
$searchstring = $ARGV[2];
open FILE, "$oraclehome/install/portlist.ini" or die "File portlist.ini not found";
while ($line = <FILE>) {
$i = index $line, $searchstring;
if ( $i == 0 ) {
if ($line =~ /(=)([ ]*)(\S+)/) {
$port = $3;
}
}
}
close FILE;
$this_socket = new IO::Socket::INET PeerAddr => $host, Timeout => "9",
PeerPort => $port, Proto => "tcp" ;
if(!$this_socket){
$returncode = "NOK";
} else {
$get_request = ("GET $url HTTP/1.0\r\n" );
$this_socket->print ($get_request);
$this_socket->print("Accept: text/plain\n");
$this_socket->print("Accept: text/html\n");
$this_socket->print("UserAgent: LoogBrowser/1.0\n\n"); $returncode="POK";
while ($line=($this_socket->getline()))
{
if ( $line =~ /(HTTP\/1.1 200 OK)/) {
$returncode = "POK";
}
if ( $line =~ /(Access Partner Applications)/) {
$returncode = "OK";
}
}
}
print $returncode
' "/pls/orasso/orasso.home" "localhost" "Oracle HTTP Server port"
If this script returns the standard output of “OK,” then SSO can accept HTTP requests. Many Application Server 10g administrators place this script into a cron task and run it every five minutes. If there is a failure in SSO, a pager alert is immediately sent to the administrator. Now, let’s look at using the mod_osso utility for SSO administration.
Next: SSO Administration Using the mod_osso Utility >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter two of Oracle Application Server 10g Administration Handbook by John Garmany and Donald Burleson(McGraw-Hill/Osborne, 2004; ISBN: 0072229586). Check it out at your favorite bookstore. Buy this book now.
|
|
/ 10 
