# DCeppm.ctl:100:Collects Primavera Enterprise Project Portfolio Management Info # $Id: DCeppm.ctl,v 1.1 2015/07/31 14:58:38 RDA Exp $ # ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/PGBU/DCeppm.ctl,v 1.1 2015/07/31 14:58:38 RDA Exp $ # # Change History # 20150727 KRA Initial version. =head1 NAME PGBU:DCeppm - Collects Primavera Enterprise Project Portfolio Management Information =head1 DESCRIPTION This module collects Primavera Enterprise Project Portfolio Management-related information. The module covers Primavera Enterprise Project Portfolio Management P6 and later. The following reports can be generated and are regrouped under C: =cut echo tput('bold'),'Processing PGBU.EPPM module ...',tput('off') # Initialization var $DOMAIN = ${D_DOMAIN_HOME:''} var $HOME = ${D_HOME:''} var $ORACLE_HOME = ${SET.RDA.BEGIN.D_ORACLE_HOME:''} var $TAIL = ${DFT.N_TAIL:1000} var $TOC = '%TOC%' var $TOP = '[[#Top][Back to top]]' pretoc '1:Enterprise Project Portfolio Management' # Set the symbols call setSymbol('$PV_HOME',$HOME) # Load the common macros run DB:DBinfo() run RDA:library() =head2 abbr - Abbreviations Displays the RDA abbreviations defined for the Primavera Enterprise Project Portfolio Management home collection. =cut debug ' Inside EPPM module, collecting defined abbreviations' report abbr prefix {write \ '---+ Primavera Enterprise Project Portfolio Management Home Abbreviations' write '|*Abbreviation*|*Location*|' } var %hsh = getSymbols() loop $key (keys(%hsh)) write '|',$key,' |',$hsh{$key},' |' if isCreated(true) toc '2:[[',getFile(),'][rda_report][Abbreviations]]' =head1 PRIMAVERA DATABASE COLLECTIONS =head2 rep_info - Repository Information Collects the repository information from the Enterprise Project Portfolio Management database. =cut if ${I_DB} {var $tgt = last # Set the database context call setDbTarget({B_SYSDBA=>$tgt->get_first('B_SYSDBA'),\ F_JAR=>[catFile($HOME,'p6','lib','ojdbc6.jar'),\ catFile($HOME,'p6','lib','sqljdbc.jar')],\ T_SOURCE=>$tgt->get_first('T_SOURCE'),\ T_TYPE=>'JDBC',\ T_USER=>$tgt->get_first('T_USER')}) # Collect the repository information debug ' Inside EPPM module, gathering the repository information' report rep_info if testDb() {echo '' echo tput('bold'),\ 'The schema containing EPPM repository is not accessible.',tput('off') if getDbMessage() {echo last write '---+ Primavera Enterprise Project Portfolio Management Repository \ Information' write 'Database not accessible (',getDbMessage(),')' toc '2:[[',getFile(),'][rda_report][Repository Information]]' } echo '' } else {var $TTL = '---+!! Primavera Enterprise Project Portfolio Management \ Repository Information' var @TTL = ('',\ '---+ Database Version Information',\ '---+ SGA Information',\ '---+ Session Memory Usage Information',\ '---+ P6 Database Version Information',\ '---+ P6 Settings Information',\ '---+ Number of Rows in Refrdel Table',\ '---+ Select the oldest entry in Refrdel Table',\ '---+ Number of Rows in Prmqueue Table',\ '---+ Select the oldest entry in Prmqueue Table',\ '---+ Number of Rows in Prmaudit Table',\ '---+ Tables enabled for Partitioning',\ '---+ Number of Rows in Projwbs Table',\ '---+ Number of Rows in Project Table',\ '---+ Number of Rows in Udfvalue Table',\ '---+ Statistics Information',\ '---+ Index Fragmentation Information') var @HDR = ('',\ '|*Banner*|',\ '',\ '|*Session*|*Pid Thread*|*Current Size MiB*|*Maximum Size MiB*|',\ '|*Version*|',\ '',\ '| *Count*|',\ '|*Date*|',\ '| *Count*|',\ '|*Date*|',\ '| *Count*|',\ '',\ '| *Count*|',\ '| *Count*|',\ '| *Count*|',\ '|*Table Name*|*Last Analyzed*|*No Rows*|*Sample Size*|',\ '|*Name*|*No Rows*|*Distinct*|*Leaf Blocks*|*Clustering Factor*|\ *Level*|*Avg Leaf Blocks Per Key*|') var ($HDR[2],$col2) = getDbColumns('RDA','V$SGAINFO') call clearDbColumns('RDA') var ($HDR[5],$col5) = getDbColumns('RDA','SETTINGS') call clearDbColumns('RDA') var ($HDR[11],$col11) = getDbColumns('RDA','USER_PART_TABLES') call clearDbColumns('RDA') set $sql {# SQL1 "SELECT '|' || v.banner || '|' " FROM v$version v "/ } if $HDR[2] {append $sql {# MACRO separator(2) "# SQL2 "SELECT :1 " FROM v$sgainfo "/ } } append $sql {# MACRO separator(3) "# SQL3 "SELECT '|' || " TO_CHAR(ssn.sid,'9999') || ' - ' || " NVL(ssn.username,NVL(bgp.name,'background')) || " NVL(LOWER(ssn.machine),ins.host_name) || ' |' || " TO_CHAR(prc.spid,'999999999') || ' |' || " TO_CHAR((se1.value/1024)/1024,'999g999g990d00') || ' |' || " TO_CHAR((se2.value/1024)/1024,'999g999g990d00') || ' |' " FROM v$statname stat1,v$statname stat2,v$session ssn,v$sesstat se1, " v$sesstat se2,v$bgprocess bgp,v$process prc,v$instance ins " WHERE stat1.name = 'session pga memory' " AND stat2.name = 'session pga memory max' " AND se1.sid = ssn.sid " AND se2.sid = ssn.sid " AND se2.statistic# = stat2.statistic# " AND se1.statistic# = stat1.statistic# " AND ssn.paddr = bgp.paddr(+) " AND ssn.paddr = prc.addr(+) "/ "# MACRO separator(4) "# SQL4 "SELECT '|' || database_version || ' |' " FROM prefer "/ } if $HDR[5] {append $sql {# MACRO separator(5) "# SQL5 "SELECT :2 " FROM settings " ORDER BY namespace "/ } } append $sql {# MACRO separator(6) "# SQL6 "SELECT '| ' || COUNT(*) || '|' " FROM refrdel "/ "# MACRO separator(7) "# SQL7 "SELECT '|' || MIN(delete_date) || ' |' " FROM refrdel "/ "# MACRO separator(8) "# SQL8 "SELECT '| ' || COUNT(*) || '|' " FROM prmqueue "/ "# MACRO separator(9) "# SQL9 "SELECT '|' || MIN(dequeue_date) || ' |' " FROM prmqueue "/ "# MACRO separator(10) "# SQL10 "SELECT '| ' || COUNT(*) || '|' " FROM prmaudit "/ } if $HDR[11] {append $sql {# MACRO separator(11) "# SQL11 "SELECT :3 " FROM user_part_tables "/ } } append $sql {# MACRO separator(12) "# SQL12 "SELECT '| ' || COUNT(*) || '|' " FROM projwbs "/ "# MACRO separator(13) "# SQL13 "SELECT '| ' || " COUNT(*) || '|' " FROM project "/ "# MACRO separator(14) "# SQL14 "SELECT '| ' || " COUNT(*) || '|' " FROM udfvalue "/ "# MACRO separator(15) "# SQL15 "SELECT '|' || " table_name || ' |' || " TO_CHAR(last_analyzed,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " num_rows || ' |' || " sample_size || ' |' " FROM dba_tables " WHERE owner = (SELECT user FROM dual) " ORDER BY last_analyzed "/ "# MACRO separator(16) "# SQL16 "SELECT '|' || " index_name || ' |' || " num_rows || ' |' || " distinct_keys || ' |' || " leaf_blocks || ' |' || " clustering_factor || ' |' || " blevel || ' |' || " avg_leaf_blocks_per_key || ' |' " FROM dba_indexes " WHERE owner = (SELECT user FROM dual) " ORDER BY num_rows DESC "/ } call separator(1) call writeDb(bindDb($sql,$col2,$col5,$col11)) call separator(0,'Repository Information') =head2 chk_del_records - Check Deleted Records Checks for deleted records related to Enterprise Project Portfolio Management from the Database. =cut debug ' Inside EPPM module, checks for deleted records' report chk_del_records var ($cnt,$sql,@HDR,@TTL) = (0) var $TTL = '---+!! Check Deleted Records' loop $tbl (\ 'ACCOUNT',\ 'ACTIVITYCODEHIERARCHY',\ 'ACTIVITYCODETYPESECURITY',\ 'ACTIVITYSPREAD',\ 'ACTVCODE',\ 'ACTVCODEX',\ 'ACTVTYPE',\ 'ADDTASKPROC',\ 'ADDTASKPROCX',\ 'ADMIN_CONFIG',\ 'ALERT',\ 'ALERTPARAM',\ 'ATTR_MASTER',\ 'BASETYPE',\ 'BGPLOG',\ 'BRE_REGISTRY',\ 'BUDGCHNG',\ 'BUDGCHNGX',\ 'CALENDAR',\ 'CALENDARX',\ 'CHGHEAD',\ 'CHGTASK',\ 'CHGTASKACTV',\ 'CHGTASKACTVX',\ 'CHGTASKPROC',\ 'CHGTASKPROCX',\ 'CHGTASKX',\ 'CHGTRSRC',\ 'CHGTRSRCX',\ 'CHGUDFVALUE',\ 'CHGUDFVALUEX',\ 'COSTACCOUNTHIERARCHY',\ 'COSTSECURITY',\ 'COSTTYPE',\ 'CURRTYPE',\ 'CURRTYPEX',\ 'DASHBOARD',\ 'DASHUSER',\ 'DELTASKPROC',\ 'DELTASKPROCX',\ 'DISCUSSION',\ 'DISCUSSION_READ',\ 'DLTACCT',\ 'DLTACTV',\ 'DLTOBS',\ 'DLTROLE',\ 'DLTRSRC',\ 'DLTRSRL',\ 'DLTUSER',\ 'DM_ATTR',\ 'DOC',\ 'DOCCATG',\ 'DOCREVIEW',\ 'DOCREVIEWTASK',\ 'DOCSTAT',\ 'DOCUMENT',\ 'DOCUMENTX',\ 'DOC_FOLDER',\ 'DOC_RECENT',\ 'DOC_VERSION',\ 'EPSHIERARCHY',\ 'EPSSPREAD',\ 'EXPPROJ',\ 'EXTAPP',\ 'FACTOR',\ 'FACTVAL',\ 'FILTPROP',\ 'FINDATES',\ 'FORMCATG',\ 'FORMPROJ',\ 'FORMTMPL',\ 'FUNDSRC',\ 'GCHANGE',\ 'GLOBALSECURITY',\ 'HQDATA',\ 'HQUERY',\ 'ISSUHIST',\ 'ITERATION',\ 'ITERTASK',\ 'ITERTEAM',\ 'JOBLOG',\ 'JOBRPT',\ 'JOBSET',\ 'JOBSVC',\ 'LOCATION',\ 'MEMOTYPE',\ 'MTXSCRTYP',\ 'NEXTKEY',\ 'NONWORK',\ 'NOTE',\ 'OBS',\ 'OBSPROJ',\ 'PCATTYPE',\ 'PCATUSER',\ 'PCATUSERX',\ 'PCATVAL',\ 'PCATVALX',\ 'PC_KEY_XREF',\ 'PC_PROCESS_STAT',\ 'PFOLIO',\ 'PFOLIOX',\ 'PHASE',\ 'PKXREF',\ 'PLPROJREF',\ 'POBS',\ 'PREFER',\ 'PRMAUDIT',\ 'PRMQUEUE',\ 'PROCGROUP',\ 'PROCITEM',\ 'PROFILE',\ 'PROFPRIV',\ 'PROFPRIVX',\ 'PROJCOST',\ 'PROJCOSTX',\ 'PROJECT',\ 'PROJECTCODEHIERARCHY',\ 'PROJECTSECURITY',\ 'PROJECTSPREAD',\ 'PROJECTX',\ 'PROJEST',\ 'PROJFUND',\ 'PROJFUNDX',\ 'PROJISSU',\ 'PROJISSUX',\ 'PROJPCAT',\ 'PROJPCATX',\ 'PROJPROP',\ 'PROJRISK',\ 'PROJSET',\ 'PROJSHAR',\ 'PROJTHRS',\ 'PROJWBS',\ 'PROJWBSX',\ 'PROJWSRPT',\ 'PRPFOLIO',\ 'PUBUSER',\ 'QUERYLIB',\ 'RCATTYPE',\ 'RCATVAL',\ 'RCATVALX',\ 'REFRDEL',\ 'REITTYPE',\ 'RELEASE',\ 'RELITEMS',\ 'RELITER',\ 'RELPROJ',\ 'REPORTDATE',\ 'REPORTTIME',\ 'RESOURCEASSIGNMENTSPREAD',\ 'RESOURCECODEHIERARCHY',\ 'RESOURCEHIERARCHY',\ 'RESOURCELIMIT',\ 'RFOLIO',\ 'RFOLIOX',\ 'RISK',\ 'RISKCTRL',\ 'RISKIMPACT',\ 'RISKMIT',\ 'RISKMITIMP',\ 'RISKMITX',\ 'RISKMTXSCR',\ 'RISKRSPPLN',\ 'RISKRSPPLNX',\ 'RISKSCRMTX',\ 'RISKSCRTHR',\ 'RISKSCRTYP',\ 'RISKTYPE',\ 'RISKX',\ 'RLFOLIO',\ 'RLFOLIOX',\ 'ROLELIMIT',\ 'ROLERATE',\ 'ROLERATEX',\ 'ROLES',\ 'ROLFOLIO',\ 'RPT',\ 'RPTBATCH',\ 'RPTGROUP',\ 'RPTLIST',\ 'RSRC',\ 'RSRCANDASH',\ 'RSRCANVIEW',\ 'RSRCCURV',\ 'RSRCCURVX',\ 'RSRCHOUR',\ 'RSRCHOURX',\ 'RSRCLOC',\ 'RSRCPROP',\ 'RSRCRATE',\ 'RSRCRATEX',\ 'RSRCRCAT',\ 'RSRCRCATX',\ 'RSRCROLE',\ 'RSRCROLEX',\ 'RSRCSEC',\ 'RSRCSECX',\ 'RSRCX',\ 'RSRFOLIO',\ 'SCENARIO',\ 'SCENPROJ',\ 'SCENROLE',\ 'SCENUSER',\ 'SETTINGS',\ 'SHIFT',\ 'SHIFTPER',\ 'SNAPGROUP',\ 'SNAPPROJ',\ 'SNAPSHOT',\ 'SPIDMAP',\ 'SUMPROJCOST',\ 'SUMTASK',\ 'SUMTASKSPREAD',\ 'SUMTRSRC',\ 'TASK',\ 'TASKACTV',\ 'TASKACTVX',\ 'TASKDOC',\ 'TASKDOCX',\ 'TASKFDBK',\ 'TASKFIN',\ 'TASKFINX',\ 'TASKMEMO',\ 'TASKMEMOX',\ 'TASKNOTE',\ 'TASKPRED',\ 'TASKPREDX',\ 'TASKPROC',\ 'TASKPROCX',\ 'TASKRISK',\ 'TASKRISKX',\ 'TASKRSRC',\ 'TASKRSRCX',\ 'TASKSUM',\ 'TASKSUMFIN',\ 'TASKUSER',\ 'TASKWKSP',\ 'TASKX',\ 'TEMPKEYS',\ 'THRSPARM',\ 'TIMESHT',\ 'TIMESHTX',\ 'TMPLCATG',\ 'TPROJMAP',\ 'TRAKVIEW',\ 'TRSRCFIN',\ 'TRSRCFINX',\ 'TRSRCSUM',\ 'TRSRCSUMFN',\ 'TSAUDIT',\ 'TSDATES',\ 'TSDELEGATE',\ 'UACCESS',\ 'UDFCODE',\ 'UDFCODEX',\ 'UDFTYPE',\ 'UDFVALUE',\ 'UDFVALUEX',\ 'UEVNTREG',\ 'UMEASURE',\ 'UPKLIST',\ 'USERCOL',\ 'USERDATA',\ 'USERENG',\ 'USEROBS',\ 'USEROBSX',\ 'USEROPEN',\ 'USERS',\ 'USERSET',\ 'USERSX',\ 'USERWKSP',\ 'USESSAUD',\ 'USESSION',\ 'USROPNVAL',\ 'VIEWPREF',\ 'VIEWPROP',\ 'VWPREFDASH',\ 'VWPREFDATA',\ 'VWPREFUSER',\ 'WBRSCAT',\ 'WBSBUDG',\ 'WBSBUDGX',\ 'WBSHIERARCHY',\ 'WBSMEMO',\ 'WBSMEMOX',\ 'WBSRSRC',\ 'WBSRSRCX',\ 'WBSRSRC_QTY',\ 'WBSRSRC_QTYX',\ 'WBSSPREAD',\ 'WBSSTEP',\ 'WBSSTEPX',\ 'WKFLTMPL',\ 'WKFLUSER',\ 'WORKFLOW',\ 'WORKSPACE',\ 'WRK_LOG_RESULTS') {var $TTL[incr($cnt)] = concat('---+ Number of Rows in ',$tbl) if getDbColumns('RDA',$tbl) {var ($HDR[$cnt],$col) = getDbView({},\ {fct=>'NUM',val=>'COUNT(1)',hdr=>' *Count*'}) set $str {# MACRO separator(:1) "# SQL:1 "SELECT :2 " FROM :3 "/ } var $sql = join("\012",$sql,bindDb($str,$cnt,$col,$tbl)) } call clearDbColumns('RDA') } call writeDb($sql) call separator(0,'Check Deleted Records') } } =head1 PRIMAVERA CLIENT COLLECTIONS =head2 Configuration Files Collects Primavera Enterprise Project Portfolio Management-related client configuration files. =cut if or(isCygwin(),isWindows()) {debug ' Inside EPPM module, collecting the client configuration files' pretoc '2:Client Information' pretoc '3:Configuration Files' var $TMP = cond(isCygwin(),${ENV.temp:catDir(${ENV.LOCALAPPDATA},'Temp')},\ ${ENV.TEMP}) call sort_files(4,0,\ catFile(${ENV.ProgramFiles(x86)},'Oracle','Primavera P6',\ 'P6 Professional','PM.ini'),\ catFile(${ENV.LOCALAPPDATA},'Oracle','Primavera P6',\ 'P6 Professional','BREBootStrap.xml'),\ catFile(${ENV.LOCALAPPDATA},'Oracle','Primavera P6',\ 'P6 Professional','PrmBootStrapV2.xml'),\ catFile(${ENV.ALLUSERSPROFILE},'Oracle','Primavera P6',\ 'P6 Professional','PrmBootStrapV2.xml')) unpretoc =head2 Log Files Collects Primavera Enterprise Project Portfolio Management-related client log files. =cut debug ' Inside EPPM module, collecting the client log files' pretoc '3:Log Files' call sort_files(4,$TAIL,\ catFile($ORACLE_HOME,'config','log','CloneRmanRestore.log'),\ catFile($ORACLE_HOME,'config','log','cloneDBCreation.log'),\ catFile($ORACLE_HOME,'config','log','cloneDBCreation.log'),\ catFile($ORACLE_HOME,'config','log','postDBCreation.log'),\ catFile($TMP,'PrimaveraVisualizerCmd.log'),\ catFile($TMP,'Visualizer.log'),\ catFile($TMP,'PrmImportLog.txt'),\ catFile($TMP,'prm_xlsimport.log'),\ grepDir(catDir(${ENV.ProgramFiles(x86)},'Oracle','Primavera P6',\ 'P6 Professional','EPPM','Java','logs'),'^\.+$','drv'),\ grepDir(catDir(${ENV.ProgramFiles(x86)},'Oracle','Primavera P6',\ 'P6 Professional','PPM','Java','logs'),'^\.+$','drv'),\ grepDir(catDir(${ENV.LOCALAPPDATA},'Oracle','Primavera P6',\ 'P6 Professional','logs'),'^\.+$','drv')) unpretoc 2 } =head1 PRIMAVERA HOME COLLECTIONS =head2 Configuration Files Collects Primavera Enterprise Project Portfolio Management-related configuration files. =cut debug ' Inside EPPM module, collecting the configuration files' pretoc '2:Configuration Files' call sort_files(3,0,\ catFile($HOME,'api','BREBootStrap.xml'),\ catFile($HOME,'p6','BREBootStrap.xml'),\ catFile($HOME,'p6procloudconnect','BREBootStrap.xml'),\ catFile($HOME,'tmws','BREBootStrap.xml'),\ catFile($HOME,'ws','BREBootStrap.xml')) unpretoc =head2 Log Files Collects Primavera Enterprise Project Portfolio Management-related log files. =cut debug ' Inside EPPM module, collecting the log files' pretoc '2:Log Files' pretoc '3:Web Access Logs' loop $row (\ ['api',catFile($HOME,'api','WebAccessLogs','P6WebAccess.html')],\ ['p6',catFile($HOME,'p6','WebAccessLogs','P6WebAccess.html')],\ ['p6procloudconnect',\ catFile($HOME,'p6procloudconnect','WebAccessLogs','P6WebAccess.html')],\ ['tmws',catFile($HOME,'tmws','WebAccessLogs','P6WebAccess.html')],\ ['ws',catFile($HOME,'ws','WebAccessLogs','P6WebAccess.html')]) {var ($nam,$fil) = @{$row} if ?testFile('f',$fil) {var $htm = htmlLoadFile(last,htmlDisable(htmlParser(),'DR')) report concat($nam,'_',basename($fil)) prefix {write '---+ Oracle Primavera P6' write '---## Information Extracted from ',encode($fil) call statFile('b',$fil) write '|*Time*|*Thread*|*Level*|*Context*|*Category*|*File:Line*|*Message*|' } loop $lin (htmlTable($htm)) {next match($lin,'^\-{3}') write $lin } if isCreated(true) {write $TOP toc '4:[[',getFile(),'][rda_report][',encode($nam),']]' } } } unpretoc call cat_report(catDir($HOME,'pr'),'primavera-logging.log') unpretoc =head1 ORACLE WEBLOGIC SERVER DOMAIN COLLECTIONS It includes all reports produced by the L module for the specified Oracle WebLogic Server domain. =cut toc '%PUSH("%SPLIT%")%' toc '%PUSH("1+:Oracle WebLogic Server Overview")%' toc '%INCLUDE("OFM_WREQ_PGBU_EPPM_WH_TF.toc")%' toc '%POP2%' toc '%PUSH("%SPLIT%")%' toc '%PUSH("1+:Oracle WebLogic Server Domain")%' toc '%INCLUDE("OFM_WREQ_PGBU_EPPM_DOM_TF.toc")%' toc '%POP2%' unpretoc =head1 SEE ALSO L, L, L, L =begin credits =over 10 =item RDA 8.09: Mihai Ciocan. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved. =head1 TRADEMARK NOTICE Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. =cut