title: Quick orasso audit log report on an account link: https://onemoretech.wordpress.com/2012/04/20/quick-orasso-audit-log-report-on-an-account/ author: lembobro description: post_id: 2586 created: 2012/04/20 11:58:58 created_gmt: 2012/04/20 15:58:58 comment_status: closed post_name: quick-orasso-audit-log-report-on-an-account status: publish post_type: post

Quick orasso audit log report on an account

Had to do a couple of reports from orasso.wwsso_audit_log_table$ the other day and decided to record the syntax here for posterity.

[me@test tmp]$ sqlplus ORASSO/12345@infra

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 20 11:01:09 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

* * *

SQL> spool report.html;

SQL> set markup html on;

SQL> select USER_NAME,ACTION,MESSAGE,
  2  to_char(LOG_DATE,'yyyy.mm.dd hh24:mi:ss')
  3  from ORASSO.WWSSO_AUDIT_LOG_TABLE$
  4  where USER_NAME = 'L123456'; 

This makes for a simple 4 column report giving the user's ID, the action taken (usually "LOGIN"), the message returned (like "Login Failed" or "Login Unsuccessful") and the date and time, down to the second, of the event. If your network topography allows you to see the real IP address of incoming clients you could also add "IP_ADDRESS" for that data element.

Copyright 2004-2019 Phil Lembo