Populating Management Studio from SCOM

Technorati Tags: ,

Wrote this at last job with the help of SCOM Gun David Murphy.  Does what it says on the tin. Having all your SQL Servers populated in Management Studio isn’t as important in >=2005 as it was prior, because you can connect / browse etc with having to register servers which wasn’t possible <=2000.

But it is useful to have servers registered / grouped together by version / environment especially given the >= 2008 Management Studio feature that allows to run the same query against multiple registered servers in the same group.

Its a bit rough and ready (a bit like) , have to change params manually (“Change these values”) section, for the environment (Prod, Dev etc) it depends on the naming standard , but wanted to get something out there.

Got the idea from the excellent post below from Kevin Holman that documents some useful SCOM queries / Schema.

http://blogs.technet.com/b/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx

# SSMSFromSCOM.ps1
# Populates SSMS Registered Servers from SCOM query
# See “Change these values” section for variablisation
# Assumes : SQL Server 2008 Management Studio installed
#           Perms to SCOM Database
#           Read Access to AD
#        Windows 7 Admin tools installed

# Add Libs required for the SQLServer Provider
Import-Module ActiveDirectory
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

set-psdebug –strict

  #1. Function to get AD Description for Computer Account using ADSI /LDAP
  Function GetADDescription
    {
    Param (
                $ServerName,
                $LDAPSearchOU
           )
          
        $Connection = $LDAPSearchOU
        $AD = [adsi] $Connection
        $searcher = new-object System.DirectoryServices.DirectorySearcher($AD)
        $SearchFilterString = “(&(objectClass=Computer)(cn=” + $ServerName + “))”

        $searcher.filter = $SearchFilterString
        $Computers = $searcher.findall()
       
        If ($Computers.Count -eq 0)
           {
                $RetString =  “AD Description = ” + $ServerName + ” not found in ” + $Connection
                Return $RetString
        Exit
           }
        Else
            {
                     ForEach($Server in $Computers)
                {
                   
                    $RetString = “AD Description = ” + $Server.Properties.description
                    Return $RetString
                }
            }       
    }

# Change these values    
    $SCOMServer = “SCOMSQLServer”
    $SCOMDB = “OperationsManager”
    $GlobalLDAPSearch = “LDAP://OU=ComputerAccount,DC=SomeDomain,DC=com,DC=au”
    $TopLevelFolder = “AllServersSCOM”
    $CSVOutputFile = “c:\temp\SCOMServerlist.csv”
    $ServerListFileName = “c:\temp\SCOMServerList.txt”
# End Change These Values

# Change to the default Folder (Local Server Groups)
CD ‘SQLSERVER:\sqlregistration\Database Engine Server Group’

# ie all the Folders / Items under the root
$Folders = get-childitem
$FolderExists = 0

# Roll through all Folders
ForEach($Item in $Folders)
{
    # If found delete it
    If ($Item.Name -eq $TopLevelFolder)
    {
        Write-Host “!!! ” $TopLevelFolder ” already exists, Deleting …”
        remove-item $TopLevelFolder -Recurse -Force
        $FolderExists = 1
        Break
    }
}

#Create the folder and set description change into it
new-item $TopLevelFolder
cd $TopLevelFolder

#Environment Folders
new-item “Environment”
    cd “Environment”
    new-item “Production”
    new-item “Development”
    new-item “Staging”
Set-Location ..

# Version specific folders
new-item “SQLVersion”
Set-Location “SQLVersion”
    new-item “SQL2000”
    new-item “SQL2005”
    new-item “SQL2008”
    new-item “SQL2008R2”
Set-Location ..

