Blog Post

Building a SQL Server Inventory – Part 1

,

Ok folks, you asked for it. Well, a few of you did, anyway. So today I’m going to share with you my script for gathering server-level information as part of my SQL Server inventory. Now, I want to make one thing perfectly clear before we begin, and I can’t emphasize this enough: I am not a PowerShell guru. I know one of my resolutions this year was to learn PowerShell in a month of lunches, but I haven’t actually done that yet. So please, as you go through this, try to temper your disappointment, m’kay?

Credits

You know who is a PowerShell guru? Allen White. In fact, my script is based heavily on Allen’s inventory script on Simple-Talk. If you haven’t already seen it, I highly recommend checking out the entire article. I’ve made some changes to Allen’s base script, to suit my own needs. Whereas Allen writes his output to a csv file, I wanted to load directly into my inventory database. Allen also reads in his list of servers to inventory from a file, I keep mine in a master table, also in my inventory database.

Tools you’ll need

In order to load your inventory data directly into SQL Server, you’ll need to download a couple of PowerShell functions.

Name: Write-DataTable.ps1

Author: Chad Miller

http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

Loads data into from a datatable object into SQL Server tables

Name: Out-DataTable.ps1

Author: Chad Miller

http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

Formats input into a datatable object which can then be imported into SQL Server using Write-DataTable.

Permissions required

I use a domain account with admin rights to the target servers I’m inventorying. If you don’t have access to such an account, for example if you have a separate domain account as admin to each box, or if you have access to local admin accounts only, you can specify credentials in your WMI connection.

You’ll also need SQL Server login with VIEW SERVER STATE on your target servers to query sys.dm_os_cluster_nodes. I’ll explain more about why this is necessary shortly.

The moment you’ve all been waiting for – The script

Parameters

My script accepts 4 parameters:

  • Inventory database instance
  • Inventory login
  • Inventory password (I use the same login to collect my info and store it)
  • Environment (Which environment do I want to inventory?)

A word of explanation about the Environment parameter. As I mentioned earlier, I store my master instance list in a table inside my inventory database. That table holds all instances across all of my environments, Production, Development, etc., and the environment for each instance is also stored in this master table. When I run this script I use this parameter to specify which environment I want to inventory.

Clean out tables pre-run

This script performs a full load every time, so my first step is simply to clear out any existing data.

$connection = new-object system.data.sqlclient.sqlconnection( `
    "Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
##### Clean out all tables pre-run. #####
$query = "select server_name_short into #cmm from Servers where Environment in ('$ssi_env') ;
      DELETE FROM Server.OS_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.Memory_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.Disk_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.PhysicalNodes WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.System_Info WHERE server_name in (Select server_name_short from #cmm);"
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()

Get list of servers to inventory

The next step in the process is to read the master table to get the list of servers to inventory.  Then we’ll step through them and for each one we’ll check and make sure it’s online and if it is, we’ll execute the get-wmiinfo function.

##### Get the list of servers to inventory #####
$query = "SELECT DISTINCT server_name as ServerName, server_name_short as ServerNameShort
      , instance_name as InstanceName, instance_name_short as InstanceNameShort
        FROM Servers WHERE Environment IN ('$ssi_env')"
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()
##### For every server gather data #####
while($reader.Read()) {
    ##### See if the server is alive #####
    $svr = $reader['ServerName']
    $svrshort = $reader['ServerNameShort']
    $inst = $reader['InstanceName']
    $instshort = $reader['InstanceNameShort']
    $result = Get-WMIObject -query "select StatusCode from Win32_PingStatus where Address = '$svr'"
       $responds = $false
    if ($result.statuscode -eq 0) {
        $responds = $true
    }
    ##### If it is alive ... #####
    If ($responds) {
        get-wmiinfo $svr $svrshort $inst $instshort
    }
    else {
              # Let the user know we couldn't connect to the server
              Write-Output "$svr does not respond"
       }
}

Get-wmiinfo Function

The last part of the script is to actually gather the information.

First we’ll go after the ComputerSystem info.  I originally used NumberOfProcessors, NumberOfLogicalProcessors from Win32_ComputerSystem to get the processors and cores, but this wasn’t always accurate in all systems.  I’ve found a better option was to query win32_processor as described here by Richard Fryar, and from whom I borrowed this section of code from.  Although he mentions having problems with this on Windows 2008 servers, I’ve had good results.  YMMV.

    ##### Get the ComputerSystem Info #####
    $ssi_table="Server.System_Info"
    $processors = get-wmiobject -computername $server win32_processor
    if (@($processors)[0].NumberOfCores)
        {
            $cores = @($processors).count * @($processors)[0].NumberOfCores
        }
        else
        {
            $cores = @($processors).count
        }
        $sockets = @(@($processors) |
        % {$_.SocketDesignation} |
        select-object -unique).count;
    $dt=Get-WMIObject -query "select * from Win32_ComputerSystem" -computername $server | select @{n="ServerName";e={$servershort}}, Model, Manufacturer, Description, DNSHostName, Domain, DomainRole, PartOfDomain, @{n="NumberofProcessors";e={$sockets}}, @{n="NumberofCores";e={$cores}}, SystemType, TotalPhysicalMemory, UserName, Workgroup | out-datatable
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

I wanted to list all of the nodes that make up a cluster.  So far, I haven’t found a way to do this via WMI.  (If anyone knows of one, I’d love to hear it.)  My workaround was to first see if the server name I was connecting to (the cluster name) was the same as the physical name according to Win32_ComputerSystem.  If they were different, I knew it was a cluster and I had to query sys.dm_os_cluster_nodes in SQL.

##### ... and the Physical Node Info #####
    $ssi_table = "Server.PhysicalNodes"
    $conn = "server=$instance;database=master;User ID=$ssi_userid;password=$ssi_passwd"
    $s=Get-WMIObject -query "select * from Win32_ComputerSystem" -ComputerName $server | select name
    if ($s.name -ne $servershort) {
        $query = "select `'$servershort`' as server_name, NodeName from sys.dm_os_cluster_nodes"
          $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
          $dat = new-object System.Data.DataTable
          $da.fill($dat) | out-null
          Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dat -Username $ssi_userid -Password $ssi_passwd
    } else {
        $query = "select `'$servershort`' as server_name, `'$servershort`' as NodeName"
          $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
          $dat = new-object System.Data.DataTable
          $da.fill($dat) | out-null
          Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dat -Username $ssi_userid -Password $ssi_passwd
    }

