Aggregating Server Data from the MAPS Tool

Last updated Mar 28, 2003.

Most DBAs are looking for a way to locate the installations of
SQL Server in their organization. They need to find and document all of these
servers, including the versions, the editions, the amount of CPU’s on the
system, the amount of RAM and so on.

In the past, I’ve used three methods to do this:

Build my own

Use Microsoft’s MOM (Now System Center Operations Manager, or SCOM)

Use a third-party product like Spotlight on SQL Server from Quest

All of these work, and in fact I’ve switched in and out
between them. And sometimes I’ve even used a couple of them in tandem,
because one solution might not have all of the information I need.

Each of these solutions offers advantages and disadvantages.
Building my own system provided the most flexibility, and not counting my
development time, the least cost. But it also is very proprietary, and unless I
spent time documenting it well was not continued beyond my tenure as the company
DBA.

Using SCOM (what Microsoft Operations Manager became) is a great
solution, especially when you can “stack” the database in with other
components such as network equipment and Windows Servers to see an entire line
of business application. But it is not as comprehensive in its monitoring and
control of the SQL Server system, especially when you factor in the new features
in SQL Server 2008 like Policy Based Management and the Management Data
Warehouse. I have no doubt that the System Center product will eventually
“know” about these features, but as of this writing, it does not.
And of course there is a cost to buy the product, and the time spent learning
and implementing it, in addition to the hardware needed to run it.

I have used (and
written about) other products that monitor SQL Server, such as Spotlight on
SQL Server, from Quest. These are also great solutions, but they also do not
know about the new features in SQL Server 2008, they have a relatively high
cost, and they require hardware to run and time to learn.

So which route do I take now? Well, there are considerations, of
course. Perhaps my firm already has System Center for other purposes, or perhaps
the DBA team is already familiar with another third party product. In those
cases, the decision is pretty simple — go with what you have, or go with
what you know, and supplement with the tools and features in the product.

But if you’re like me, you might not have any budget right
now, and only an anemic machine in the corner to run your system. And... I have
a few more requirements than just performance data. I want a system that does it
all, from one location.

So what I’m going to do is use the data I collected in
that MAPS tool
evaluation I wrote about some time ago. You can read that entire article
here, but the upshot is that the Microsoft Assessment and Planning Solution
Accelerator (MAPS) is a free tool from Microsoft that will run across a series
of IP addresses, lists of servers, or even an Active Directory OU to find
systems that have SQL Server installed — from version 2000 and higher. It
then creates these awesome reports that show all that data, from the version and
edition installed all the way down to the CPU and memory installed on the
Instance.

So I thought I would try and leverage that data. I’ll run
the MAPS tool on my network, and find the Instances of SQL Server I have
installed. I would like to store all that data in a single location, so I
decided to do some research to find out where all that data comes from, and
where it ends up.

The Investigation

Before I begin, it’s important to note that I’m
going after data that was generated by a specific version of a tool that is not
a Microsoft SQL Server product. The point is that if I develop a procedure or
process based on that tool, it might break someday if that tool ever changes.
And of course it will change.

So the most important thing to know about the process I’m
describing is the version of the MAPS
tool. The version I used to inventory my SQL Server systems is: 3.2.2315.0.
That’s important because of some decisions I’ll make along the
way.

The main decision I have is where to get my data from. In the
article I wrote on this tool earlier, you’ll see that the tool produces
two reports when it is run for a SQL Server inventory, one in Microsoft Word and
the other in Microsoft Excel.

The first approach I thought about what extracting the data from
the Excel spreadsheet. I considered the Word document; but it only shows things
like the number of versions and some other high-level data. Great for reporting
what we have installed to the boss, but not as useful for the kind of detailed
data I am after.

As I reviewed the Excel spreadsheet the tool makes, I found that
it contains a lot of useful information, including:

SQL Server Component Name

Number of instances

Computer Name

SQL Server Instance Name

SQL Server Product Name

SQL Server Version Number

SQL Server Service Pack

SQL Server Edition

Clustered?

Language

Machine Type

SQL Server Sub-Directory

SQL Service State

SQL Service Start Mode

Current Operating System

Operating System Service Pack Level

Operating System Architecture Type

Number of Processors

Number of Cores

Number of Logical Processors CPU

System Memory (MB)

Logical Disk Drive Name

Logical Disk Size (GB)

Logical Disk Free Space (GB)

That’s quite a lot of information about a system, and I
have a lot of choices about how to get the data. I can store the Excel files
after each evaluation on my “Central Management Server” and then run
a query from SQL Server to attach directly to the spreadsheet, I can import the
data from the spreadsheet using SQL Server Integration Services, or I can export
the data to a flat-text file in Excel and then import the data with BCP. I can
even use PowerShell to open the spreadsheet, open SQL Server, and transfer the
data that way.

But I began to wonder about the fact that the MAPS tool installs
SQL Server Express edition when it runs. Perhaps that’s where the data is
stored for all those spreadsheets and Word documents.

And in fact it is. I opened the SQL Server Configuration
Management tool on my MAPS system, and there was the name of the Instance
— MAPS. So I connected to that database using SQL Server Management Studio
(SSMS) and began to explore. I found the name of the database I installed when I
set up the MAPS product, and began to explore the relationships in the tables
there.

NOTE