# Get List of SQL Servers from SCOM in a Datatable – returns 2 fields ServerName and Version
$SQLQuery = “SELECT
            ServerName=[ConnectionString_B87E82BC_1A85_4DE3_330A_13133CF5F9C3],
            Version = Version_B87E82BC_1A85_4DE3_330A_13133CF5F9C3,
            ServicePack = CASE [ServicePackVersion_B87E82BC_1A85_4DE3_330A_13133CF5F9C3]
                                WHEN ‘8.00.2039’ THEN ‘SP4’
                                WHEN ‘0’ THEN ‘RTM’
                                ELSE ‘SP’ + [ServicePackVersion_B87E82BC_1A85_4DE3_330A_13133CF5F9C3]
                            END,
            DBCount = DatabaseCount.DBCount               
            FROM OperationsManager.dbo.[MTV_DBENGINE] WITH(NOLOCK)
            LEFT OUTER JOIN
            (
            — Crazyness to get rid of instance name if default instance
            SELECT     PrincipalName = CASE PATINDEX(‘%;MSSQLServer%’, bme.Path)
                                        WHEN 0 THEN SUBSTRING (bme.Path, 1, PATINDEX( ‘%.%’, bme.Path)-1) + ‘\’ + SUBSTRING(bme.Path, PATINDEX(‘%;%’, bme.Path) + 1, 1000)
                                        ELSE SUBSTRING (bme.Path, 1, PATINDEX( ‘%.%’, bme.Path)-1)
                                       
                                    END,
                    DBCount = COUNT(*)
            FROM BaseManagedEntity bme WITH(NOLOCK)
                        LEFT JOIN ManagedType mt WITH(NOLOCK) ON mt.ManagedTypeID = bme.BaseManagedTypeID
            WHERE bme.IsDeleted = 0
            AND mt.TypeName = ‘Microsoft.SQLServer.Database’
            — Assume we don’t want to count ms supplied / system dbs
            AND Name NOT IN (‘DBA’, ‘model’, ‘master’, ‘msdb’, ‘tempdb’,’ReportServerTempDB’, ‘pubs’, ‘Northwind’, ‘AdventureWorks’, ‘AdventureWorksDW’ )
            — GROUP BY SUBSTRING (bme.Path, 1, PATINDEX( ‘%.%’, bme.Path)-1)
            GROUP BY  CASE PATINDEX(‘%;MSSQLServer%’, bme.Path)
                                        WHEN 0 THEN SUBSTRING (bme.Path, 1, PATINDEX( ‘%.%’, bme.Path)-1) + ‘\’ + SUBSTRING(bme.Path, PATINDEX(‘%;%’, bme.Path) + 1, 1000)
                                        ELSE SUBSTRING (bme.Path, 1, PATINDEX( ‘%.%’, bme.Path)-1)
                                       
                                    END
            ) AS DatabaseCount
            ON [ConnectionString_B87E82BC_1A85_4DE3_330A_13133CF5F9C3]  = DatabaseCount.PrincipalName
            WHERE Edition_B87E82BC_1A85_4DE3_330A_13133CF5F9C3 NOT IN (‘Express Edition’, ‘Windows Internal Database’, ‘Windows Internal Database (64-bit)’)
            AND ([ConnectionString_B87E82BC_1A85_4DE3_330A_13133CF5F9C3] NOT IN (‘ShonkyServer1′,’ShonkyServer2’) )

# Build Query and Connection String
$ConnectionString = “Server=” + $SCOMServer + “;Database=” + $SCOMDB + “;Integrated Security=True;Application Name=ShonkyPowershell”
#Create Dataset and Adapter
$dataset = new-object “System.Data.DataSet” “MyDataSet”
$erroractionpreference = “Stop”
Write-Host “Connecting to ” $SCOMServer ” SQL Server”
$DataAdapter = new-object “System.Data.SqlClient.SqlDataAdapter” ($SQLQuery, $ConnectionString)
$RowCount = $DataAdapter.Fill($dataset)

Write-Host “Processing ” $RowCount ” Servers”
Write-Host “”

