Portal Usage And Statistics

Tim ArcherDirector of Information and

Administrative Services

Gettysburg College Portal 2008 Conference

Presented by:

You Finally Have Your Portal Built, but…Are They Coming?

Quick Overview• School Info

• Located in Joliet, IL

• National presence

• 1200 on-campus students

• 2400 degree completion and graduate students

• “Techie Details”• Production with uPortal August 2006

• Running uPortal 2.5.2, CAS, Oracle DB

• Banner 7.x ERP / Oracle DB

• 3 FT Staff, 2 Student Workers

• Novell E-Directory

About This Presentation

• We’ll talk about:• Our portal statistics requirements.• How we solved the need.• How we used the statistics data store to also track usage

of our ERP system.• Show some example reports of how we joined gathered

stats to info in the ERP system to answer business questions.

• Feel free to ask questions throughout!• Lets make this interactive!

The Problem…

We had to quantify the usage of the portal!

• Who is logging in / when?

• Who is using what features of the portal?

• Who triggered major events? For Example:• Payment failure• Submitted early alert• Encountered an application error

• How to tie to ERP system?• Student/employee attributes

What We Had To Work With

• Apache access_log / error_log• 10.0.128.162 - - [14/May/2008:14:41:14 -0500] "GET

/usfportal/secure/general/youHaveMessagesCheck.jsp?displayMessagesLink= HTTP/1.1" 200 601

• Tomcat Logs• INFO [TP-Processor56] payments.PaymentProcessorUtil.[]

May/14 14:28:52 - Done proessing Intellipay Credit Card Payment for ppl_id 21269

We Wanted More!

• Log files didn’t provide all the info we needed.• Userid, UID to person record in ERP, Form POST attributes?

• Log files need to be rotated and pruned• History is then in multiple spots

• Couldn’t easily join to ERP attributes for the user

• They’re great for technical analysis, but…

• Bad for business analysis & decision making

The USF Solution

• Decision made: Every portal account MUST tie back to a record in the ERP system (students, faculty, staff, alumni, etc)

• Log events to a custom table in our ERP system.• Table Name: ACTIVITY_LOG

• Every event worth tracking goes into this table.

• We had this approach before going live with portal.

Show Me The Table!

• OBJ_ID number• OBJTYPE varchar(3)• EVENT_CODE varchar(10)• EVENT_NOTE varchar(2000) • EVENT_DATE date• EVENT_USER varchar(30)• RULE_EVAL_IND varchar(1)

How It Works

• Every event relates to an object through the OBJTYPE and OBJ_ID columns• For events generated by users, we tie the event back to the persons

ERP record• OBJTYPE is SPR for the SPRIDEN table (Banner Person Iden Table)• OBJ_ID is the UID into that table (The SPRIDEN_PIDM)

• Every unique event has its own event code to group the event.• i.e., PAYFAIL – Online payment failed

• The event note contains specific details about that individual event.• i.e., Card declined, over limit

How It Works (continued)

• The Event Date is the timestamp of when the event occurred.• 5/29/2008 2:54:51 PM

• The Event User is the userid (portal userid) of who generated the event.• i.e., tarcher

• Rule Evaluation Indicator is for future use. This is so that events can trigger workflow events. Every event will need to be evaluated as to if it triggers an action and this indicator stores that.

Logging an Event

• Every portlet, JSP page, and even client server applications do inserts into this table to track events.

• Servlets do a simple JDBC insert• Our JSP pages use JSTL, we do a JDBC insert

there• We have a master list of the event codes and their

description in English. (a small table named appl_codes). Keeps the event_code and the event_description.

Sample JSTL Insert• Logging access to a class roster page showing a faculty

member the students enrolled in their class.• ${pidm} is a session variable set to the pidm/UID of the user.

It is resolved upon login. Same with ${userName}

<%-- Log that this page was accessed so we can gather statistics. --%><sql:update> INSERT INTO activity_log (obj_id, objtype, event_code, event_note, event_user,

bus_proc_cd) VALUES(?, ?, ?, ?, ?, ?) <sql:param value="${pidm}" /> <sql:param value="SPR" /> <sql:param value="CLASROSTER" /> <sql:param value="Viewed portal class roster for term ${param.term} and CRN ${param.crn}"

/> <sql:param value="${userName}" /> <sql:param value="USF" /></sql:update>

We Even Updated Banner (Our ERP)!

• Modified Core Security Function to log successful form accesses• Allows us to see who is using what in our ERP

system…• And find who’s getting at things they shouldn’t!

• Modified Banner Job Submission to log all jobs run, and all parameters passed into the job.• We can now tell when the system malfunctions, or the

user malfunctioned! • Event code is the Banner form or job name.

Package USF_ACTIVITY_LOG_UTIL

• Used to simplify the inserts and to contain the logic to log the job parameters

• Helper methods• p_log_banner_job_execution

• p_log_banner_form_execution

• getPidmForUserid

• createActivityLogEntry

USF_ACTIVITY_LOG_UTIL.pks USF_ACTIVITY_LOG_UTIL.pkb

Log Banner Form Execution

• Modify BANSECR.G$_SECURITY_PKG• In Procedure g$_verify_password2_prd