The remainder of the function gathers the OS, Memory, and Logical Disk info.  These are all straightforward WMI queries.

##### ... and the OperatingSystem Info #####
$ssi_table="Server.OS_Info"
$dt=Get-WMIObject Win32_OperatingSystem -computername $server | select @{n="ServerName";e={$servershort}}, Name, Version, OSLanguage, OSProductSuite, OSType, ServicePackMajorVersion, ServicePackMinorVersion |out-datatable
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### ... and the PhysicalMemory Info #####
$ssi_table="Server.Memory_Info"
$dt=Get-WMIObject -query "select * from Win32_PhysicalMemory" -computername $server | select @{n="ServerName";e={$servershort}},Name, Capacity, DeviceLocator, Tag | out-datatable
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
##### ... and the LogicalDisk Info #####
$ssi_table="Server.Disk_Info"
$dt=Get-WMIObject -query "select * from Win32_Volume where DriveType=3 and not name like '%?%'" -computername $server |select @{n="ServerName";e={$servershort}},Name, Label, DriveLetter, Capacity, FreeSpace | out-datatable
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

The complete script

So here’s the complete script.  Give it a whirl and let me know what you think.  If you come up with some improvements, I’d love to hear them, too.

# get-ServerInfo.ps1
# usage: ./get-ServerInfo.ps1 <Inventory Instance name> <Inventory Login> <Inventory Password> <Environment>
# Collects all Server information into MyInventory database
# Including:
#     Server.System_Info;
#     Server.OS_Info;
#     Server.Memory_Info;
#     Server.Disk_Info;
param(
    [string]$ssi_instance=$null,
    [string]$ssi_userid=$null,
    [string]$ssi_passwd=$null,
    [string]$ssi_env=$null
     )
