Using SQL Server to Query the PI Repository

A colleague of mine (Darren Mallette) wrote a great blog post explaining how to query the embedded Foglight Performance Investigator (PI) repository using tools which provide native PostgreSQL connectivity (i.e. Toad Data Point or pgAdmin).  While reading this post, I thought: "SQL Server provides PostgreSQL connectivity via Linked Servers, I wonder if you could query the PI repository from SQL Server itself?"  The following blog will walk through this science experiment.

The first step, is to take an inventory of your current Foglight Performance Investigator setup, specifically the location and name of your PI Repository.  The location (server) of your Performance Investigator repository, can be found using the Foglight Administration -> Agents -> Agent Status interface, searching for "PI_Repsitory", and noting the server name shown in the "Agent Manager" column.  

Once you have found the location of your PI repository, we next need to find the name of the database itself.  The name could vary based on your specific installation, so I've included a process below for determining the specific name of your PI repository.

1. Remote Desktop to the server that hosts your PI repository (or ssh if your PI repository runs on Linux).
2. Open a command prompt, and navigate to the following directory ($FGLAM_HOME\infobright\bin).  The Infobright directory will be wherever you installed it to, so you may have to look elsewhere if you configured it differently than the default.
3. Connect to the PostgreSQL command line using the psql.exe utility.  You can connect by running the following command:
   1. psql.exe -U postgres
      1. When prompted, enter the password, which by default is "postgres".
4. Once in the PostgreSQL prompt, run the following query:
   1. SELECT datname FROM pg_database;
5. You will likely see a few different databases, the one you are looking for starts with "spimssql".  Take note of this database name.

Below you can see a screenshot of a command prompt session running these commands.  In the example referenced in the screenshot, the PI Repository database name is: spimssql-a99cd47f-bfd9-470b-8fbc-c765bc9e6714

Now that we know the location, and name of our PI repository, we can prepare SQL Server for PostgreSQL based connectivity.  The first step in this is to download the PostgreSQL ODBC driver.  For this example, I used the latest current version (9.6) available from the following site: https://www.postgresql.org/ftp/odbc/versions/msi/

Download and install the ODBC driver on your SQL Server.  The installation is fairly straight forward.

Once installed, we need to create a system DSN for our SQL Server to connect with.  Depending on your version of Windows, this process may look slightly different, although the general steps should be similar.

1. Launch the ODBC Data Sources interface from Control Panel -> Administrative Tools.
2. Click on "Add" on the System DSN tab to create a new Data Source.
3. Choose the PostgreSQL Unicode(x64) driver that you just installed, and click "Finish".
4. Fill in the form as in the screenshot below.  You will use the server name as "Server", and PI Repository name as "Database".  The "Port" will be 5029, and the "User Name" and "Password" will both be "postgres", unless you changed these during your PI Repository creation.
5. Set the Data Source name and Description to be whatever you like.  In this example, I've used "PostgreSQL35W" as the name.
6. Make sure that the "Test" button connects successfully, and troubleshoot any connectivity issues by making sure that the fields are correct, and that your SQL Server instance can "ping" the "Server".  You also may need to check whether port 5029 is open on the firewall between your SQL Server and the PI Repository.
7. Assuming that the "Test" is successful, click the "Save" button.

Now that the Data Source is created, we will create a SQL Server Linked Server via the following process:

1. Launch SQL Server Management Studio, and connect to your SQL Server Instance.  You will need to connect as a sysadmin level account so that you can create and manage Linked Servers.
2. Navigate to the Server Objects -> Linked Servers folder, right click, and choose "New Linked Server...".
3. Give your Linked Server a name, in this example I went with "PIREPO", and change the radio button to "Other data source". 
4. Choose "Microsoft OLE DB Provider for ODBC Drivers", type "PostgreSQL" as the "Product" and your data source name as your "Data Source". In this example the Data Source name is "PostgreSQL35W".  The dialog should look as the screenshot below:
   1. 
5. In the "Security" tab, select the "Be made using this security context:" and enter a username and password of postgres, assuming you have used the defaults.
6. In the "Server Options" tab, configure as in the screenshot below.
   1. 
7. Click "Ok" to create your linked server.
8. You should right click on your newly created Linked Server, and test the connection to ensure that it is successful.

Now that your Linked Server is created, you can query it as you would any other database object.  We can start with one simple query below that returns the number of instances with collected data associated with them.  While running this code sample, you will need to replace the "<PIREPOSITORYNAME>" reference with the name of your own PI repository.

SELECT instance_key,
       HOST,
       instance_name,
       hw_type,
       os_type,
       db_version
FROM PIREPO.<PIREPOSITORYNAME>.[public].pass_instance_dim
ORDER BY instance_key

Below you can see a screenshot from SQL Server Management Studio showing the returned result set:

Here is a more complex query that you can run.  This query returns a list of blocking scenarios, along with the SQL Statements involved.

SELECT pid.HOST,
       pid.instance_name,
       prlf_15.database_name,
       prlf_15.program_name AS blocked_program,
       prlf_15.username AS blocked_user,
       prlf_15.event_duration,
       psd.sql_short_text AS blocked_sql,
       prlf_15.blck_username AS blocking_user,
       pbd.batch_short_text AS blocking_sql
FROM PIREPO.<PIREPOSITORYNAME>.[public].pass_request_lock_fact_15m prlf_15,
     PIREPO.<PIREPOSITORYNAME>.[public].pass_syntax_dim psd,
     PIREPO.<PIREPOSITORYNAME>.[public].pass_instance_dim pid,
     PIREPO.<PIREPOSITORYNAME>.[public].pass_batch_dim pbd
WHERE     prlf_15.query_hash like psd.query_hash
      AND pid.instance_key like prlf_15.instance_key
      AND prlf_15.blck_sql_handle like pbd.sql_handle
      AND prlf_15.event_duration > 10.0

That should hopefully be enough to get you started.  Querying the Foglight Performance Investigator repository using a SQL Server Linked Server may open up several custom reporting scenarios to SQL Server DBA's who are comfortable writing SQL queries, but are unfamiliar with PostgreSQL based offerings.

Finally, while the process above was performed using SQL Server, there is no reason why a very similar process for Oracle couldn't be followed.  From a Performance Investigator perspective, the only piece that would change if using Performance Investigator for Oracle, would be that the PI Repository name would include "spiora" instead of "spimssql".  If you are interested in using Oracle to query the Performance Investigator repository (instead of SQL Server Linked Servers), there appear to be a few options that should work (pg_link and Oracle Gateways being a few that I've found).  If anyone decides to tackle this use case around Oracle, I'd love to hear your feedback!

For repository schema, including sample queries, view the Foglight Performance Investigator pdf.