#ie each SQL Server
$arrServerDetails = @()
ForEach($Row in $dataset.Tables[0])
    {
        # ie just the first column as we only have one in our dataset poindexter
        $RegSQLServerName = $Row[“ServerName”].ToUpper()
        $SQLVersionNumber = $Row[“Version”].ToUpper()
       
        # need to do this cos i think powershel sql server provider doesn’t like instances names as the delimeter is \ !       
        # and 2 \\ cos its a delimiter in Powershell
        $RegSQLDisplayName = $RegSQLServerName -replace (“\\”, “_”)
        $RegConnectionString = “Server=” + $RegSQLServerName  + “;integrated security=true”
      
        # Trim off Instance name to get NETBIOS name if a named instance
        If ($RegSQLServerName.contains(“\”) -eq $True)
            {
                $NETBIOSName = $RegSQLServerName.Substring(0, $RegSQLServerName.IndexOf(“\”))
            }
        Else
            {
                $NETBIOSName = $RegSQLServerName
            }
       
        # Get last 2 chars, ie DE = Dev , ST = Staging
        $EnvironmentSuffix = $NETBIOSName.substring($NETBIOSName.Length – 2, 2)
       
        Switch ($EnvironmentSuffix)
        {
            “DE” {$EnvironmentFolder=”Development”}
            “ST” {$EnvironmentFolder=”Staging”}
            Default {$EnvironmentFolder=”Production”}
        }
               
        # Change to environment Folder (assumes created in earlier step)
        Get-Location       
        cd “Environment”
        cd $EnvironmentFolder
       
        $MajorVersion = $SQLVersionNumber.substring(0, $SQLVersionNumber.IndexOf(“.”))
       
        # Get friendly name for SQL Version Number
        Switch ($MajorVersion)
        {
            “8” {$VersionFolder=”SQL2000″}
            “9” {$VersionFolder=”SQL2005″}
            “10” {
                    Switch ($SQLVersionNumber.SubString(0, 5) )
                        {
                            “10.50” {$VersionFolder=”SQL2008R2″}
                            default {$VersionFolder=”SQL2008″}
                        }
                  }
           
            default {$VersionFolder=”Unknown”}
        }
       
       
        # 1. Do Environment Stuff
        $ConnectionString = “Server=” + $RegSQLServerName + “;integrated security=true”
        Write-Host “Registering ” $RegSQLServerName ” (NETBIOS Name = ” $NETBIOSName “, in Folder = Environment\” $EnvironmentFolder “)”
        new-item $RegSQLDisplayName   -itemtype registration -Value $ConnectionString
       
        #Get it back so we can add description (seems a bit shonky)
        # $ADDescription = GetADDescription ($NETBiosName, $GlobalLDAPSearch)
        $ADDescription = GetADDescription $NETBIOSName $GlobalLDAPSearch
        # Write-Host “`t” $ADDescription       
        $Item = Get-ChildItem  $RegSQLDisplayName 
        $Item.Description=$ADDescription
       
        #Back to root
        Set-Location ..
        Set-Location ..
       
       
        # 2. Do Version stuff
        cd “SQLVersion”
        cd $VersionFolder

        Write-Host “Registering ” $RegSQLServerName ” (NETBIOS Name = ” $NETBIOSName “, in Folder = SQLVersion\” $VersionFolder “)”
        new-item $RegSQLDisplayName   -itemtype registration -Value $ConnectionString
       
        $Item = Get-ChildItem  $RegSQLDisplayName 
        $Item.Description=$ADDescription
       
        #Back to root
        Set-Location ..
        Set-Location ..
       
            
        # For CSV output Summary
        $CSVrow = “” | Select  SQLServerName,         `
                            ADDescription ,         `
                            MajorVersion, 
                            ServicePack,  `
                            Environment, `
                            DBCount

        $CSVRow.SQLServerName = $RegSQLServerName
        $CSVRow.ADDescription = $ADDescription
        $CSVRow.MajorVersion = $VersionFolder
        $CSVRow.ServicePack = $row[“ServicePack”].ToUpper()
        $CSVRow.Environment =  $EnvironmentFolder
        $CSVRow.DBCount = $row[“DBCount”]
       
        $arrServerDetails+=$CSVRow
                       
    } # ForEach($Row in $dataset.Tables[0])
   

Write-Host “Writing List of SQL Servers to file = ” $ServerListFileName
$dataset.Tables[0] | Select-Object ServerName | Out-File $ServerListFileName

Write-Host “Writing CSV summary to file = ” $CSVOutputFile
$arrServerDetails | Export-CSV -Path $CSVOutputFile -NoTypeInformation

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s