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 

Also recommended: