College Center for Library Automation College Center for Library - PowerPoint PPT Presentation

College Center for Library Automation College Center for Library Automation Tallahassee, FL Tallahassee, FL • Susan B. Campbell Susan B. Campbell • (scampbell@cclaflorida.org scampbell@cclaflorida.org) ) ( • Jim McGill Jim McGill • (jmcgill@cclaflorida.org jmcgill@cclaflorida.org) ) ( March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

automating retrieval and reporting of automating retrieval and reporting of database usage statistics for a consortium database usage statistics for a consortium CCLA provides and maintains the Library Information Network CCLA provides and maintains the Library Information Network • • for 28 Community Colleges (LINCC) for Florida's 65+ for 28 Community Colleges (LINCC) for Florida's 65+ community college libraries. community college libraries. db statistics we’ ’re collecting and reporting re collecting and reporting db statistics we • • 19 vendors 19 vendors • • over 200 databases over 200 databases • • monthly reports by database, campus, statewide monthly reports by database, campus, statewide • • on demand on demand • • customers for monthly reports customers for monthly reports • • 28 community colleges in Florida 28 community colleges in Florida • • internal reports internal reports • • March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

automating retrieval and reporting of automating retrieval and reporting of database usage statistics for a consortium database usage statistics for a consortium • problem • problem problem problem • • what we were doing and why it doesn’ what we were doing and why it doesn’ what we were doing and why it doesn ’t work ’t work t work t work what we were doing and why it doesn • • • • • solution solution • the pieces, the parts and how they fit together the pieces, the parts and how they fit together • • • future future • what we’ ’ve learned and our expectations ve learned and our expectations what we • • March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

the problem the problem • excel excess excel excess • March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

the problem the problem • vendor variety vendor variety • repeat 28 times or more for each vendor (and sometimes each database) March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

automating retrieval and reporting of automating retrieval and reporting of database usage statistics for a consortium database usage statistics for a consortium • problem problem • what we were doing and why it doesn’ ’t work t work what we were doing and why it doesn • • • solution solution • the pieces, the parts and how they fit together the pieces, the parts and how they fit together • • • future future • what we’ ’ve learned and our expectations ve learned and our expectations what we • • March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

the solution the solution • automating automating • • maintenance utilities maintenance utilities • • handling retrieved data handling retrieved data • • reporting in multiple formats reporting in multiple formats • • retrieval of vendor data retrieval of vendor data • March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

intranet web interface intranet web interface March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

t o n r o g d n n i d e n V o p s e r March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

reporting reporting March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

creating retrieval scripts creating retrieval scripts “nuts and bolts nuts and bolts” ” “ March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

(Manual edits for testing & first cleanup – remove everything that isn’t in table. This is iterative and run from the command prompt until satisfactory file is returned.) March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

step 1. capture HTTP headers Process Trace File (ParseHTTPTrace.pl) Generic Web Page retrieval (GetWebPage_VENDOR.pl) This is a manual process to create the Perl script that will accept variables and create GetWebPage_VENDOR.pl March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

step 2. modify Perl script to accept command line variables $Period= $ARGV[0]; YYYYMM - our DB format $ScopeCustID= $ARGV[1]; vendor specific scope $UserName= $ARGV[2]; customer ID $Password= $ARGV[3]; remarks - unremarked # $ScopeCustID= "bcc"; for testing # $Period= "200701"; to reformat standard $yr= substr($Period,0,4); $mon= substr($Period,4,2); YYYYMM format to two separate variables: MM if ($mon < 10) { $mon= ~ s/0//gi;} ; and YYYY for URL Automated Web Page Retrieval (GetWebPage_VENDOR.pl) March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

Step 3. modify script with command line variables and parse runtime variables ... iodFromMonth= ' . $mon . '&timePeriodFromYear= ' . $yr . '&timeP ... $content0= $resp5-> content; $pos= index($content0,"VIEWSTATE")+ 13; $pos2= substr($content0,$pos,5000); SECURITY CODES $pos3= index($pos2,"value")+ 7; $pos4= index($pos2,"\/> "); some codes are $VIEWSTATE= substr($pos2,$pos3,$pos4-$pos3-2); session based & $VIEWSTATE= ~ s/\//\%2F/gi; must be parsed $VIEWSTATE= ~ s/\+ /\%2B/gi; out to pass to $VIEWSTATE= ~ s/\= /\%3D/gi; subsequent pages $pos= index($content0,"EVENTVALIDATION")+ 13; $pos2= substr($content0,$pos,2000); $pos3= index($pos2,"value")+ 7; $pos4= index($pos2,"\/> "); $EVENTVALIDATION= substr($pos2,$pos3,$pos4-$pos3-2); Automated Web Page Retrieval $EVENTVALIDATION= ~ s/\//\%2F/gi; (GetWebPage_VENDOR.pl) $EVENTVALIDATION= ~ s/\+ /\%2B/gi; $EVENTVALIDATION= ~ s/\= /\%3D/gi; March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

step 4. create page parser (part 1) creating ProcessVendor.pl script $col= $ARGV[0]; college name – when needed $vendor= “vendorname"; anonymized ( for this presentation ) vendor name $VDBSuffix= “VENDOR"; $jumpin= "< b> Site:"; points to begin and stop processing file $jumpout= "Grand Total"; require ("../VDBProcs.pl"); include file with needed subroutines Parse Web Page Information (ProcessVENDOR.pl) March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries

Step 4. create page parser (part 2) After processing, each table row is on one line with all carriage returns, linefeeds, and tabs removed. Blank lines and page feeds are not output, code outside jump* is ignored. Period, college name and other variables are passed from the database by the VDBProc.pl file. Validation is run on SQL log file to look for error messages and write to log. Entries are made for no data, change from previously retrieved period value or other potential problems. Parse Web Page Information (ProcessVENDOR.pl) procedures called from common include file March 20, 2008 Electronic Resources and Libraries March 20, 2008 Electronic Resources and Libraries