Using The Database Schema, 10.1 | HP System Reporter Media Kit

System Reporter 2.8

3PAR System Reporter User’s Guide

10

Database Schema

This chapter documents the 3PAR System Reporter database schema.

10.1 Using The Database Schema

The description of the database schema is provided as a convenience to those wishing to query the database directly instead of using the web queries described in Chapter 8, Web Queries. However, if you wish to write your own applications that directly query the database, it is important to note the following caution:

CAUTION: The database schema may change between releases of the System Reporter and users must be prepared to revise their applications accordingly.

3PAR does not provide direct assistance in generating SQL queries and only users who are confident of their own database expertise to use the schema description in this chapter should attempt to use it to create applications.

Users may only issue queries that read the database, not alter it.

Database Schema

10.1

Image 293

HP System Reporter Media Kit, System Reporter E-Media Kit manual Using The Database Schema, 10.1

Contents

3PAR System Reporter 2.8 User’s Guide Revision Notice Oractcl Copyright and License Page Page Page Preamble Page Page No Warranty Table of Contents Xii Installation, Configuration, Update, and Removal Removing the System Reporter Components Xiii Creating Custom Reports Xiv Working with Excel Charts Interpreting Report Metrics Xvi Web Queries Xvii Xviii Performance Reports Xix Using Adaptive Optimization Xxi Database Schema Xxii Troubleshooting Xxiii Xxiv User Interfaces Audience For information about… Read the… Related Documentation Organization Typeface Meaning Example ABCDabcd Typographical ConventionsAdvisories About 3PAR System Reporter Overview and Features Configuring System Reporter Policy Settings Choosing a Method for Accessing Reports Report Name Description Report Metrics Cross Reference Creating Customized Reports Node Cache Performance Formatting Reports for Presentation Generating Email Alerts Accessing Report Data using Web QueriesCustomizing the Reporting Format Analyzing and Optimizing Storage Utilization Using the Database Schema to Build Custom Reports Before You Begin Installation, Configuration, Update, and Removal System Reporter CD Contents Setup.exe Installer.exeApache2.2.14 Win32-x86-openssl System Component Minimum Requirement System Requirements InServ Storage Server System Requirements Using the System Reporter Sizing Spreadsheet System Reporter Sizing Spreadsheet Using the System Reporter Sizing Spreadsheet InServ Storage Server and User Interface Components System Reporter Components System Reporter Components Installing the System Reporter Components Installing the System Reporter Components Section Installing the Web Server SQLite Installing the Database ServerChoosing the Appropriate Database Oracle MySQL Comment SQLite MySQL Oracle Installing and Setting Up the MySQL Database Optional Installing MySQL Server Select Multifunctional Database then click Next Configuring the MySQL Database Maxallowedpacket=32M Setting the maxallowedpacket parameter Mysql -u root -p Creating MySQL Users and Schema Creating an Oracle database Setting Up the Oracle Database OptionalStart the Oracle Database Configuration Assistant Creating the Oracle Users for System Reporter Creating the Oracle Net Service 3PAR System Reporter Installer window opens Installing or Upgrading the System Reporter ToolsInstalling or Upgrading System Reporter Tools on Windows Installing or Upgrading the System Reporter Tools Installing or Upgrading the System Reporter Tools Installing or Upgrading the System Reporter Tools Installing System Reporter Tools on Linux Rpm --install sampleloop-2.8-1.i386.rpm Etc/init.d/sampleloop start 3PAR System Reporter window appears Adding InServ Systems Click Submit Query Setting File Permissions OptionalVerifying Installation Installing the Excel Client Optional About Apache Http Server Authorization and Access Control Directory cgi-bin/3par-rpts AuthType Basic Removing the System Reporter Tools on Windows Removing the System Reporter Components Removing the MySQL Database Optional Removing the System Reporter Tools on Linux Drop user cliuser drop user webuser Getting Started Accessing the Main System Reporter Interface Connection Dialog Box System Reporter Main Window System Reporter Main Menu Window Getting Help Filtered Multi-Selection Control Using the Object Selection ControlsSimple Selection List Starting the System Reporter Excel Client Accessing the Optional Excel Client Setting the Security for Excel Setting the Security Level for Excel Security Warning for Enabling Macros in Excell Setting the Security Level Macro Security Alert for Excel Setting the Security Level for Excel Trust Center Screen Click Trust Center Click Trust Center Settings Connecting to the Web Server from the Excel Client Connection Parameters dialog box appears Figure Changing the Excel Client Server and Resetting the Workbook Changing the Web Server Accessing the Excel Client Query LogResetting the Workbook Deleting Excel Sheets Deleting Data SheetsDeleting Menu Sheets Deleting the Query Log Saving and Exiting the Excel Client 10.Security Alerts Multiple Issues Accessing and Creating Reports Quick Reports Window Accessing Quick Reports Scheduled Reports Window Accessing Scheduled Reports Creating Custom Reports Custom Reports Window Building the Report Menu Report Sample Resolution Common Performance Metrics on page 6.11 for Option Purpose Select Systems Select Domains Report Menu for Daily Vlun Performance Generating the Report Creating Custom Reports Creating Custom Reports Query Results Report Selection Controls Available by Report Object Selection ReportTime Selection 3PAR System Reporter User’s Guide Format Selection Report Selection At Time Standard Report TypesReport Type Purpose Versus Time Time Report Type Purpose HistogramHistogram At Versus Time Report Example Versus Time with Compare=System Report Example Group By SystemHost At Time Report Example Histogram Versus Time Report Example 10.Histogram At Time Report Example Select Summary Columns Group By CompareSelect Peak Pdid System Reporter Order By Order by the CPG that the LD belongs to Sum End Buckets Low BucketHigh Bucket Reload After mins Time SelectionBegin/At Time End Time Count Include Chunklets Object SelectionSelect PDIDs Select Disk Types Select Cage IDs Include PDs in States Select Disk Speed Select Usr CPGs Select CPGs Select Snp CPGs Select RAID Types Select LDs Select LD Usage Select VVs CTRL+CLICK Select Prov Types Select Ports nsp Select VV Types Select Hosts Select Port Types Select To Nodes Select Nodes Report Information Format Selection Web interface onlySelect Queues 11.Example of a Table Chart Types Stacked Vert Bars Stacked Bars Lines Stacked Area Vert Bars Bars Time Labels Chart X PixelsChart Y Pixels Select Space Unit Time Format Choose Change Chart Type Working with Excel ChartsChoosing a Chart Type 18.Change Chart Type Menu for the Excel Client Refreshing Charts 19.Data Range Properties Shortcut Menu External Data Range Properties dialog box appears 20.External Data Range Properties Dialog Box Retaining Charts Exporting Executable Reports Customizing Excel Reports Adding Custom Columns to a Data Sheet =IFN530,H53/N53*100,0 Creating a Custom Chart=H53+J53+K53 Working with Excel Charts Chart Saving Customized Settings Summary Report Interpreting Report Metrics Metric Description Summary Report Metrics 3PAR System Reporter User’s Guide Metric Description PD Space Reports Space Reports Totals LD Space ReportsUsage Type Description Spare, Unused, Failed VV Space Reports VV Space Report Metrics 3PAR System Reporter User’s Guide Common Performance Metrics Performance Reports 3PAR System Reporter User’s Guide VV Cache Performance Reports Node Cache Performance Reports CPU Performance Reports Link Performance Reports Adaptive Optimization Reports Accessing the System Reporter Policies Configuring the System Reporter Policy Settings Policy Default Full path to directory containing InForm Configuring Sampling PoliciesCLI password files Etc/clipwdir on Linux See Compacting Databases on Compacting Databases Editing Sampling Policies Connection Dialog Box Adding, Removing and Re-Configuring InServ Storage Systems Verifying Addition of a Storage Server Adding a Storage ServerRemoving a Storage Server Click Remove InServ Re-Configuring a Storage Server Sampling Adding an Alert Rule Configuring Rules for Email Alerts3PAR System Reporter Policies window appears Click Add Alert Removing an Alert Rule Changing an Alert Rule Data Table Alert Rule ParametersClick Remove Alert Data Table Description Resolution Metric Resolution DescriptionSystem Metrics Description Data Tables Averaged over the sample Limit Value Direction Limit Count Example of an Alert RuleCondition Condition Value Scheduledreports/report directory/report name/YYYYMMDDHH Scheduling Reports Click Add Scheduled Report Adding a Scheduled ReportChoose the Scheduled Reports tab Changing a Scheduled Report Removing a Scheduled Report About File Permissions Configuring Adaptive Optimization Modifying the Apache Http Server Configuration Files Modifying the Apache Http Server Configuration Files About Web Queries Web Queries Data Format Version Report System Reporter Version Report Report Options About Reports and OptionsCategory Option=value Example CompareDaily Hourly Values VVType HostProvType LDUsage Comparesel Ctxtsws InterruptsTotalspace Rawspace Vvspace PdspaceStatpd Statld Chart CharttabRefresh Both Stackedbars CharttypeStackedvbars Stackedarea Value GraphxGraphy Bottom Right GraphlegposTableformat Excel Alllabels Begintsecs Timeform Tsecs Endtsecs Groupby PortNode Groupby=System Orderby Order results by Virtual Volume VV Type IOPs Service TimeBandwidth IO Size Idle TotalAccessesIntr Ctxt Ldspaceunit Histbegin Examples PdspaceunitMiB 220 bytes GB 109 bytes Chunklets Vvspaceunit All cage IDs Selcageid Nrmavaildrty Selchunks Selchunkls=NRMAVAILCLEAN&selchunks=SPRAVAILCLEAN Selchunkls=NRMAVAILCLEAN Selusrcpg SelcpgSelsnpcpg Seldisktype All Disk Speeds SeldiskspeedAll Disk Types Domainname SeldomainALL Domains Domain2 SelhostALL Hosts Hostname Sellduse SelldAll LDs Ldname Log Selnode All Ports SelnspAll Nodes ALL PDIDs Selpdid Selporttype SelpdstateAll PD States Valid Selraidtype Selraidtype= SelsysSelraidtype=1&selraidtype=5 All Systems Selvv Selprovtype Selvvtype Report Types Pdspacegroup SummaryPdspacetime Ldspacegroup Ldspacetime Pdperftime VvspacetimeVvspacegroup Pdsvthist Pdperfgroup Portperfgroup PdsvthistvstimePortperftime Portsvthistvstime PortsvthistLdperftime Compare=System Ldsvthistvstime LdperfgroupLdsvthist Vlunsvthist VlunperftimeVlunperfgroup Vvcmpgroup VlunsvthistvstimeVvcmptime Cpuperfgroup Cpuperftime Regmoves RegiodensityVvtier Regiondensity example Report Option Options Available by ReportVvtier example M M Report Option Histsvtpd M M M M M M Objects Present Reports Http//localhost/cgi-bin/3par-rpts Using tableformat=excel Without using tableformat=excel Report Table values Objectspresent&objectpres= ldname on Sampletimes on Objectpres= cageid Objectspresent&objectpres=cageid Objectpres= cpgnameOutput Objectspresent&objectpres=cpgname Objectpres= diskspeed Objectspresent&objectpres=disktype Objectpres= disktypeObjectspresent&objectpres=diskspeed Objectspresent&objectpres=domainname Objectpres= domainnameObjectpres= hostname Objectspresent&objectpres=hostname Objectpres= ldname Objectspresent&objectpres=ldname Objectpres=node Objectspresent&objectpres=node Objectpres=pdid Objectspresent&objectpres=pdid Objectpres=port Objectspresent&objectpres=port Objectpres= system Objectspresent&objectpres=vvname Objectspresent&objectpres=system usingObjectpres= vvname Objectspresent&objectpres=vvnamenosnap Objectpres= vvnamenosnap Objectspresent&objectpres=porttype Objecctpres= porttype Time string with format yyyy-mm-dd hhmmss Sampletimes Objects Present Reports Objects Present Reports Overview Using Adaptive Optimization Overview Selecting an InServ for Adaptive Optimization Configuring Adaptive Optimization Adaptive Optimization Configuration Main Window Tier Definition CPG Name and GiB Configuring Adaptive Optimization Configuring Adaptive Optimization Measurement Hours Schedule Adding an Adaptive Optimization Configuration Adaptive Optimization ModeAdd Adaptive Optimization Configuration window appears Adaptive Optimization Active Change Adaptive Optimization Configuration window appears Changing an Adaptive Optimization Configuration Removing an Adaptive Optimization Configuration Region IO Density Report Region IO Density with Adaptive Optimization Region IO Density without Adaptive Optimization VV Tiers Report Space Moved Report Adaptive Optimization Space Moved Report Cli% source showvvcpg.tcl Showvvcpg CLI extension CPG Showvvcpg CLI extension 10.1 Using The Database Schema Table Versions and Naming Conventions Administrative Tables10.2 Policy Table Sampleinserv Table 10.3 System Table 10.4 10.5 Alertconfig Table Datatable value 10.6 Scheduledreport Table 10.7 10.8 Scheduledreportlog Table Adprovconfig Table10.9 10.10 Adprovlog Table 10.11 Ldrgmoves Table 10.12 10.13 Recentsample Table Sample Data and Inventory Tables10.14 Pdspace Table 10.15 10.16 10.17 Pdspaceinv Table 10.18 Ldspace Table 10.19 10.20 Ldspaceinv Table 10.21 Vvspace Table 10.22 10.23 10.24 Vvspaceinv Table 10.25 10.26 Cpgspace Table 10.27 10.28 Cpgspaceinv Table 10.29 Common Performance Table Columns 10.30 10.31 10.32 10.33 10.34 Metric SQL Expression 10.35Calculating Common Performance Metrics IO Size Histogram Buckets Service Time Histogram Buckets10.36 Statpd Table 10.37 10.38 Statpdinv Table 10.39 Statld Table 10.40 Statldinv Table 10.41 Statport Table 10.42 Statportinv Table 10.43 10.44 Statvlun Table 10.45 Statvluninv Table 10.46 Statvvcmp Table 10.47 10.48 Sysuid 10.49Statvvcmpinv Table Statcpu Table 10.50 Null CPU 10.51 Statlink Table 10.52Statcpuinv Table 10.53 Statlinkinv Table 10.54 Statldrg Table 10.55 Statldrgtimeinv Table 10.56 Spacetimeinv Table 10.57Perftimeinv Table 10.58 11.1 Troubleshooting Overview Sampling Component Log Files Troubleshooting the Sampling Components11.2 11.3 Installation ConfigurationInServ Sampling Errors Sampler Service/Daemon Errors Troubleshooting Adaptive Optimization ComponentsErrors incurred by the inservsample process Errors incurred by the CLI sampler process 11.5 Troubleshooting the Web Server ComponentsTroubleshooting the Excel Client 11.6 Extracting Data from the Database with sysbck 11.7 11.8 Examples of Using sysbck for Various Databases From MySQL to Oracle 11.9 11.10 11.11 Dumping the Entire SQLite Database Dumping the Entire Database11.12 Dumping the Entire MySQL Database 11.13 11.14 IX.1 Index IX.2 IX.3 IX.4 IX.5 IX.6 Release level Revision summary RH.1 RH.2 Configuration, Update, and Removal RH.3 RH.4 RH.5 RH.6 RH.7 RH.8