Powershell, Monitoring and the Central Management Server
/****** Object: Table [dbo].[ServerInfo] Script Date: 05/29/2009 15:46:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServerInfo](
[ServerID] [int] IDENTITY(1,1) NOT NULL,
[LastUpdated] [datetime] NULL,
[Server] [varchar](50) NULL,
[Instance] [varchar](50) NULL,
[Version] [varchar](50) NULL,
[Edition] [varchar](50) NULL,
[ServicePack] [varchar](50) NULL,
[TimeStarted] [datetime] NULL,
[BackupDirectory] [varchar](250) NULL,
[defaultfile] [varchar](250) NULL,
[defaultlog] [varchar](250) NULL,
[installdatadirectory] [varchar](250) NULL,
[osversion] [varchar](50) NULL,
[physicalmemory] [int] NULL,
[errorlogpath] [nvarchar](250) NULL,
CONSTRAINT [PK_ServerInfo] PRIMARY KEY CLUSTERED
(
[ServerID] 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
SET ANSI_PADDING OFF
GO
I have been looking at gathering basic information in my environment using powershell. There is some good stuff out there. I have been using the CMS server registered server function to query multiple servers but the problem is you can only query against them and not write to your CMS without using linked servers.
So I looked at some of the projects on Codeplex – Buck Woody’s SQLCMS got me interested in extending the CMS datawarehouse.
Sqlserverpedia has some good info on building a CMDB and extending the CMS datawarehouse too
Then I took a look at Chad Millers SQLPSX project he has written some excellent functions and has a complete Powershell “base” on codeplex. I learnt a lot from looking throught the scripts and functions.
Using the SQLPSX library and functions, I wrote a powershell script that queries each server in your Central Management Server by group or as a whole returning basic information like version, Service pack, default backupfolder, OS version, Memory and path location information and merges it into a table so it can be run repeatedly.
*use at your own risk of course 
**this requires the SQLPSX library – see above
[void][reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)
##Point to library files
$scriptRoot = Split-Path (Resolve-Path $myInvocation.MyCommand.Path)
#. $scriptRoot\LibrarySmo.ps1
Set-Alias Test-SqlConn $scriptRoot\Test-SqlConn.ps1
##Define variables
#CMS Server
$sqlServer = “YOURSERVERNAMEHERE”
$dbName = “msdb”
$ServerList = New-Object System.Collections.ArrayList
##CMS server Group
$serverGroup = “CMS SERVER GROUP NAME OR % FOR ALL”
##CMS database
$dbTARGET = “DATABASE WITH THE TABLE _SCRIPT AT THE BOTTOM”
##CMS Table
$tblServerInfo = “TABLENAME”
#######################
function Get-SqlList
{
Get-SqlData $SQLServer $dbname “SELECT DISTINCT
s.server_name as server
FROM dbo.sysmanagement_shared_server_groups_internal g
INNER JOIN dbo.sysmanagement_shared_registered_servers_internal s ON g.server_group_id = s.server_group_id
where g.NAME LIKE ‘$serverGroup’
” | foreach {$_.Server} | Test-SqlConn |
foreach { $ServerList.Add(“$_”) > $null }
#Write-Host $ServerList
}# Get-SqlList
Get-SqlList
while ($ServerList.Count -gt 0)
{
$server = $ServerList[0]
#Launch Another Thread
#Write-Host $server
## open database connection
$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=$sqlServer;
Initial Catalog=$dbtarget; Integrated Security=SSPI”)
$conn.Open()
$cmd = $conn.CreateCommand()
##read servernames and update/insert the table
#get-content C:\Working\SQLCMS\Powershellscripts\servers.txt | foreach {
#$server = $_
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $server
$lastUpdated = Get-Date
$servername = $srv.Name
$instance = $srv.InstanceName
$version = $srv.VersionString
$edition = $srv.Edition
$ServicePack = $srv.ProductLevel
$BackupDirectory = $srv.BackupDirectory
$defaultfile = $srv.DefaultFile
$defaultlog = $srv.DefaultLog
$installdatadirectory = $srv.InstallDataDirectory
$osversion = $srv.OSVersion
$physicalmemory = $srv.PhysicalMemory
$errorlogpath = $srv.ErrorLogPath
$cmd.CommandText = ”
MERGE $tblServerInfo AS target
USING (VALUES (‘$lastupdated’,'$server’
,’$instance’
,’$version’
,’$Edition’
,’$ServicePack’
,’$BackupDirectory
,’$defaultfile’
,’$defaultlog’
,’$installdatadirectory’
,’$osversion’
,’$physicalmemory’
,’$errorlogpath’))
AS src
([LastUpdated]
,[Server]
,[Instance]
,[Version]
,[Edition]
,[ServicePack]
,[BackupDirectory]
,[defaultfile]
,[defaultlog]
,[installdatadirectory]
,[osversion]
,[physicalmemory]
,[errorlogpath])
ON (Target.SERVER = (‘$servername’))
WHEN MATCHED THEN
UPDATE SET lastupdated = (‘$lastupdated’)
,instance = (‘$instance’)
,Version = (‘$version’)
,Edition = (‘$Edition’)
,Servicepack = (‘$ServicePack’)
,BackupDirectory = (‘$BackupDirectory’)
,defaultfile = (‘$DefaultFile’)
,defaultlog = (‘$DefaultLog’)
,installdatadirectory = (‘$InstallDataDirectory’)
,osversion = (‘$OSVersion’)
,physicalmemory = (‘$PhysicalMemory’)
,errorlogpath = (‘$ErrorLogPath’)
WHEN NOT MATCHED THEN
INSERT
([LastUpdated]
,[Server]
,[Instance]
,[Version]
,[Edition]
,[ServicePack]
,[BackupDirectory]
,[defaultfile]
,[defaultlog]
,[installdatadirectory]
,[osversion]
,[physicalmemory]
,[errorlogpath])
VALUES (‘$lastupdated’
,’$server’
,’$instance’
,’$version’
,’$Edition’
,’$ServicePack’
,’$BackupDirectory’
,’$defaultfile’
,’$defaultlog’
,’$installdatadirectory’
,’$osversion’
,’$physicalmemory’
,’$errorlogpath’);”
$cmd.ExecuteNonQuery()
#}
$conn.Close()
#Set the Server as processed
$ServerList.Remove(“$server”)
}
I imagine there is an easier way of doing this but it was fun figuring it out. One thing i wanted to get was the start time of the server. I usually use crdate of tempdb in master but i couldn’t work this into the script. That will be my next step.
Below is the table that the script inserts into
/****** Object: Table [dbo].[ServerInfo] Script Date: 05/29/2009 15:46:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServerInfo](
[ServerID] [int] IDENTITY(1,1) NOT NULL,
[LastUpdated] [datetime] NULL,
[Server] [varchar](50) NULL,
[Instance] [varchar](50) NULL,
[Version] [varchar](50) NULL,
[Edition] [varchar](50) NULL,
[ServicePack] [varchar](50) NULL,
[TimeStarted] [datetime] NULL,
[BackupDirectory] [varchar](250) NULL,
[defaultfile] [varchar](250) NULL,
[defaultlog] [varchar](250) NULL,
[installdatadirectory] [varchar](250) NULL,
[osversion] [varchar](50) NULL,
[physicalmemory] [int] NULL,
[errorlogpath] [nvarchar](250) NULL,
CONSTRAINT [PK_ServerInfo] PRIMARY KEY CLUSTERED
(
[ServerID] 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
SET ANSI_PADDING OFF
GO