---- Get the role this user is authorized to use with this object.--role_name := g$_get_role_for_object_fnc(p_object, user_id);IF role_name is null THEN …Code Removed for Slide Readability…. RAISE validation_failed;END IF;

BEGIN USF_ACTIVITY_LOG_UTIL.p_log_banner_form_execution(user_id,p_object,role_name);EXCEPTIONWHEN OTHERS THEN NULL;END;

Log Banner Job Execution

• Update gjajobs.shl#

# Submit shl script just created to run in background and exit.

#

sh $H/$TEMP.shl &

# This will log this job into the USF activity log

sqlplus $BANUID/$PSWD <<endofit

exec usf_activity_log_util.p_log_banner_job_execution('$ONE_UP','$BANUID','$6','$PRNTOPT');

exit;

endofit

Other Modifications• Modified the CAS code to record login events.

• Added a database logon/logoff trigger to also record these events.• Extended the example found at

• http://www.dba-oracle.com/art_builder_sec_audit.htm• Added an insert into activity_log component.

• Applications• Datafeeds, integration services, etc also log to this table. Usually just

basic info such as event start/end times. • Used in conjunction with log4j and socket server to track additional

details, notify on errors• We can then use this central log table to tell us if an event ran or not

(exception reports)

Table Admin Issues

• Create several indexes for reporting performance• Index on things like the date, userid, event code, and the objtype/id

• Table Stats• 9/25/06 to 5/14/08:• 7,900,000+ Rows• 1.45 GB

• We don’t plan to prune the table in the near future. We want to be able to easily create reports to show trends.

• Secure the table just like any other database table

• If necessary, use VPD (Oracle) to give administrators access to only see specific event codes. We don’t do this, but in theory…it should work.

Query Examples

• What types of applicants are logging into the portal?

• How many faculty have not used the online class roster?

Examples provided are just to demonstrate the

concept. Many other complex questions are asked in

actuality. We have some queries several pages long.

Applicants Who Logged Into The PortalDistinct Users Logging In By Entry TermSELECT /*+RULE*/ app.saradap_term_code_entry AS entry_term, COUNT(DISTINCT saradap_pidm) AS num_applicants, COUNT(DISTINCT obj_id) AS num_loginsFROM activity_log act, saradap app WHERE app.saradap_pidm = act.obj_id(+) AND act.event_code(+) = 'PORTLLOGIN'AND act.objtype(+) = 'SPR‘AND app.saradap_term_code_entry >= '200910'AND app.saradap_coll_code_1 <> 'TA'AND app.saradap_styp_code <> 'S'GROUP BY app.saradap_term_code_entryORDER BY app.saradap_term_code_entry

Term Apps Logins

200910 1,828 555

200920 146 40

200921 8 6

200930 2 0

201010 39 7

201110 2 0

Applicants Who Logged Into The PortalDistinct Applicants Logging In By Age Group For Fall TermSELECT /*+RULE*/ app.saradap_term_code_entry AS entry_term,

(CASE WHEN per.age < 21 THEN '< 21' WHEN per.age >= 21 AND per.age <= 30 THEN '21-30'

ELSE '31+' END) AS age_group,

COUNT(DISTINCT saradap_pidm) AS num_applicants, COUNT(DISTINCT obj_id) AS num_loginsFROM activity_log act, usfv_general_person per, saradap app WHERE app.saradap_pidm = act.obj_id(+) AND act.event_code(+) = 'PORTLLOGIN'AND act.objtype(+) = 'SPR'AND app.saradap_term_code_entry = '200910'AND app.saradap_pidm = per.pidm_keyAND app.saradap_coll_code_1 <> 'TA'AND app.saradap_styp_code <> 'S'GROUP BY app.saradap_term_code_entry, (CASE WHEN per.age < 21 THEN '< 21' WHEN per.age >= 21 AND per.age <= 30 THEN '21-30'

ELSE '31+' END)

Age Group Apps Logins

< 21 1,052 272

21-30 443 163

31+ 333 120

How Many Faculty Have Not Used The Online Class Roster

SELECT /*+RULE*/ COUNT(DISTINCT sirasgn_pidm) AS num_faculty, COUNT(DISTINCT obj_id) AS num_using_rosterFROM activity_log act, sirasgnWHERE sirasgn_term_code = '200820'AND sirasgn_pidm = act.obj_id(+) AND act.event_code(+) = 'CLASROSTER'AND act.objtype(+) = 'SPR'

Num Faculty Who used Roster

331 314

Next step would be to identify which faculty have not used it, and give them some training.

Who

Staff Member 1

Staff Member 2

Etc…

SELECT DISTINCT sirasgn_pidm, F_USF_FORMAT_NAME_FOR_PIDM(sirasgn_pidm, 'LFM') AS nameFROM activity_log act, sirasgnWHERE sirasgn_term_code = '200820'AND sirasgn_pidm = act.obj_id(+) AND act.event_code(+) = 'CLASROSTER'AND act.objtype(+) = 'SPR'AND act.obj_id IS NULLORDER BY 2

My Recommendations

• Build statistics gathering into your portal from the beginning.

• Build detailed event notes to assist in debugging. • store key parameters for a job or page load so that you

can reproduce it. • i.e., What term was the class list loaded for?

• Centralize your logging as best as possible• Utilize a workflow or other utilities to notify you

on key events, or kick off a process• i.e., email an admin on application error