Portal Usage And Statistics

1. You Finally Have Your Portal Built, but…Are They Coming? Portal Usage And Statistics Presented by: Tim Archer Director of Information and Administrative Services Gettysburg College Portal 2008 Conference

2. 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

3. 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!

4. 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

5. 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

6. 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

7. 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.

8. 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)

9. 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

10. 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.

11. 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.

12. 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>

13. 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.

14. 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

15. 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); EXCEPTION WHEN OTHERS THEN NULL; END;

16. 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

17. 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)

18. 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.

19. 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.

20. Applicants Who Logged Into The Portal Distinct Users Logging In By Entry Term SELECT /*+RULE*/ app.saradap_term_code_entry AS entry_term, COUNT(DISTINCT saradap_pidm) AS num_applicants, COUNT(DISTINCT obj_id) AS num_logins FROM 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_entry ORDER BY app.saradap_term_code_entry

21. Applicants Who Logged Into The Portal Distinct Applicants Logging In By Age Group For Fall Term SELECT /*+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_logins FROM 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_key AND 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)

22. 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_roster FROM activity_log act, sirasgn WHERE sirasgn_term_code = '200820' AND sirasgn_pidm = act.obj_id(+) AND act.event_code(+) = 'CLASROSTER' AND act.objtype(+) = 'SPR' Next step would be to identify which faculty have not used it, and give them some training. SELECT DISTINCT sirasgn_pidm, F_USF_FORMAT_NAME_FOR_PIDM(sirasgn_pidm, 'LFM') AS name FROM activity_log act, sirasgn WHERE sirasgn_term_code = '200820' AND sirasgn_pidm = act.obj_id(+) AND act.event_code(+) = 'CLASROSTER' AND act.objtype(+) = 'SPR' AND act.obj_id IS NULL ORDER BY 2

23. 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