. .\write-datatable.ps1
. .\out-datatable.ps1
. .\LibraryMSCS.ps1
$ssi_database = "MyInventory"
Import-Module FailoverClusters;
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
function get-wmiinfo ($server, $servershort, $instance, $instanceshort) {
    ##### Get the ComputerSystem Info #####
    $ssi_table="Server.System_Info"
    $processors = get-wmiobject -computername $server win32_processor
    if (@($processors)[0].NumberOfCores)
        {
            $cores = @($processors).count * @($processors)[0].NumberOfCores
        }
        else
        {
            $cores = @($processors).count
        }
        $sockets = @(@($processors) |
        % {$_.SocketDesignation} |
        select-object -unique).count;
    $dt=Get-WMIObject -query "select * from Win32_ComputerSystem" -computername $server | select @{n="ServerName";e={$servershort}}, Model, Manufacturer, Description, DNSHostName, Domain, DomainRole, PartOfDomain, @{n="NumberofProcessors";e={$sockets}}, @{n="NumberofCores";e={$cores}}, SystemType, TotalPhysicalMemory, UserName, Workgroup | out-datatable
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### ... and the Physical Node Info #####
    $ssi_table = "Server.PhysicalNodes"
    $conn = "server=$instance;database=master;User ID=$ssi_userid;password=$ssi_passwd"
    $s=Get-WMIObject -query "select * from Win32_ComputerSystem" -ComputerName $server | select name
    if ($s.name -ne $servershort) {
        $query = "select `'$servershort`' as server_name, NodeName from sys.dm_os_cluster_nodes"
          $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
          $dat = new-object System.Data.DataTable
          $da.fill($dat) | out-null
          Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dat -Username $ssi_userid -Password $ssi_passwd
    } else {
        $query = "select `'$servershort`' as server_name, `'$servershort`' as NodeName"
          $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
          $dat = new-object System.Data.DataTable
          $da.fill($dat) | out-null
          Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dat -Username $ssi_userid -Password $ssi_passwd
    }
    ##### ... and the OperatingSystem Info #####
    $ssi_table="Server.OS_Info"
    $dt=Get-WMIObject Win32_OperatingSystem -computername $server | select @{n="ServerName";e={$servershort}}, Name, Version, OSLanguage, OSProductSuite, OSType, ServicePackMajorVersion, ServicePackMinorVersion |out-datatable
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### ... and the PhysicalMemory Info #####
    $ssi_table="Server.Memory_Info"
    $dt=Get-WMIObject -query "select * from Win32_PhysicalMemory" -computername $server | select @{n="ServerName";e={$servershort}},Name, Capacity, DeviceLocator, Tag | out-datatable
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
    ##### ... and the LogicalDisk Info #####
    $ssi_table="Server.Disk_Info"
    $dt=Get-WMIObject -query "select * from Win32_Volume where DriveType=3 and not name like '%?%'" -computername $server |select @{n="ServerName";e={$servershort}},Name, Label, DriveLetter, Capacity, FreeSpace | out-datatable
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
} # get-wmiinfo
$connection = new-object system.data.sqlclient.sqlconnection( `
    "Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
##### Clean out all tables pre-run. #####
$query = "select server_name_short into #cmm from Servers where Environment in ('$ssi_env') ;
      DELETE FROM Server.OS_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.Memory_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.Disk_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.PhysicalNodes WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.System_Info WHERE server_name in (Select server_name_short from #cmm);"
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()
##### Get the list of servers to inventory #####
$query = "SELECT DISTINCT server_name as ServerName, server_name_short as ServerNameShort
      , instance_name as InstanceName, instance_name_short as InstanceNameShort
        FROM Servers WHERE Environment IN ('$ssi_env')"
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()
##### For every server gather data #####
while($reader.Read()) {
    ##### See if the server is alive #####
    $svr = $reader['ServerName']
    $svrshort = $reader['ServerNameShort']
    $inst = $reader['InstanceName']
    $instshort = $reader['InstanceNameShort']
    $result = Get-WMIObject -query "select StatusCode from Win32_PingStatus where Address = '$svr'"
       $responds = $false
    if ($result.statuscode -eq 0) {
        $responds = $true
    }
    ##### If it is alive ... #####
    If ($responds) {
        get-wmiinfo $svr $svrshort $inst $instshort
    }
    else {
              # Let the user know we couldn't connect to the server
              Write-Output "$svr does not respond"
       }
}

Edit: Here’s the code to create the Server tables.

 USE [MyInventory]
GO
CREATE TABLE [dbo].[Servers](
[instance_name] [nvarchar](128) NOT NULL,
[instance_name_short] [nvarchar](128) NOT NULL,
[server_name] [nvarchar](128) NOT NULL,
[server_name_short] [nvarchar](128) NOT NULL,
[Environment] [varchar](7) NOT NULL,
CONSTRAINT [PK_Server_Instance] PRIMARY KEY CLUSTERED
(
[instance_name_short] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE SCHEMA [Server] AUTHORIZATION [MyInventoryLogin]
GO
CREATE TABLE [Server].[PhysicalNodes](
[Server_Name] [nvarchar](128) NOT NULL,
[Node_Name] [nvarchar](128) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [Server].[System_Info](
[Server_Name] [nvarchar](128) NOT NULL,
[Model] [varchar](200) NULL,
[Manufacturer] [varchar](50) NULL,
[Description] [varchar](100) NULL,
[DNSHostName] [varchar](30) NULL,
[Domain] [varchar](30) NULL,
[DomainRole] [smallint] NULL,
[PartOfDomain] [varchar](5) NULL,
[NumberOfProcessors] [smallint] NULL,
[NumberOfCores] [smallint] NULL,
[SystemType] [varchar](50) NULL,
[TotalPhysicalMemory] [bigint] NULL,
[UserName] [varchar](50) NULL,
[Workgroup] [varchar](50) NULL,
CONSTRAINT [PK_Server_Name] PRIMARY KEY CLUSTERED
(
[Server_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Server].[Memory_Info](
[Server_Name] [nvarchar](128) NOT NULL,
[Name] [varchar](50) NULL,
[Capacity] [bigint] NULL,
[DeviceLocator] [varchar](20) NULL,
[Tag] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [Server].[OS_Info](
[Server_Name] [nvarchar](128) NOT NULL,
[OSName] [varchar](200) NULL,
[OSVersion] [varchar](20) NULL,
[OSLanguage] [varchar](5) NULL,
[OSProductSuite] [varchar](5) NULL,
[OSType] [varchar](5) NULL,
[ServicePackMajorVersion] [smallint] NULL,
[ServicePackMinorVersion] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [Server].[Disk_Info](
[Server_Name] [nvarchar](128) NOT NULL,
[Disk_Name] [varchar](50) NULL,
[Label] [varchar](50) NULL,
[DriveLetter] [varchar](5) NULL,
[Capacity] [bigint] NULL,
[FreeSpace] [bigint] NULL,
[Run_Date] [date] NULL
) ON [PRIMARY]
GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-23

1,567 reads

Technical Article

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-17

1,530 reads

Technical Article

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-13

360 reads