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.
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.
Now that the Data Source is created, we will create a SQL Server Linked Server via the following process:
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.
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.
prlf_15.program_name AS blocked_program,
prlf_15.username AS blocked_user,
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,
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.