Print Friendly, PDF & Email

 
PURPOSE

This testcase is made up of number of steps helping out to find out the exact query that is executed by a database metric.
The example is based on the Failed Logins (Historical) metric that is regulary executed by the Oracle Enterprise Manager Cloud Control 13c Release 3.

 
 
SOLUTION
 

(1) Log in to the target server with the monitored database and apply the agent environment, eg

export AGENT_HOME=/agent13c/agent_13.3.0.0.0
export AGENT_INST=/agent13c/agent_inst
export PS1="\[\e[32;1m\]\u@\h\[\e[m\]|\A|\[\e[0;33m\]\w:\[\e[m\]\[\e[31;1m\]\[\e[m\]"

 
(2) Get the internal name of the metric used by the agent from the database.xmlp file

oracle@srv-meridb2|17:36|~: grep "Failed Logins (Historical)" $AGENT_HOME/plugins/oracle.sysman.db.agent.plugin_13.3.1.0.0/metadata/database.xmlp

      <Label NLSID="<strong>audit_failed_logins_historical</strong>">Failed Logins (Historical)</Label>

The value of NLSID is the internal name of the metric that is executed by the agent. So the metric name is the audit_failed_logins_historical.

 
(3) Every metric uses a perl script to build the query. Find out the name of that script. For that purpose open $AGENT_HOME/plugins/oracle.sysman.db.agent.plugin_13.3.1.0.0/metadata/database.xmlp file and find related section for the Failed Logins (Historical) metric.

<!--
  ======================================================================
  == Category:         Security:  Audit Failed Logins by Day
  == Collection Level: Recommended
  == Purpose:          Reports
  == NOTE: This metric is a is not intended for alerts. It is simply
  ==       intended to collect historical login information.
  ==       This metric relies on agent side state files to insure that
  ==       it does not collect duplicate data. If the state files are
  ==       deleted, then collection will start fresh for the day in
  ==       which the state file was missing and the agent is up.
  ==       When state files are missing, it is possible to have days
  ==       in which no data is collected.  This is preferrable
  ==       to collecting duplicate data, which we CANNOT do, and so
  ==       this method has been approved.
  ======================================================================
  -->

  <Metric NAME="audit_failed_logins_historical" TYPE="TABLE"
          USAGE_TYPE="COLLECT_UPLOAD" HELP="NO_HELP">
    <ValidIf>
      <CategoryProp NAME="MetricScope" CHOICES="DB"/>
    </ValidIf>
    <Display>
      <Label NLSID="audit_failed_logins_historical">Failed Logins (Historical)</Label>
    </Display>

    <CategoryValue CLASS="Default" CATEGORY_NAME="Security"/>

    <TableDescriptor>
      <ColumnDescriptor NAME="time" TYPE="STRING" IS_TIMESTAMP="TRUE" TRANSIENT="TRUE" HELP="NO_HELP">
        <Display>
           <Label NLSID="time_series_time">Time</Label>
           <Unit NLSID="EM_SYS_STANDARD_TIMESTAMP_NA">NA</Unit>
           <UnitCategory>TIME_STAMP</UnitCategory>
        </Display>
      </ColumnDescriptor>
      <ColumnDescriptor NAME="key_col_time" TYPE="STRING" IS_KEY="TRUE">
        <Display>
           <Label NLSID="time_series_time">Time</Label>
           <Unit NLSID="EM_SYS_STANDARD_TIMESTAMP_NA">NA</Unit>
           <UnitCategory>TIME_STAMP</UnitCategory>
        </Display>
      </ColumnDescriptor>
      <ColumnDescriptor NAME="failed_login_count" TYPE="NUMBER" IS_KEY="FALSE" >
        <Display>
           <Label NLSID="failed_login_count">Failed Login Count</Label>
           <Unit NLSID="EM_SYS_STANDARD_COUNT_NA">NA</Unit>
           <UnitCategory>COUNT</UnitCategory>
        </Display>
      </ColumnDescriptor>
    </TableDescriptor>
    <QueryDescriptor FETCHLET_ID="OSLineToken">
      <Property NAME="perlBin" SCOPE="SYSTEMGLOBAL">perlBin</Property>
      <Property NAME="scriptsDir" SCOPE="SYSTEMGLOBAL">scriptsDir</Property>
      <strong><Property NAME="command" SCOPE="GLOBAL"> %perlBin%/perl %scriptsDir%/failedLogin.pl </Property>
      <Property NAME="startsWith" SCOPE="GLOBAL">em_result=</Property>
      <Property NAME="delimiter" SCOPE="GLOBAL">|</Property>
      <Property NAME="ENVEM_TARGET_NAME" SCOPE="INSTANCE">NAME</Property>
      <Property NAME="STDINEM_TARGET_USERNAME" SCOPE="INSTANCE">UserName</Property>
      <Property NAME="STDINEM_TARGET_PASSWORD" SCOPE="INSTANCE">password</Property>
      <Property NAME="ENVEM_TARGET_ROLE" SCOPE="INSTANCE" OPTIONAL="TRUE">Role</Property>
      <Property NAME="ENVEM_TARGET_ORACLE_HOME" SCOPE="INSTANCE">OracleHome</Property>
                <Property NAME="ENVEM_TARGET_HOST" SCOPE="INSTANCE">MachineName</Property>
                <Property NAME="ENVEM_TARGET_PORT" SCOPE="INSTANCE">Port</Property>
                <Property NAME="ENVEM_TARGET_PROTOCOL" OPTIONAL="TRUE" SCOPE="INSTANCE">Protocol</Property>
                <Property NAME="ENVEM_TARGET_ORACLE_SID" SCOPE="INSTANCE">SID</Property>
      <Property NAME="ENVVersionCategory" SCOPE="INSTANCE">VersionCategory</Property>
      <Property NAME="ENVEM_AGENT_STATE_DIR" SCOPE="SYSTEMGLOBAL">agentStateDir</Property>
      <Property NAME="ENVEM_TARGET_GUID" SCOPE="INSTANCE">GUID</Property>
                        <Property NAME="ENVEM_TIMEPERIOD" SCOPE="GLOBAL">HISTORICAL</Property>
                        <Property NAME="ENVEM_UPDATE_STATEFILE" SCOPE="USER" OPTIONAL="TRUE">updateStateFile</Property>
    </QueryDescriptor>
  </Metric>

 <!-- END Security Audit Metrics -->

 
