[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 851
  • Last Modified:

Assistance for newbie creating a package using DBMS_SCHEDULER and SYS.DBA_AUDIT_TRAIL

I'm very new to PL/SQL (and programming in general). I would like to create a report that shows the # of logins per hour for a separate application and it will be based on this program. For this program, DB1 will be my production database. The user can provide a readonly, no sys privs, login to DB1. I am writing all code on DB2.

I am using SYS.DBA_AUDIT_TRAIL for finding the accumulated logins.

I am trying to create a program (and I want it packaged) that aggregates logins by hour and inserts the results into this table.

(Table created on DB2)
CREATE TABLE  access_log (
access_date DATE
,access_hour NUMBER
,login_count NUMBER 
,audit_timestamp TIMESTAMP
)

Open in new window


For the above column definitions:
access_date = date of the aggregated logins
access_hour = the hour of the logins
login_count = count of logins
audit_timestamp = when the aggregation occurred

This program needs to run on a schedule of no more than once per hour. I'm making no assumption that this program will run every hour, for instance: I realize there is a possibility that the database could become inaccessible to me for a few hours.

If my program just looks at ‘the last hour’ then I will have lost all data for the inaccessible hours so I need to create it so as to aggregate all logins since the last successful aggregation.

With my table, I am trying to create an audit process log that will log any errors that have occurred. I intend to only log errors (not necessarily every run) in this table.

I am going to use DB privs, dbms_scheduler exec privs, CREATE JOB priv.

So far I have generated a query that I am going to use in the package for retrieving a login date, login hour, and a count
SELECT 
       TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
       ,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
      ,COUNT(*)
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
GROUP BY TRUNC(TIMESTAMP, 'DD') 
        ,TO_CHAR(TIMESTAMP, 'HH24')
ORDER BY 1 , 2
--on db1

Open in new window


I have a functional procedure made that I am going to eventually insert into my package as well:

CREATE OR REPLACE FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP IS

              /* variables */
              L_LOGGED_TIMESTAMP TIMESTAMP;             

BEGIN
              /* query to return MAX timestamp */
              SELECT 
              MAX(AUDIT_TIMESTAMP)
              INTO L_LOGGED_TIMESTAMP
              FROM ACCESS_LOG;              
             
              /* returning MAX timestamp value*/
              RETURN L_LOGGED_TIMESTAMP;
END;

Open in new window


Finally I have this non-functional proc I began to work on but didn't compile the first run (will be glad for any tips):

create or replace procedure access_date_hour_count IS
       L_QUERY VARCHAR2(4000);
begin
  SELECT 
       TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
       ,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
      ,COUNT(*)
      INTO L_QUERY
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
GROUP BY TRUNC(TIMESTAMP, 'DD') 
        ,TO_CHAR(TIMESTAMP, 'HH24')
ORDER BY 1 , 2;
end access_date_hour_count;

Open in new window


My intention is to take my main query and turn it into a standalone procedure. Didn't compile,  so please tell me what you see I'm doing wrong. Thanks!
0
Mark_Co
Asked:
Mark_Co
  • 7
  • 3
  • 2
  • +1
5 Solutions
 
ianmills2002Commented:
What you have written is not a procedure it should be a view.

create or replace view access_date_hour_count
AS
begin
  SELECT 
       TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
       ,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
      ,COUNT(*)
      INTO L_QUERY
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
GROUP BY TRUNC(TIMESTAMP, 'DD') 
        ,TO_CHAR(TIMESTAMP, 'HH24')
ORDER BY 1 , 2;

                                  

Open in new window



Then you perform a select on the view

select * from access_date_hour_count

Open in new window


OR

To you want to return a reference cursor of your query?


You cannot put the results of a query into a single variable.

Ian
0
 
Mark_CoAuthor Commented:
Thank you for the tip
0
 
slightwv (䄆 Netminder) Commented:
To build on the post above:  Inside a procedure the results of a select need to go somewhere.

You cannot select into something inside a create view statement though.

I also don't think you really need a view here.

I would just have the procedure insert into audit_log where remote_audit_table_timestamp > max(timestamp) fro maudit_log.

I'm also not fully understanding the audit_log table design.  Why have a date, hour and a timestamp column?  Hour is derived from the other columns.  No need to store it unelss performance is a huge issue.

I'm also not understading what will be sotred in access_date and audit_timestamp.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Geert GruwezOracle dbaCommented:
a view will be bad for performance.

why not aggregate until the last aggregated time for every completed interval ?
in simple column terms:
DT_AGG:  DATETIME of the aggregation (for example, every hour)
APP: name of the application, (you might want to do others eventually)
EVENTCOUNT: number of occurences

> in pl/sql
step 1: determine the last aggregated time
step 2: aggregate data from the last aggregated time to last completed time frame.
step 2.b store in subtable
0
 
Mark_CoAuthor Commented:
So here is where I am so far:

CREATE OR REPLACE PACKAGE PKG_AUDIT_ACCESS_TABLES IS

              /*
               Original Author: Me
               Created Date: 23-Aug-2012
               Purpose: For storing procedures to drop, create, and archive new tables
              */

              /* START WITH THE HEADER THEN WORK ON THE IMPLEMENTATION */

              /* Package specification functions and procedure declarations */
              PROCEDURE W(STR VARCHAR2);
              FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP;

END PKG_AUDIT_ACCESS_TABLES;

Open in new window


and the body

CREATE OR REPLACE PACKAGE BODY PKG_AUDIT_ACCESS_TABLES IS

              ----------------------------------------------------------------------------------------------------------------------------
              ----------------------------------------------------------------------------------------------------------------------------

              /* procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
              PROCEDURE W(STR VARCHAR2) IS
              
              BEGIN
              
                            /* setting variable to simpler to use STR variable for outputting */
                            DBMS_OUTPUT.PUT_LINE(STR);
              END;

              ----------------------------------------------------------------------------------------------------------------------------
              ----------------------------------------------------------------------------------------------------------------------------

              FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP IS
              
                            /* variables */
                            L_LOGGED_TIMESTAMP TIMESTAMP;
              
              BEGIN
                            /* query to return MAX timestamp */
                            SELECT MAX(AUDIT_TIMESTAMP)
                            INTO L_LOGGED_TIMESTAMP
                            FROM ACCESS_LOG;
              
                            /* returning MAX timestamp value*/
                            RETURN L_LOGGED_TIMESTAMP;
              END;
              ----------------------------------------------------------------------------------------------------------------------------
              ----------------------------------------------------------------------------------------------------------------------------

BEGIN
              NULL;
END PKG_AUDIT_ACCESS_TABLES;

Open in new window


Now I need to work this query into my package:

SELECT
       TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
       ,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
      ,COUNT(*)   
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
GROUP BY TRUNC(TIMESTAMP, 'DD') 
        ,TO_CHAR(TIMESTAMP, 'HH24')
ORDER BY 1 , 2;

Open in new window

0
 
Mark_CoAuthor Commented:
thanks
0
 
Mark_CoAuthor Commented:
Silly question but perhaps I've been staring too long and you'll see it right away:


CREATE OR REPLACE PROCEDURE CALLING_ACCESS_LOG IS

BEGIN
              INSERT INTO ACCESS_LOG
                            (SELECT TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
                                   ,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
                                   ,COUNT(*)
                             FROM SYS.DBA_AUDIT_TRAIL A
                             WHERE A.ACTION_NAME = 'LOGON'
                                   AND RETURN_MAX_AUDIT_TIME  > A.TIMESTAMP
                             GROUP BY TRUNC(TIMESTAMP, 'DD')
                                     ,TO_CHAR(TIMESTAMP, 'HH24')
                            ORDER BY 1
                                     ,2);
END CALLING_ACCESS_LOG;

Open in new window

Getting errors form my order by... it's saying I am missing right parenthesis but I have put parenthesis and removed them... thoughts?
0
 
slightwv (䄆 Netminder) Commented:
>>but I have put parenthesis and removed them... thoughts?

I don't believe you can have an order by in a sub-select.
0
 
Mark_CoAuthor Commented:
Silly me, got it now


CREATE OR REPLACE PROCEDURE CALLING_ACCESS_LOG IS

BEGIN
              INSERT INTO ACCESS_LOG
                            (SELECT TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
                                   ,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
                                   ,COUNT(*)
                                   ,A.TIMESTAMP
                             FROM SYS.DBA_AUDIT_TRAIL A
                             WHERE A.ACTION_NAME = 'LOGON'
                                   AND RETURN_MAX_AUDIT_TIME  > A.TIMESTAMP
                             GROUP BY TRUNC(TIMESTAMP, 'DD')
                                     ,TO_CHAR(TIMESTAMP, 'HH24') );
END CALLING_ACCESS_LOG;

Open in new window

0
 
Mark_CoAuthor Commented:
Thought I was done but nothing is logging to my table ACCESS_LOG. Here is my spec and body:


CREATE OR REPLACE PACKAGE PKG_AUDIT_ACCESS_TABLES IS

              /*
               Original Author: Me
               Created Date: 23-Aug-2012
               Purpose: For storing procedures to drop, create, and archive new tables
              */

              /* START WITH THE HEADER THEN WORK ON THE IMPLEMENTATION */

              /* Package specification functions and procedure declarations */
              PROCEDURE W(STR VARCHAR2);
              FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP;
              PROCEDURE CALLING_ACCESS_LOG;

END PKG_AUDIT_ACCESS_TABLES;

Open in new window


CREATE OR REPLACE PACKAGE BODY PKG_AUDIT_ACCESS_TABLES IS

              ----------------------------------------------------------------------------------------------------------------------------
              ----------------------------------------------------------------------------------------------------------------------------

              /* procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
              PROCEDURE W(STR VARCHAR2) IS
              
              BEGIN
              
                            /* setting variable to simpler to use STR variable for outputting */
                            DBMS_OUTPUT.PUT_LINE(STR);
              END;

              ----------------------------------------------------------------------------------------------------------------------------
              ----------------------------------------------------------------------------------------------------------------------------

              /* function returns the MAX timestamp from table ACCES_LOG */
              FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP IS
              
                            /* variables */
                            L_LOGGED_TIMESTAMP TIMESTAMP;
              
              BEGIN
                            /* query to return MAX timestamp */
                            SELECT MAX(AUDIT_TIMESTAMP)
                            INTO L_LOGGED_TIMESTAMP
                            FROM ACCESS_LOG;
              
                            /* returning MAX timestamp value*/
                            RETURN L_LOGGED_TIMESTAMP;
                            COMMIT;
              END;
              ----------------------------------------------------------------------------------------------------------------------------
              ----------------------------------------------------------------------------------------------------------------------------

              /* procedure inserts into log table the current values when called */
              PROCEDURE CALLING_ACCESS_LOG IS
              
              BEGIN
                            INSERT INTO ACCESS_LOG
                                          (SELECT TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
                                                 ,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
                                                 ,COUNT(*)
                                                 ,A.TIMESTAMP
                                           FROM SYS.DBA_AUDIT_TRAIL A
                                           WHERE A.ACTION_NAME = 'LOGON'
                                                 AND RETURN_MAX_AUDIT_TIME > A.TIMESTAMP
                                           GROUP BY TRUNC(TIMESTAMP, 'DD')
                                                   ,TO_CHAR(TIMESTAMP, 'HH24'));
                                                   COMMIT;
              END CALLING_ACCESS_LOG;
              
              ----------------------------------------------------------------------------------------------------------------------------
              ----------------------------------------------------------------------------------------------------------------------------

BEGIN
              NULL;
END PKG_AUDIT_ACCESS_TABLES;

Open in new window


and my test window:


begin
 myschema.pkg_audit_access_tables.CALLING_ACCESS_LOG;
  
end;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
If the audit_log table is empty your MAX function will return null.

null can never be > anything so there is nothing to insert.
0
 
Mark_CoAuthor Commented:
1	8/1/2012	0	1	24-AUG-12 02.59.52.291884 PM
2	8/2/2012	1	2	24-AUG-12 03.00.17.372953 PM

Open in new window



are currently in my ACCESS_LOG table though

btw, here is an updated body, adjusted for scoping.

CREATE OR REPLACE PACKAGE BODY PKG_AUDIT_ACCESS_TABLES IS

              ----------------------------------------------------------------------------------------------------------------------------
              ----------------------------------------------------------------------------------------------------------------------------

              /* procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
              PROCEDURE W(STR VARCHAR2) IS
              
              BEGIN
              
                            /* setting variable to simpler to use STR variable for outputting */
                            DBMS_OUTPUT.PUT_LINE(STR);
              END;

              ----------------------------------------------------------------------------------------------------------------------------
              ----------------------------------------------------------------------------------------------------------------------------

              /* procedure inserts into log table the current values when called. Placing it here according to recent scoping standards */
              PROCEDURE CALLING_ACCESS_LOG IS
              
                            /* variables */
                            L_MAX_AUDIT_TIME VARCHAR2(4000);
              
                            /* function returns the MAX timestamp from table ACCES_LOG */
                            FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP IS
                            
                                          /* variables */
                                          L_LOGGED_TIMESTAMP TIMESTAMP;
                            
                            BEGIN
                                          /* query to return MAX timestamp */
                                          SELECT MAX(AUDIT_TIMESTAMP)
                                          INTO L_LOGGED_TIMESTAMP
                                          FROM ACCESS_LOG;
                            
                                          W('from the return max function');
                                          /* returning MAX timestamp value*/
                                          RETURN L_LOGGED_TIMESTAMP;
                            END;
              
              BEGIN
                            L_MAX_AUDIT_TIME := RETURN_MAX_AUDIT_TIME;
                            INSERT INTO ACCESS_LOG
                                          (SELECT TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
                                                 ,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
                                                 ,COUNT(*)
                                                 ,SYSTIMESTAMP
                                           FROM SYS.DBA_AUDIT_TRAIL A
                                           WHERE A.ACTION_NAME = 'LOGON'
                                                 AND L_MAX_AUDIT_TIME > A.TIMESTAMP
                                           GROUP BY TRUNC(A.TIMESTAMP, 'DD')
                                                   ,TO_CHAR(A.TIMESTAMP, 'HH24'));
              
                            W('from the calling access log proc');
                            COMMIT;
              
              END CALLING_ACCESS_LOG;

----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------

BEGIN
              NULL;
END PKG_AUDIT_ACCESS_TABLES;

Open in new window

0
 
Geert GruwezOracle dbaCommented:
did you consider HH24 for truncating by the hour ?

select trunc(timestamp, 'HH24') x, au.*
from SYS.DBA_AUDIT_TRAIL au
where rownum < 200;

you could have used that returned datetime as 1 column
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now