Transcription
PRTG DB PLUGINUSER MANUALAbstractThe purpose of this document is to describe the prerequisites, to configure and to use thePRTG DB Plugin application.CORP-IT3/30/2021V 1.49
PRTG DB Plugin ManualTABLE OF CONTENT1INSTALLING THE PRTG DB PLUGIN . 51.1PREREQUISITES . 51.1.1 SQL Server Express . 61.1.2 .NET Framework 4.5 . 61.1.3 IBM DB2 Driver . 71.1.4 SAP HANA . 91.1.5 IBM Informix . 91.2MAIN INSTALLATION . 111.32LICENSE AND PRTG CONFIGURATION . 13WORKING WITH PRTG DB PLUGIN UI . 142.1HOME . 142.1.1 Profile panel . 152.1.2 Query panel . 162.1.3 Result panel . 172.2PROFILES. 182.3QUERIES . 192.3.1 Query format . 192.3.2 Query example . 202.3.3 Query result . 202.3.4 XML result . 212.3.5 Parameters . 222.3.6 Comment section . 232.3.7 Query Groups . 272.3.8 PRTG Device Templates . 282.4ITPS LIBRARY . 292.53ABOUT . 31WORKING WITH PRTG DB PLUGIN CONSOLE . 323.1LICENSING SPECIFIC PARAMETERS . 323.1.1 Validate a full license . 323.1.2 Deactivate a license . 333.2PROFILE CREATION . 333.3GENERAL USE . 343.3.13.3.23.3.34Profile and Query . 34Manually specify parameters . 34Debug . 37PRTG IMPLEMENTATION . 384.1ADDING A CUSTOM SENSOR IN PRTG . 384.2MENTIONS. 404.2.14.2.2Recommendations . 40Limitations . 41CORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 2 of 41
PRTG DB Plugin ManualTABLE OF FIGURESFIGURE 1 - THE FIRST WINDOW IN INSTALLER . 5FIGURE 2 - PREREQUISITES WINDOW . 5FIGURE 3 - SQL SERVER EXPRESS DOWNLOAD . 6FIGURE 4 - INSTALLING SQL SERVER EXPRESS . 6FIGURE 5 - IBM DB2 DRIVER DOWNLOAD . 7FIGURE 6 - IBM DB2 SETUP . 7FIGURE 7 - SETUP LICENSE AGREEMENT . 8FIGURE 8 - INSTALLATION TYPE . 8FIGURE 9 - SETUP CONFIRMATION . 8FIGURE 10 - SAP HANA DRIVER INSTALLATION . 9FIGURE 11 - IBM INFORMIX CHECKBOX . 9FIGURE 12 - INSTALL INFORMIX CLIENT. 9FIGURE 13 - INSTALL C . 10FIGURE 14 - INFORMIX LICENSE AGREEMENT . 10FIGURE 15 - INFORMIX INSTALL LOCATION . 10FIGURE 16 - INFORMIX INSTALL SET . 10FIGURE 17 - INFORMIX INSTALLATION SUMMARY . 10FIGURE 18 - INSTALLATION COMPLETE . 11FIGURE 19 - LICENSE AGREEMENT . 11FIGURE 20 - CONFIGURE SQL CONNECTION. 12FIGURE 21 - INSTALLATION CONFIRMATION . 12FIGURE 22 - ACTIVATE LICENSE . 13FIGURE 23 - ADD PRTG SERVER . 13FIGURE 24 - HOMEPAGE . 14FIGURE 25 - ADD PROFILE . 15FIGURE 26 - EDIT PROFILE. 16FIGURE 27 - ADD QUERY . 16FIGURE 28 - EDIT QUERY BUTTON . 16FIGURE 29 - GENERATE QUERY . 17FIGURE 30 - EXECUTE QUERY. 17FIGURE 31 - PROFILES PANEL . 18FIGURE 32 - QUERIES PANEL . 19FIGURE 33 - PRTG SENSOR . 21FIGURE 34 - EXAMPLE RESULT OF PARAMETER REPLACING . 22FIGURE 35 - AUTOMATICALLY FILLED ADD QUERY DIALOG . 25FIGURE 36 - DB PLUGIN SENSOR WITH LIMITS AND PARAMETERS . 26FIGURE 37 - ADD QUERY GROUP . 27CORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 3 of 41
PRTG DB Plugin ManualFIGURE 38 - QUERY GROUPS. 27FIGURE 39 - GENERATE PRTG DEVICE TEMPLATE . 28FIGURE 40 - PRTG DEVICE MENU . 28FIGURE 41 - RUN AUTO-DISCOVERY . 28FIGURE 42 - CUSTOM SENSORS . 29FIGURE 43 - ITPS LIBRARY . 29FIGURE 44 - ITPS LIBRARY OFFLINE . 30FIGURE 45 – ABOUT DIALOG . 31FIGURE 46 - HELP . 32FIGURE 47 - RETRIEVE MACHINE ID . 32FIGURE 48 - ACTIVATE LICENSE . 33FIGURE 49 - DEACTIVATE LICENSE . 33FIGURE 50 - REMOVE ALL HOSTS. 33FIGURE 51 - PROFILE AND QUERY PARAMETERS . 36FIGURE 52 - MANUALLY SPECIFIED PARAMETERS . 37FIGURE 53 - DEBUG. 37FIGURE 54 - ADD SENSOR 1 . 38FIGURE 55 - ADD SENSOR 2 . 39FIGURE 56 - EXE/SCRIPT ADVANCED. 39FIGURE 57 - SENSOR SETTINGS . 40CORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 4 of 41
PRTG DB Plugin Manual1 INSTALLING THE PRTG DB PLUGINPRTG DB Plugin UI can be used on multipleservers with the same license and database. If thePRTG Network Monitor is not installed on the machine,only PRTG DB Plugin UI will be installed.You need to install DB Plugin on each PRTGProbe server where you want to create DB Pluginsensors.Figure 1 - The first window in installer1.1 PrerequisitesWhen you click on next, the second window willappear containing information about the prerequisites.When a driver is missing, it will be checked bydefault. Otherwise, it will be unchecked and will appearas already installed.You may choose only those drivers that you need.If, for example, you do not want to monitor IBM DB2database, you can uncheck IBM DB2 Driver.Drivers for MSSQL, MySQL, Oracle and PostgreSQLare always installed. Drivers for IBM DB2, SAP Hanaand Informix are optional.If you plan to connect to SQL server, which is configuredwith hardened SSL/TLS options you need to installMicrosoft ODBC Driver 17 for SQL Server x64.CORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .chFigure 2 - Prerequisites Window3/30/2021Page 5 of 41
PRTG DB Plugin Manual1.1.1SQL Server ExpressDB Plugin requires a MSSQL database. If youalready have a SQL instance available where youwant to place this database, you can deselect SQLServer Express and choose existing SQL Instanceduring a later step.If you do not have any MSSQL Instance (2012SP1 or newer), you can choose to install the freeversion of MSSQL Express locally on your PRTGserver or you can manually install this product on anyother system in your environment reachable fromPRTG server.You need sysadmin role (or dbcreator andsecurityadmin roles) on the SQL server for theinstallation phase. MSSQL database needs to beFigure 3 - SQL Server Express downloadaccessible for each DB Plugin installation.If you choose to install SQL ServerExpress via the installer, it will download andinstall a SQL instance locally for you. YourWindows account will be granted sysadminrole on the instance.Figure 4 - Installing SQL Server Express1.1.2.NET Framework 4.5On older Operating Systems such as Windows Server 2008 R2 or older, .NET Framework4.5 is not installed by default.If .NET Framework 4.5 is not installed when initializing the installation, the installer will notifyyou and automatically check the relevant box in the Prerequisites list.CORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 6 of 41
PRTG DB Plugin Manual1.1.3IBM DB2 DriverIf the checkbox is checked, the IBM DB2 clientwill be downloaded and installed. This is an optionalstep required only if you plan to monitor IBM DB2instances. The IBM DB2 Client installation uses about350MB of space. It also requires an internetconnection.Figure 5 - IBM DB2 Driver downloadWhen the download is completed, theDB2 Setup window appears. If you click onNext, the Software License Agreementappears. If you click on Cancel, theinstallation of the driver will be interrupted.Figure 6 - IBM DB2 SetupCORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 7 of 41
PRTG DB Plugin ManualTo continue with the installation of thedriver, it is required to have the first boxchecked, after which, you can click on Nextto continue.If you click on Cancel, the installation of thedriver will be interrupted. If you click on Back,the installation will return to the previousstep.Figure 7 - Setup License AgreementAfter that, the Installation typewindow will appear. The installation typeneeded is Typical.Click Next and then Install for theinstallation to start.Figure 8 - Installation TypeOnce the instalation is complete, youwill be prompted with a confirmation window.Figure 9 - Setup confirmationCORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 8 of 41
PRTG DB Plugin Manual1.1.4SAP HANAIf the checkbox is checked, the SAPHana Driver will be installed. This driver ispart of the DB Plugin installer and does notneed to be downloaded, hence there is noneed for an internet connection to finish thisstep.Figure 10 - Sap Hana Driver Installation1.1.5IBM InformixIf the checkbox is checked, the IBM Informix client will be downloaded and installed. This isan optional step required only if you plan to monitor IBM Informix instances.Figure 12 - Install Informix ClientFigure 11 - IBM Informix CheckboxCORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 9 of 41
PRTG DB Plugin ManualIf Microsoft Visual C is notinstalled on your system, it will ask you forapproval to install it.Figure 13 - Install C After accepting the Software LicenseAgreement, you can choose the installationpath, the install set (Typical recommended) andstart the installation.Figure 14 - Informix License AgreementFigure 15 - Informix Install LocationFigure 16 - Informix Install SetFigure 17 - Informix Installation SummaryCORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 10 of 41
PRTG DB Plugin ManualWhen the installation has finished, youwill receive a confirmation message.Figure 18 - Installation Complete1.2 Main InstallationAfter all drivers are installed, a window such as inFigure 19 will appear. To start the main installation, youmust agree to the License terms and condition andclick on Install.Figure 19 - License AgreementCORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 11 of 41
PRTG DB Plugin ManualAfterwards,theConfigureSQLServerConnection window will appear. Here, you need tospecify the SQL Server and Instance, as well as theauthentication method for the DB Plugin to connect tothe Database.In case local SQL installation is found you canselect it from drop-down menu. Otherwise pleaseconfigure corresponding remote SQL server.Figure 20 - Configure SQL ConnectionAfter the installation finishes, it will prompt youwith a confirmation message.Figure 21 - Installation ConfirmationCORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 12 of 41
PRTG DB Plugin Manual1.3 License and PRTG ConfigurationThe DB Plugin will automatically create a 30day Free Trial License upon the first start-up.If you have purchased a license, you can enteryour License Key in the About section or using usingcommand line as described in chapter Validate a fulllicense. This is also where you can find the MachineID required to generate a new License Key.Figure 22 - Activate LicenseAfter the first start-up, you will also need to addthe PRTG Server / Servers, depending on your licensetype. This is done from the Add button, in the PRTGServers List section of About dialog. Here, you willneed to provide the following:-Protocol: HTTP or HTTPS-Port: by default, 80 for HTTP and 443 forHTTPS-Url: PRTG Server URL-Username: PRTG Username used for theDB Plugin-Password: password/passhash for thePRTG User-Use Passhash: check this box if you wantFigure 23 - Add PRTG Serverto rather provide passhash instead of password. The passhash can be found in PRTG,under Setup Account Settings My Account.CORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 13 of 41
PRTG DB Plugin Manual2 WORKING WITH PRTG DB PLUGIN UI2.1 HomeThe first page of the application is the place where you can: add or edit profiles/queries generate sensor parameters in format -profile: profile name -query: query name that can be used to create a sensor in PRTG execute a query to see its XML representation directly in UI for testing and debuggingpurposes.Figure 24 - HomepageCORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 14 of 41
PRTG DB Plugin Manual2.1.1Profile panelYou can add a profile from the Home page byclicking button in the Profile panel.To add a profile, there are some mandatoryfields that are necessary to complete. These are: Database Type: Specify the DB Typefrom the dropdown menu. Profile: (Mandatory field) Representsthe name of the profile, which cannotcontain spaces. Host: (Mandatory field) Use hostname,Figure 25 - Add ProfileIP address or SQL Server name. Database: (Mandatory field only for DB2, Oracle and SAPHANA database type)Represents the name of the database. Port: (Optional field) For MYSQL, if it is not filled, the default value will be 3306. ForORACLE, if it is not filled, the default value will be 1521. For DB2, if it is not filled, thedefault value will be 5000. Authentication: The default authentication type is “SQL Authentication”. If thedatabase type chosen is MSSQL or PostgreSQL, the default option is “Windowauthentication”, but you can also use “SQL Authentication”. For WindowsAuthentication, your Windows user is used for execution in UI and PRTG Serviceaccount or Device Windows credentials are used when scanning the sensor fromPRTG. For the rest of the database types, the authentication is set to “SQLauthentication” and cannot be modified. User / password: Mandatory fields, except for MSSQL or PostgreSQL when usingWindows Authentication. Encrypted: Specify whether the connection is encrypted or not. Specific to SAPASE databases. Additional connection string parameters: Specifies to use custom connectionstring parameters when connecting to the database. Use this in case your databasehas special configuration and requires special connection parameters or normalconnection from DB plugin does not work. Format of parameter is key-value pairconnected with . Use ; (semicolon) for multiple options.Example: Use OldGuids true; when your MySQL database is configured with skipcharacter-set-client-handshakeCORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 15 of 41
PRTG DB Plugin ManualAfter all the required information has been filled in, you can save the profile by selecting Save at thebottom of the window.Before saving, you also have the option of testing the configuration by selecting Test.If you want to edit a profile, you can do so by selectingProfile from the dropdown menu, after which youneed to select the Edit option.Figure 26 - Edit Profile2.1.2Query panelTo add a query from the home window, it isnecessary to click on the button from the querypanel. The following fields are mandatory: Name: (Mandatory field) Represents thename of the query, which cannot containspaces. Show Query: (Mandatory field) Write a newquery using the pencil icon.oPath: Instead of manually insert thequery text, click Upload button andbrowse for your file. The query canbe added from a .txt or .sql file.Figure 27 - Add QueryIf you want to choose a query, you need to select a Profile first. Only queries matching theDBMS profile type will be available.You can edit a query via the Edit Query option.Figure 28 - Edit Query ButtonCORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 16 of 41
PRTG DB Plugin Manual2.1.3Result panel2.1.3.1 Generate parametersOnce both Profile and Query have been selected, you can Generate parameters for a PRTGSensor.Figure 29 - Generate Query2.1.3.2 Execute queryYou need to select both Profile and Query to beable to Execute the query. The result is an XMLformatted data prepared for PRTG processing. This canbe used for testing and troubleshooting your queries.For details about the Query design, see chapter 2.3QueriesFigure 30 - Execute QueryCORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 17 of 41
PRTG DB Plugin Manual2.2 ProfilesA profile represents your Database server / instance.You can see the saved profiles on the Profiles panel. From this panel, you can Add, Edit orDelete profiles.Figure 31 - Profiles PanelTo search for a profile, use the search field on the right side. The search is performed onProfile Name, Host and Database names.To add the profile, you need to select the Plus icon, which is available on the right sideabove the header section of the table.To clone the profile, you need to right click the desired profile and select the Clone option.Clone option is also available on the left side of each row under the three dots button. After youclick on clone, you are prompted to provide a new Name and modify any other desired informationbefore saving the new profile. Once the option is confirmed, the profile is cloned.To edit the profile, you need to right click the desired profile and select the Edit option. Editoption is also available on the left side of each row under the three dots button.To delete the profile, you need to right click the desired profile and select Delete option.Delete option is also available on the left side of each row under the three dots button. Once theoption is confirmed, the profile is deleted.CORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 18 of 41
PRTG DB Plugin Manual2.3 QueriesOn the queries panel, you will find the queries that have been added to the database. Fromthis panel, you can add, edit or delete queries.Figure 32 - Queries Panel2.3.1Query formatThe query result is expected to have from 2 to 5 columns in specific format.Column 1Name: Channel (not case sensitive)MandatoryType: VARCHAR, NOT NULLPurpose: name of the channel, which will be shown in PRTG SensorColumn 2Name: Value (not case sensitive)MandatoryType: INT FLOAT (numeric)Purpose: numeric value for the channel in PRTGCORP-IT 41 55 417 4744Seedammstrasse 3, 8808 .ch3/30/2021Page 19 of 41
PRTG DB Plugin ManualColumn 3Name: Channel Settings (not case sensitive)OptionalType: VARCHAR, XML formatPurpose: PRTG channel configuration according to Paessler documentation.Column 4Name: Error (can be 1 or 0)OptionalType: BIT (numeric)Purpose: Sensor will be set to down status regardless the channel limits.Column 5Name: Text (case sensitive)OptionalType: VARCHARPurpose: Message to be shown in PRTG sensors. Useful for providing additional, nonnumerical values.Only first text column will be taken into account if final result contains multiple rows withtext columns.2.3.2Query exampleSELECTDB NAME(database id) AS Channel,sum(size * 8/1024) AS Value,' Unit Custom /Unit CustomUnit MB /CustomUnit ' AS Channel SettingsFROM sys.master filesGROUP BY database id2.3.3Query resultChannelValueChannel Settingsmaster4 Unit Custom /Unit CustomUnit MB /CustomUnit tempdb2 Unit Custom /Unit CustomUnit MB /CustomUnit model3 Unit Custom /Unit CustomUnit MB /CustomUnit msdb35 Unit Custom /Unit CustomUnit MB /CustomUnit PRTG DB Plugin4 Unit Custom /Unit CustomUnit MB /CustomUnit CORP-IT 41 55 41
PRTG DB Plugin Manual CORP-IT 41 55 417 4744 Seedammstrasse 3, 8808 Pfäffikon support@corp-it.ch 3/30/2021