(4) Look for the value of command property from the found Failed Logins (Historical) section

<Property NAME="command" SCOPE="GLOBAL"> %perlBin%/perl %scriptsDir%/failedLogin.pl </Property>

 
So, according to the property value the perl script failedLogin.pl is used by the Failed Logins (Historical) metric.

oracle@srv-meridb2|17:46|~: ls $AGENT_HOME/plugins/oracle.sysman.db.agent.plugin_13.3.1.0.0/scripts/failedLogin.pl

/agent13c/agent_13.3.0.0.0/plugins/oracle.sysman.db.agent.plugin_13.3.1.0.0/scripts/failedLogin.pl

 
(5) Review the failedLogin.pl script and find the Failed Logins (Historical) related query. Depending on what values the script has during runtime (standalone database, container database, database release, audit_trail parameter) the different query will be used by the metric.

eg, the related Failed Logins (Historical) section

#True if db is 12.2 or Higher
$db_is12_2 =0;
$db_is12_1=0;
if($db_version >= 12  || $meta_db_version =~ /^12cR2/)
{
  $db_is12_2 =1;
}

if($db_version >= 12 || $meta_db_version =~ /^12cR1/)
{
   $db_is12_1=1;
}

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

sub createHistFailedLoginSql
{
   my ($audit_trail_value) =  $_[0];
   my ($db_is10_2) = $_[1];
   my ($db_is12_2) = $_[2];
   my $dbversion = $_[3];
   my $session_offset = $_[4];
   my $os_offset = $_[5];
   my $failed_login_sql = "";

   if ($audit_trail_value =~ /DB/ && $dbversion == "8")
   {
     $failed_login_sql = "SELECT TO_CHAR(TO_DATE('$date_last_run' , 'YYYY-MM-DD') - (  ($os_offset/24) - ($session_offset/24)) ".
                           ", 'YYYY-MM-DD HH24:MI:SS') || ' GMT' AS curr_timestamp, ".
                           "COUNT(username) AS failed_count ".
                         "FROM sys.dba_audit_session ".
                         "WHERE returncode != 0 ".
                         "AND  ACTION_NAME = 'LOGON' ".
                         "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 =~ /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  ACTION_NAME = 'LOGON' ".
                         "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')";

      if($db_is12_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, ".
                           " SUM(failed_count) AS failed_count ".
                           " FROM ( SELECT 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') ".
                      " UNION ".
                      " SELECT COUNT(DBUSERNAME) AS failed_count ".
                      " FROM unified_audit_trail ".
                      " WHERE ACTION_NAME='LOGON' and return_code <> 0 ".
                      "AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') ".
                      ">= '$date_last_run' ".
                      "AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') ".
                      "< TO_CHAR((TO_DATE('$date_last_run', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD'))";
      }

   }
  elsif (($audit_trail_value =~ /DB_EXTENDED/) && ($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(username) AS failed_count ".
                         "FROM sys.dba_audit_session ".
                         "WHERE returncode != 0 ".
                         "AND  ACTION_NAME = 'LOGON' ".
                         "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')";

       if($db_is12_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, ".
                           " SUM(failed_count) AS failed_count ".
                           " FROM ( SELECT 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') ".
                         " UNION ".
                      " SELECT COUNT(DBUSERNAME) AS failed_count ".
                      " FROM unified_audit_trail ".
                      " WHERE ACTION_NAME='LOGON' and return_code <> 0 ".
                      "AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') ".
                      ">= '$date_last_run' ".
                      "AND TO_CHAR(CAST(event_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/)||($audit_trail_value =~ /XML_EXTENDED/)||($audit_trail_value =~ /NONE/)||($audit_trail_value =~ /OS/)) && ($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 STATEMENT_TYPE = 'LOGON' ".
                         "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')";

      if($db_is12_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, ".
                         "  SUM(failed_count) AS failed_count FROM ".
                         " (SELECT COUNT(db_user) AS failed_count ".
                         "FROM  sys.dba_common_audit_trail ".
                         "WHERE action BETWEEN 100 AND 102 ".
                         "AND returncode != 0 ".
                         "AND STATEMENT_TYPE = 'LOGON' ".
                         "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') ".
                         " UNION ".
                      " SELECT COUNT(DBUSERNAME) AS failed_count ".
                      " FROM unified_audit_trail ".
                      " WHERE ACTION_NAME='LOGON' and return_code <> 0 ".
                      "AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') ".
                      ">= '$date_last_run' ".
                      "AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') ".
                      "< TO_CHAR((TO_DATE('$date_last_run', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD'))";
         }
    }
    else
    {
       $failed_login_sql = "";
    }
    return $failed_login_sql;
}

 
For instance, for the standalone 12cR2 database with the audit_trail=DB the following query is used by the metric

     if($db_is12_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, ".
                           " SUM(failed_count) AS failed_count ".
                           " FROM ( SELECT 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') ".
                      " UNION ".
                      " SELECT COUNT(DBUSERNAME) AS failed_count ".
                      " FROM unified_audit_trail ".
                      " WHERE ACTION_NAME='LOGON' and return_code <> 0 ".
                      "AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') ".
                      ">= '$date_last_run' ".
                      "AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') ".
                      "< TO_CHAR((TO_DATE('$date_last_run', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD'))";
      }

 
(6) Knowing the full text of the query you can try to find its sql_id and other runtime details (if the query is still in library cache)

SET LINES 200
SET PAGES 999
COL SQL_ID FOR A15
COL CHILD_NUMBER FOR 99999
COL SQL_FULLTEXT FOR A170 WORD_WRAPPED
SET LONG 200000000

SELECT SQL_ID,
       CHILD_NUMBER,
       SQL_FULLTEXT
FROM
       V$SQL
WHERE
       PARSING_SCHEMA_NAME='DBSNMP' AND
       SQL_FULLTEXT LIKE '%SELECT TO_CHAR(TO_TIMESTAMP%FROM sys.dba_audit_session%UNION%SELECT COUNT(DBUSERNAME) AS failed_count %FROM unified_audit_trail%WHERE ACTION_NAME=% and return_code <> 0%';

SQL_ID          CHILD_NUMBER SQL_FULLTEXT
--------------- ------------ -------------------------------------------------------------------------------------------------------------------------------
1y1k03vvus43c              0 SELECT TO_CHAR(TO_TIMESTAMP('2019-03-04' , 'YYYY-MM-DD')  AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp,
                              SUM(failed_count) AS failed_count  FROM ( SELECT COUNT(username) AS failed_count FR
                             OM sys.dba_audit_session WHERE returncode != 0 AND TO_CHAR(CAST(timestamp AS DATE) ,
                              'YYYY-MM-DD') >= '2019-03-04' AND TO_CHAR(CAST(timestamp AS DATE) , 'YYYY-MM-DD')
                             < TO_CHAR((TO_DATE('2019-03-04', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD')  UNION  SELECT CO
                             UNT(DBUSERNAME) AS failed_count  FROM unified_audit_trail  WHERE ACTION_NAME='LOGON'
                              and return_code <> 0 AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') >= '
                             2019-03-04' AND TO_CHAR(CAST(event_timestamp AS DATE) , 'YYYY-MM-DD') < TO_CHAR((TO_
                             DATE('2019-03-04', 'YYYY-MM-DD') + 1), 'YYYY-MM-DD'))

 
So, the query of the Failed Logins (Historical) metric is running with sql_id 1y1k03vvus43c in the target database.

 
 
REFERENCES
 
Cloud Control 12c: How to view metric values using emcli (Doc ID 1572180.1)
EM 12c, EM 13c: Troubleshooting Database Metrics in Enterprise Manager 12c and 13c Cloud Control (Doc ID 2032156.1)

 
 

Version  : 18:14 08.09.2020
Database : 11.2.0.4,12.1.0.1
OEM      : 13.3.0.0.0