Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\scripts\failedLogin.pl

#  $Header: emdb/sysman/admin/scripts/failedLogin.pl /st_emdbsa_11.2/1 2009/03/25 20:35:44 mperugu Exp $
#
# Copyright (c) 2001, 2009, Oracle and/or its affiliates. All rights reserved. 
#
#    NAME
#      failedLogin.pl - <one-line expansion of the name>
#
#    DESCRIPTION
#      Event file that checks the number of failed logins.
#      By default this will be checked every 10 minutes.
#      This will only fire if audit session has been run and the 
#      db audit_trail is set to: DB, DB_EXTENDED or XML (10.2+ dbs only).
#
#      To enable audit of a session:
#      edit init.ora and add/set audit_trail=DB (or DB_EXTENDED or XML)
#      bounce the db
#      connect to the db as system and execute:
#      SQL> audit session
#
#    OUTPUT:
#      This script will generate two pieces of output:
#      1) Timestamp: of the form YYYY-MM-DD HH24:MI:SS TZD
#         This is the end point for this 10 minute time interval.
#      2) Failed Login Count: This is the failed login count for the 
#         database for this time interval.  It will not include the
#         counts from previous runs of this metric.
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#      mperugu   03/18/09 - fix for bug 8305638
#      mpawelko  09/07/05 - bug 4596296: extended_timestamp for 
#                           dba_common_audit_trail 
#      mnihalan  08/03/05 - Fix timestamp bug 
#      mnihalan  08/01/05 - Fix bug 4523096 
#      rreilly   04/22/05 - Add support for passing params to the perl
#      mnihalan  04/18/05 - Add code for audit_failed_logins_historical metric 
#      mnihalan  04/11/05 - Fix bug 4293934 
#      xchen     01/24/05 - xchen_rar_metrics_policies
#      xchen     01/24/05 - 
#      rreilly   01/13/05 - Creation
#

use strict;
use DBI;

require "emd_common.pl";

EMD_PERL_DEBUG ("-- --> ++++  Welcome to Failed Login Debugging +++++ ");

# --------------------------------------------------------------------
# +++ VARIABLES
# --------------------------------------------------------------------

# GENERAL
my $meta_db_version;
my $db_version;
my $db_is10_2;
my $lda;
my @fetch_row;
my $audit_trail_sql;
my $audit_trail_cur;
my $audit_trail_value;
my $audit_priv_sql;
my $audit_priv_cur;
my $audit_priv_value;
my $failed_login_sql;
my $failed_login_cur;
my $check_dates_sql;
my $check_dates_cur;
my $date_last_run;
my $current_day;

# OUTPUT
my $time;
my $collection_timestamp;
my $failed_login_count = 0;
my $current_interval_min = $ENV{EM_CURRENT_INTERVAL_MIN};

# INPUT
# The DB Connection info is passed in by the metric definition

my %stdinArgs = get_stdinvars();
my $username = $stdinArgs{"EM_TARGET_USERNAME"};
my $password = $stdinArgs{"EM_TARGET_PASSWORD"};
my $address = $ENV{EM_TARGET_ADDRESS};
my $role = $ENV{EM_TARGET_ROLE};
my $targetGuid = $ENV{EM_TARGET_GUID};
my $state_root = $ENV{EM_AGENT_STATE_DIR};

my $mode = 0;
if($role =~ /SYSDBA/i)
{
  $mode = 2;
}
elsif($role =~ /SYSOPER/i)
{
  $mode = 4;
}

my $timeperiod = $ENV{EM_TIMEPERIOD};
if ($timeperiod ne 'HISTORICAL')
{
  $timeperiod = 'CURRENT';
}

if ($current_interval_min eq "")
{
  $current_interval_min = 30;
}

my $update_statefile = $ENV{EM_UPDATE_STATEFILE};
if ($update_statefile eq "")
{
  $update_statefile = 'FALSE';
}

EMD_PERL_DEBUG ("-- --> Update State File? $update_statefile");

my $separator = $^O =~ m/MSWin32/ ? "\\" : "\/";

