USF's Activity Log Table for Portal Logging

For all of USF's portlets, we have a custom logging table that we insert into every time the portlet is accessed. This table allows us to track who is viewing what and when. The DDL below is for the table to be recreated in an oracle database. Other DB's may require some tweaks. I also attached the SQL to this page along with a presentation we did at Gettysburg College on activity logging, as well as a custom database package used to further expand our logging capabilities to log every screen access and report run from Banner.

Essentially, the obj_id and objtype columns in this table are loose pointers to allow us to relate an activity log entry to arbitrary objects. The objtype is a 3 letter acronym of what the entry is attached to, and the obj_id is the primary key into the table. For purposes of our portlet logging, we use an objtype of "SPR" to represent the spriden banner object, and the spriden_pidm of the user logged in is put into the obj_id column.

Every unique event gets its own event code to group the event. i.e., PAYFAIL - Online payment failed. Or for purposes of our student schedule portlet, PORTSTUSCH is inserted as the event code to track every time that portlet is loaded.

The event note just contains specific details about that individual event. i.e., "Card declined, over limit". You may use this to track the term a schedule is loaded for, or anything else you want to track.


CREATE SEQUENCE USFBANNER.ACT_SEQ
  START WITH 26883747
  MAXVALUE 2000000000
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;


CREATE PUBLIC SYNONYM ACT_SEQ FOR USFBANNER.ACT_SEQ;


GRANT SELECT ON USFBANNER.ACT_SEQ TO PUBLIC;


CREATE TABLE USFBANNER.ACTIVITY_LOG
(
  ACT_ID         NUMBER(15)                     NOT NULL,
  OBJ_ID         NUMBER(15)                     NOT NULL,
  OBJTYPE        VARCHAR2(3 CHAR)               NOT NULL,
  ACT2QUE        NUMBER(15),
  EVENT_CODE     VARCHAR2(50 BYTE)              NOT NULL,
  EVENT_NOTE     VARCHAR2(2000 CHAR),
  EVENT_DATE     TIMESTAMP(0) WITH TIME ZONE    DEFAULT sysdate               NOT NULL,
  EVENT_USER     VARCHAR2(50 CHAR)              DEFAULT user                  NOT NULL,
  BUS_PROC_CD    VARCHAR2(10 CHAR)              DEFAULT 'DEFAULT'             NOT NULL,
  RULE_EVAL_IND  VARCHAR2(1 CHAR)               DEFAULT 'N'                   NOT NULL,
  UPDATE_COUNT   NUMBER(15)                     DEFAULT 0                     NOT NULL,
  ACTIVE_IND     VARCHAR2(1 CHAR)               DEFAULT 'Y'                   NOT NULL,
  ADD_USER       VARCHAR2(50 CHAR)              DEFAULT user                  NOT NULL,
  ADD_DATE       TIMESTAMP(0) WITH TIME ZONE    DEFAULT sysdate               NOT NULL,
  MOD_USER       VARCHAR2(50 CHAR)              DEFAULT user                  NOT NULL,
  MOD_DATE       TIMESTAMP(0) WITH TIME ZONE    DEFAULT sysdate               NOT NULL
)
TABLESPACE DEVELOPMENT
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


CREATE INDEX USFBANNER.ACT_OBJTYPE_ID ON USFBANNER.ACTIVITY_LOG
(OBJTYPE, OBJ_ID)
LOGGING
TABLESPACE DEVELOPMENT
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX USFBANNER.ACT_ACT2QUE ON USFBANNER.ACTIVITY_LOG
(ACT2QUE)
LOGGING
TABLESPACE DEVELOPMENT
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX USFBANNER.ACT_RULE_EVAL_IND ON USFBANNER.ACTIVITY_LOG
(RULE_EVAL_IND)
LOGGING
TABLESPACE DEVELOPMENT
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX USFBANNER.ACT_PK ON USFBANNER.ACTIVITY_LOG
(ACT_ID)
LOGGING
TABLESPACE DEVELOPMENT
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX USFBANNER.ACT_EVENT_CODE_DATE ON USFBANNER.ACTIVITY_LOG
(EVENT_CODE, SYS_EXTRACT_UTC("EVENT_DATE"))
LOGGING
TABLESPACE DEVELOPMENT
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX USFBANNER.ACT_EVENT_DATE_CODE ON USFBANNER.ACTIVITY_LOG
(SYS_EXTRACT_UTC("EVENT_DATE"), EVENT_CODE)
LOGGING
TABLESPACE DEVELOPMENT
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE TRIGGER USFBANNER."BI_ACT_TRIG" BEFORE INSERT
ON USFBANNER.ACTIVITY_LOG REFERENCING OLD AS o NEW AS n FOR EACH ROW
BEGIN
  IF :n.act_ID IS NULL THEN
    select act_seq.nextval
    into :n.act_ID
    from dual;
  END IF;

  IF :n.ADD_USER IS NULL THEN 
    :n.ADD_USER := user;
  END IF;

  IF :n.ADD_DATE IS NULL THEN 
    :n.ADD_DATE := sysdate;
  END IF;

  IF :n.MOD_USER IS NULL THEN 
    :n.MOD_USER := user;
  END IF;

  IF :n.MOD_DATE IS NULL THEN 
    :n.MOD_DATE := sysdate;
  END IF;

  IF :n.UPDATE_COUNT IS NULL THEN
    :n.UPDATE_COUNT := 0;
  END IF;
END;
/


CREATE OR REPLACE TRIGGER USFBANNER."BU_ACT_TRIG" BEFORE UPDATE
ON USFBANNER.ACTIVITY_LOG REFERENCING OLD AS o NEW AS n FOR EACH ROW
BEGIN

  IF :n.MOD_USER IS NULL THEN 
    :n.MOD_USER := user;
  END IF;

  IF :n.MOD_DATE IS NULL THEN 
    :n.MOD_DATE := sysdate;
  END IF;

  :n.update_count := :o.update_count+1;
END;
/


DROP PUBLIC SYNONYM ACTIVITY_LOG;

CREATE PUBLIC SYNONYM ACTIVITY_LOG FOR USFBANNER.ACTIVITY_LOG;


ALTER TABLE USFBANNER.ACTIVITY_LOG ADD (
  CONSTRAINT ACT_PK
 PRIMARY KEY
 (ACT_ID)
    USING INDEX 
    TABLESPACE DEVELOPMENT
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

GRANT INSERT, SELECT ON USFBANNER.ACTIVITY_LOG TO PUBLIC;