This is always a dangerous prospect. Number one, I’m looking directly at base tables here in another product, which are not documented. Since they are not documented, they can change at any time, with any new service pack or release, and that’s their right to do so. I can’t complain a bit if they do. I also don’t know the structure well, so if I based a decision on a value, I need to try in every way I can to ensure that the value is accurate. Although it might have a number or set of characters, you can’t always be certain that they mean what you think. With all of those caveats, you have been warned.

I normally always start with the views on a system when I want
to look at the data it contains. I don’t normally use the stored
procedures, since they are code and technically belong to the vendor, and many
times they not only return data, but insert, delete or update data as well. I
want to ensure that I’m in a “read only” mode.

The views I found in this database didn’t help me all that
much. In fact, many of them returned no data at all. That makes sense, since the
MAPS tool has the ability to evaluate everything from Microsoft Office to Vista
and even Virtualization.

So that left me with the tables. What I did to evaluate them was
to use the Database Diagram feature in SQL Server 2008 to pull in all of the
tables into a diagram, and that showed me the links that the Primary and Foreign
keys had. I then did a SELECT from each table with a TOP 10 limit to see which
ones had data.

Armed with that knowledge, I began to pull out the data that I
wanted to see.

The Tables

What I found in my discoveries was that there was one main table
that seems to store all of the data the other primary tables link to. I also
found that some of the columns contained pointers to other data using a numeric
system, so I excluded that data for the time being. For instance, in the table
that shows the installed software on the machine, there’s a column called
install_state, which is numeric. I don’t know what those mean, so I did
not include them.

But what I was left with was a rich selection of data —
very useful for my monitoring solution, so that I can find out things like
service pack numbers, CPU counts and so on. I can even find information about
drive space, although it isn’t always easy to find out the unit the space
is stored in.

The way I documented this information was in a grid, showing the
table name, the information I found that it stored, and how it was linked to
other tables. Here is that grid, and then in the section that follows I’ll
show you a few of the queries I used to get the information I wanted as an
example. I won’t show all columns, since some of them were empty, and
others had those numeric or code references. If you’re interested in doing
the same thing, you can install the MAPS tool and then perform an inventory on
your own systems. Then you can explore more of these columns.

Table Name

Columns

Data it Shows

Related Table(s)

Key

Devices

device_number

ad_dns_host_name

ad_domain_name

ad_fully_qualified_domain_name

bios_manufacturer

bios_name

bios_release_date

bios_serial_number

bios_version

boot_device

boot_rom_supported

build_number

computer_system_name

csd_version

current_language

dns_host_name

free_physical_memory

free_virtual_memory

last_bootup_time

local_datetime

model

number_of_processors

number_of_users

operating_system

operating_system_service_pack

organization

os_architecture

os_caption

os_install_date

os_manufacturer

roles

service_pack_major_version

service_pack_minor_version

smbios_asset_tag

system_device

system_directory

system_drive

total_physical_memory

workgroup

This is the main table, and it shows multiple items such as the

sql_assessments

physical_memory

processors

disk_drives

logical disks

disk_partitions

services

services_details

device_number

sql_assessments

device_number

clustered

create_datetime

dns_host_name

instanceid

iswow64

language

servicename

skuname

splevel

sqlservicetype

version

Shows SQL Server specific information

devices

device_number

physical_memory

device_number

capacity

create_datetime

data_width

device_locator

speed

tag

total_width

Memory Chips installed in the system

devices

device_number

processors

device_number

address_width

create_datetime

current_clock_speed

data_width

description

device_id

l2_cache_size

l2_cache_speed

l3_cache_size

l3_cache_speed

manufacturer

max_clock_speed

name

number_of_cores

number_of_logical_processors

processor_id

revision

version

Processor hardware information

devices

device_number

disk_drives

device_number

bytes_per_sector

capability_descriptions

caption

compression_method

create_datetime

default_block_size

description

device_id

interface_type

manufacturer

media_loaded

media_type

model

name

partitions

pnp_device_id

scsi_bus

scsi_logical_unit

scsi_port

scsi_target_id

sectors_per_track

signature

size

total_cylinders

total_heads

total_sectors

total_tracks

tracks_per_cylinder

Physical disk drive information

devices

device_number

logical disks

device_number

caption

compressed

create_datetime

description

device_id

file_system

free_space

name

quotas_disabled

quotas_incomplete

quotas_rebuilding

size

supports_disk_quotas

supports_file_based_compression

volume_name volume_serial_number

Logical Disk Drives information

devices

device_number

services

device_number

caption

create_datetime

description

desktop_interact

display_name

name

path_name

start_mode

start_name

started

state

The Windows Services that are installed and their states.

devices

device_number

products

device_number

caption

create_datetime

description

install_date

install_location

name

vendor

version

Major software packages installed on this system.

devices

device_number

Sample Queries

Right now I’m leaving the data in the MAPS tables, and
just using these queries when I need them. In the future, I’ll pull the
data out and store it in my own tables, and then refresh the data in my
“Central Management Server” from time to time for the data space,
drive space and so on. In fact, I’ll probably just use the Management Data
Warehouse feature for that data.

But in any case, these queries are examples of what I can get
out of this database — very handy! There is much more here, of course, but
this will demonstrate a little of what you can find out.

InformIT Articles and Sample Chapters

Rich Schiesser has a great tutorial on inventorying your hardware in the (now defunct) InformIT
IT Management Reference Guide. You can find
the full installment still online with this direct link, however.