#
# Location of the offsets state file is
#  $EMDROOT/sysman/emd/state/<TARGET_GUID>.<filename>
#
my $scannedFile = $state_root.$separator."sysman".$separator."emd".$separator."state".$separator."$targetGuid"."_failedLogin.log";

EMD_PERL_DEBUG ("-- --> timeperiod : $timeperiod");
if ($timeperiod eq 'HISTORICAL')
{
  # ------------------------------------------------------------------
  # Open scanned file for reading (if scanned file does not exist, create it.)
  # ------------------------------------------------------------------
  if (!open(SCANNED, "+>> $scannedFile"))
  {
    EMAGENT_PERL_DEBUG("target: $targetGuid; Cannot open $scannedFile for read/write.");
    exit 1;
  }

# seek to top of file
  seek(SCANNED, 0, 0);

# file only contains a single line, a timestamp
  while (<SCANNED>)
  {
      my @pos =  $_;
      my $pos = \@pos;

$date_last_run = $pos->[0];
      $date_last_run =~ s/\s+$//;
  }

close(SCANNED);
}

# INPUT DEBUG 
#my $username = "sysman";
#my $password = "sysman";
#my $address = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))(CONNECT_DATA=(SID=)))";

# --------------------------------------------------------------------
# +++ Establish Target DB Connection
# --------------------------------------------------------------------

$lda = DBI->connect('dbi:Oracle:', "$username@".$address, "$password", 
    {ora_session_mode => $mode, PrintError => 0, RaiseError => 0})
    or die "em_error=Could not connect to $username/$address: $DBI::errstr\n";
register_metric_call($lda);

# --------------------------------------------------------------------
# +++ Get DB Version
# --------------------------------------------------------------------

# Major version number
$meta_db_version = $ENV{VersionCategory};
if (!defined($meta_db_version))
{
    $meta_db_version = $ENV{VERSIONCATEGORY};
}

$db_version = $meta_db_version;
if (index($db_version, '8') == 0)
{
    $db_version = 8;
}
elsif (index($db_version, '9') == 0)
{
    $db_version = 9;
}
else
{
    $db_version = substr($db_version, 0, 2);
}

# True if the db is 10.2 or higher.
$db_is10_2 = 0;
if ($db_version > 10 || $meta_db_version eq '10gR2')
{
    $db_is10_2 = 1;
}

# --------------------------------------------------------------------
# +++ Determine Audit Trail Type
#
# The audit_trail init parameter tells us how audit data
# is stored by the database.
#
# Currently we only support DB, DB_EXTENDED and XML, since in these
# modes audit data is stored in database views.
# XML is only valid for 10.2+ versions of the database.
# We do not currently support the other audit_trail settings:
# OS,TRUE,FALSE
#
# --------------------------------------------------------------------

$audit_trail_sql = "SELECT value ".
                   "FROM v\$parameter ".
                   "WHERE name='audit_trail'";

$audit_trail_cur = $lda->prepare($audit_trail_sql)
    or die "em_error=prepare($audit_trail_sql): $DBI::errstr\n";
$audit_trail_cur->execute()
    or die "em_error=audit_trail_cur->execute(): $DBI::errstr\n";

@fetch_row = $audit_trail_cur->fetchrow_array();
$audit_trail_value = $fetch_row[0];

# --------------------------------------------------------------------
# +++ Determine Correct SQL to get Audit Info
#
# If audit_trail is DB or DB_EXTENDED use dba_audit_session .
# If audit_trail is XML (10.2+ dbs only) use dba_common_audit_trail.
# Else, exit - there's no audit info to collect
#
# --------------------------------------------------------------------

EMD_PERL_DEBUG ("-- --> audit_trail value: $audit_trail_value");
EMD_PERL_DEBUG ("-- --> db version is 10gR2 or greater? $db_is10_2");

# ---------------------------------------------------------------------
# TIMEPERIOD CURRENT:
# Select failed login count for the last specified interval 
# (default is every 30 minutes).
# ----------------------------------------------------------------------

