Mining Performance Data from SQL Sentry (Part 2)

Introduction

In Mining Performance Data from SQL Sentry (Part 1) of this series we learned how to use the SQL Sentry repository database to find out about the various computers and connections SQL Sentry knows about. In this post, we're going to build on that foundation, and use it to get some deeper information about the host operating system for each computer. This will be a fairly short post, as we're only covering one table, but this one table holds some really useful information for us, as you'll discover momentarily.

Operating System

We already know that the record for a computer is found in the Device table. The Device table contains some basic information on the computer such as the platform and how to find it on the network, but what if we want to know more about it? Enter the PerformanceAnalysisDeviceOS table.

Note that tables prefixed with “PerformanceAnalysis” are generally part of SQL Sentry Performance Advisor, so we'll only be able to use this table for systems being monitored with Performance Advisor. That is easily restricted with a join back to EventSourceConnection and a filter on the “IsPerformanceAnalysisEnabled” column.

As you can see, this table contains several columns that tell us all about the operating system including its name, build number, service pack level, the time zone offset in minutes, install and last start date, memory details, system location, and full version number.

This information is used in various ways inside of SQL Sentry to make decisions about how to pull performance data from the system, but it can also be used in your own queries for reporting.

Putting it all Together

Let's say you are monitoring several systems with SQL Sentry, and you want to find all the computers that are running in UTC time. You might build a query similar to this one:

SELECT 
	d.FullyQualifiedDomainName,
	d.IPAddress,
	os.Name,
	os.Version,
	os.ServicePackVersion
FROM
	dbo.PerformanceAnalysisDeviceOS as os
	JOIN dbo.Device as d ON
	d.ID = os.DeviceID
	JOIN dbo.EventSourceConnection as cnn ON
	cnn.DeviceID = d.ID
WHERE
	cnn.IsPerformanceAnalysisEnabled = 1
	AND os.CurrentTimeZone = 0
GROUP BY
	d.FullyQualifiedDomainName,
	d.IPAddress,
	os.Name,
	os.[Version],
	os.ServicePackVersion;

And the output from one of our test servers here:

I'm sure there are other things you could come up with that are more useful than querying by time zone. Checking for missing or old service packs comes to mind, or searching for all those old Windows 2003 servers lurking about.

Whatever the need may be, this becomes a very useful table when we get to querying performance data, because the OS version can make a big difference in what counters are or are not being collected, or are even available.

Next Time

In part 3 of this series, we're going to take a look at some tables in the repository that will tell us how the disk system is configured on systems that are monitored with SQL Sentry Performance Advisor. Stay tuned!

Until next time,
-JRH

Mining Performance Data Series

• Part 1: Mining Performance Data from SQL Sentry (Part 1) 
• Part 2: Mining Performance Data from SQL Sentry (Part 2) ← You are here.
• Part 3: Mining Performance Data from SQL Sentry (Part 3) 
• Part 4: Mining Performance Data from SQL Sentry (Part 4) 
• Part 5: Mining Performance Data from SQL Sentry (Part 5) 
• Part 6: Mining Performance Data from SQL Sentry (Part 6)