if ($timeperiod eq 'CURRENT')
{

# Figure out the dsinterval
     my $min = $current_interval_min;
     my $hour = 0;
     my $day = 0;
     if ($min > 59)
     {
       my $interval_min = $current_interval_min;
       while ( $interval_min >= 1440 )
       {
         $day = $day + 1;
         $interval_min = $interval_min - 1440;
       }
       while ( $interval_min >= 60 )
       {
         $hour = $hour + 1;
         $interval_min = $interval_min - 60;
       }
       $min  = $interval_min;
     }

# get the SQL
     $failed_login_sql = &createCurrFailedLoginSql($audit_trail_value, $db_is10_2, $day, $hour, $min);
     
     EMD_PERL_DEBUG ("failed_login_sql: $failed_login_sql");
     
     if ($failed_login_sql eq "")
     {
        # --------------------------------------------------------------------
        # +++ Disconnect from the Target DB
        # This isn't a supported audit trail setting, so disconnect
        # --------------------------------------------------------------------
        EMD_PERL_DEBUG ("-- --> This audit_trail setting is not supported, exiting");

$lda->disconnect
            or warn "disconnect $DBI::errstr\n";

exit 0;
      }
}

# ---------------------------------------------------------------------
# TIMEPERIOD HISTORICAL:
# Read the statefile to get the collection date.
# If the statefile does not exist: ifstate update_statefile is TRUE, 
#    save the current date, in the DB TimeZone to the state file. 
# When the current date is at least one day ahead of the date in the 
#   state file, collect the data for the day listed in the state file
#   and, if update_statefile is TRUE, save the next day to the state file.
# If the dates are the same, then don't collect, just exit.
# ----------------------------------------------------------------------

else 
{
  # if statefile does not exist then write the current time stamp out to the 
  # file and exit
  if ($date_last_run eq "" && $update_statefile eq 'TRUE') 
  {

$failed_login_sql = "SELECT TO_CHAR(current_date, ".
                                "'YYYY-MM-DD') AS curr_timestamp ".
                         "from dual";
     $failed_login_cur = $lda->prepare($failed_login_sql)
          or die "em_error=prepare($failed_login_sql): $DBI::errstr\n";
     $failed_login_cur->execute()
          or die "em_error=failed_login_cur->execute(): $DBI::errstr\n";

EMD_PERL_DEBUG ("-- --> executed the failed login SQL");

@fetch_row = $failed_login_cur->fetchrow_array();
     $current_day = $fetch_row[0];
     open (SCANNED, "+> $scannedFile") or die "em_error=Cannot open $scannedFile";
     print SCANNED "$current_day";
     close(SCANNED);

$lda->disconnect
         or warn "disconnect $DBI::errstr\n";

exit 0;
  }
  else
  {
      # -------------------------------------------------------------
      # Check if the dates are different
      # ------------------------------------------------------------
     
      $check_dates_sql = "SELECT (current_date - to_date('$date_last_run', 'YYYY-MM-DD')), ".
                            " TO_CHAR(to_date('$date_last_run', 'YYYY-MM-DD') + 1, 'YYYY-MM-DD'), ".
                            " TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') ".
                         "from dual";
      $check_dates_cur = $lda->prepare($check_dates_sql)
          or die "em_error=prepare($check_dates_sql): $DBI::errstr\n";
      $check_dates_cur->execute()
          or die "em_error=check_dates_cur->execute(): $DBI::errstr\n";

EMD_PERL_DEBUG ("-- --> executed the check dates SQL");

@fetch_row = $check_dates_cur->fetchrow_array();
      my $num_days = $fetch_row[0];
      $current_day = $fetch_row[1];
      $collection_timestamp = $fetch_row[2];
      
      if ($num_days >= 1 )
      {
          $failed_login_sql = &createHistFailedLoginSql($audit_trail_value, $db_is10_2);
       
          EMD_PERL_DEBUG ("failed_login_sql: $failed_login_sql");
          if ($failed_login_sql eq "")
          {
              # --------------------------------------------------------------------
              # +++ Disconnect from the Target DB
              # This isn't a supported audit trail setting, so disconnect
              # --------------------------------------------------------------------
              EMD_PERL_DEBUG ("-- --> This audit_trail setting is not supported, exiting");

$lda->disconnect
                  or warn "disconnect $DBI::errstr\n";

exit 0;
           }
      }

# -------------------------------------------------------------
      # Else the same day and month
      # ------------------------------------------------------------
      else
      {
        $lda->disconnect
         or warn "disconnect $DBI::errstr\n";
        exit 0;
      }
   }
       
}

# --------------------------------------------------------------------
# +++ Test to verify that the CREATE SESSION priv is present
#  
# After the audit_trail is verified, verify correct privs are present
#
# When audit session (or audit create session) is run, the 
# CREATE SESSION priv is added to the priv list.  If this priv is not
# present, then audit is not keeping track of the failed_login_count.
#
# --------------------------------------------------------------------

$audit_priv_sql = "SELECT privilege ".
                   "FROM dba_priv_audit_opts ".
                   "WHERE privilege='CREATE SESSION'";

$audit_priv_cur = $lda->prepare($audit_priv_sql)
    or die "em_error=prepare($audit_priv_sql): $DBI::errstr\n";
$audit_priv_cur->execute()
    or die "em_error=audit_priv_cur->execute(): $DBI::errstr\n";

@fetch_row = $audit_priv_cur->fetchrow_array();
$audit_priv_value = $fetch_row[0];

if ($audit_priv_value ne 'CREATE SESSION')
{
  # --------------------------------------------------------------------
  # +++ Disconnect from the Target DB
  #
  # Audit is not running on this system, even though a valid audit_trail
  # was specified.
  # --------------------------------------------------------------------

EMD_PERL_DEBUG ("-- --> Missing Privs, audit is not currently monitoring failed logins on this system.");

$lda->disconnect
      or warn "disconnect $DBI::errstr\n";

exit 0;
}

# --------------------------------------------------------------------
# +++ Retrieve the Failed Connection Count and Timestamp
# --------------------------------------------------------------------

$failed_login_cur = $lda->prepare($failed_login_sql)
    or die "em_error=prepare($failed_login_sql): $DBI::errstr\n";
$failed_login_cur->execute()
    or die "em_error=failed_login_cur->execute(): $DBI::errstr\n";

EMD_PERL_DEBUG ("-- --> executed the failed login SQL");

@fetch_row = $failed_login_cur->fetchrow_array();
$time = $fetch_row[0];

# for historical we already have the right
# collection_timestamp, for current, we can 
# just use the time returned from the sql
if ($timeperiod eq 'CURRENT')
{
  $collection_timestamp = $time;
}

$failed_login_count = $fetch_row[1];

EMD_PERL_DEBUG ("-- --> mode: $timeperiod collection\n");
EMD_PERL_DEBUG ("-- --> return: $collection_timestamp | $time | $failed_login_count | $current_interval_min\n");

if ($timeperiod eq 'HISTORICAL' && $update_statefile eq 'TRUE')
{
    # --------------------------------------------------------------------
    # Reopen scanned file and write out new offset (log size), line number,
    # and time
    #
    # --------------------------------------------------------------------
    open (SCANNED, "+> $scannedFile") or die "em_error=Cannot open $scannedFile";
    print SCANNED "$current_day";
    close(SCANNED);
}

# --------------------------------------------------------------------
# +++ Print Results
#
# This returns the results to the agent, which will look for a 
# standard output line that starts with em_result. The '|' character
# is the delimiter between values.
# Results should only be printed if there was at least 1 failed login
# attempt.
#
#  TIMEPERIOD CURRENT:
#       We return time twice.  This is for stateless alert support.
#       The first time is used as the collection_timestamp in the 
#       repository.  The second time is used as a key column.  This
#       is because the metric framework does not support having a 
#       timestamp column that is also a key column but we need a key
#       to support stateless alerts correctly for now.
#       
#  TIMEPERIOD HISTORICAL:
#       The time is again returned twice.  But for historical the
#       time is really used for the 'day' info.  So, midnight is 
#       always returned.  Midnight is converted to GMT to be returned
#       as the timestamp and the key column.  Since this metric only
#       returns one result for each day, we should not get duplicate 
#       key errors. If a duplicate key error occurs it is because the
#       state file was removed and the metric attempted to collect 
#       duplicate data.  
# --------------------------------------------------------------------

if ($failed_login_count > 0)
{
    print "em_result=$collection_timestamp|$time|$failed_login_count|$current_interval_min\n";  
}

# --------------------------------------------------------------------
# +++ Disconnect from the Target DB
# --------------------------------------------------------------------

$lda->disconnect
    or warn "disconnect $DBI::errstr\n";

exit 0;

# --------------------------------------------------------------------
# +++ Create the failedLoginSQL for CURRENT collections
# --------------------------------------------------------------------

sub createCurrFailedLoginSql
{
   my ($audit_trail_value) =  $_[0];
   my ($db_is10_2) = $_[1];
   my ($day) = $_[2];
   my ($hour) = $_[3];
   my ($min) = $_[4];
   my $failed_login_sql = "";

if ($audit_trail_value =~ /DB/)
   {
     $failed_login_sql = "SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', ".
                           "'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, ".
                           "COUNT(username) AS failed_count ".
                         "FROM  sys.dba_audit_session ".
                         "WHERE returncode != 0 ".
                         "AND TO_CHAR(timestamp, ".
                              "'YYYY-MM-DD HH24:MI:SS') ".
                              ">= TO_CHAR(current_timestamp - TO_DSINTERVAL('$day $hour:$min:00'), ".
                              "'YYYY-MM-DD HH24:MI:SS') ";
   }
   elsif (($audit_trail_value =~ /XML/) && ($db_is10_2 == 1))
   {
     $failed_login_sql = "SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', ".
                           "'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, ".
                           "COUNT(db_user) AS failed_count ".
                         "FROM sys.dba_common_audit_trail ".
                         "WHERE action BETWEEN 100 AND 102 ".
                         "AND returncode != 0 ".
                         "AND TO_CHAR(extended_timestamp, ".
                              "'YYYY-MM-DD HH24:MI:SS') ".
                              ">= TO_CHAR(current_timestamp - TO_DSINTERVAL('$day $hour:$min:00'), ".
                              "'YYYY-MM-DD HH24:MI:SS') ";
     }
    else
    {
       $failed_login_sql = "";
    }
}

# --------------------------------------------------------------------
# +++ Create the failedLoginSQL for HISTORICAL collections
# --------------------------------------------------------------------

sub createHistFailedLoginSql
{
   my ($audit_trail_value) =  $_[0];
   my ($db_is10_2) = $_[1];
   my $failed_login_sql = "";

if ($audit_trail_value =~ /DB/)
   {
     $failed_login_sql = "SELECT TO_CHAR(TO_TIMESTAMP('$date_last_run' , 'YYYY-MM-DD') ".
                           "AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, ".
                           "COUNT(username) AS failed_count ".
                         "FROM sys.dba_audit_session ".
                         "WHERE returncode != 0 ".
                         "AND TO_CHAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD') ".
                           ">= '$date_last_run' ".
                         "AND TO_CHAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD') ".
                           "< TO_CHAR((TO_DATE('$date_last_run', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD')";
   }
   elsif (($audit_trail_value =~ /XML/) && ($db_is10_2 == 1))
   {
     $failed_login_sql = "SELECT TO_CHAR(TO_TIMESTAMP('$date_last_run' , 'YYYY-MM-DD') ".
                           "AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, ".
                           "COUNT(db_user) AS failed_count ".
                         "FROM  sys.dba_common_audit_trail ".
                         "WHERE action BETWEEN 100 AND 102 ".
                         "AND returncode != 0 ".
                         "AND TO_CHAR(CAST(extended_timestamp AS DATE) , 'YYYY-MM-DD') ".
                           ">= '$date_last_run' ".
                           "AND TO_CHAR(CAST(extended_timestamp AS DATE) , 'YYYY-MM-DD') ".
                           "< to_char((to_date('$date_last_run', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD')";
    }
    else
    {
       $failed_login_sql = "";
    }
}

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de