get-tfsserverstatus.ps1 - fetch the configuration bits of your TFS server

It's currently in the collection as get-tfsstatus.ps1 which has caused confusion since it doesn't act like "tf status", but it's intended to run on your app tier and gather some config settings - I use it to confirm the right things have changed when using "tfsadminutil activateat" or "tfsreg" or whatever.

Note that it needs to run on the app tier, as it uses a local registry path to figure out where the TFS bits are installed and then uses a local filesystem path to read the web.config files.

It'll change over time, since right now I haven't done the WMI calls to get the IIS settings (web sites, app pools, etc), although they'll be easy to add, I just need to get around to it :)  It's also ripe for a good bit of refactoring, but it's in the "works for me" category right now :)

Example output (from one of our lab machines):

 AppTierInstallLocation                          : D:\Program Files\Microsoft Visual Studio 9.0 Team Foundation Server\
AppTierMainWebConfigPath                        : D:\Program Files\Microsoft Visual Studio 9.0 Team Foundation Server\W
                                                  eb Services\web.config
AppTierServicesWebConfigPath                    : D:\Program Files\Microsoft Visual Studio 9.0 Team Foundation Server\W
                                                  eb Services\Services\web.config
AppTierMainWebConfigTfsNameUrl                  : https://VSTSNC-020:8080
AppTierMainWebConfigAllSettings                 : {sqlTimeout, commandLogging, TFSNameUrl, traceWriter...}
AppTierServicesWebConfigConnectionString        : Application Name=TeamFoundation;Persist Security Info=False;Initial C
                                                  atalog=TfsIntegration;Data Source=VSTSNC-008;Integrated Security=SSPI
AppTierServicesWebConfigAllSettings             : {ConnectionString, eventingEnabled, DetailedExceptions, emailNotifica
                                                  tionFromAddress...}
DataTierDatabaseConnections                     : {BIS DB, BISDW DB, TeamBuild DB, WIT DB...}
DataTierServiceInterfaces                       : {BuildStoreService, BuildControllerService, IBISEnablement, LinkingPr
                                                  oviderService...}
DataTierAppTierStamp                            : VSTSNC-020
DataTierAppTierStampPingCheck                   : Successful
ServicesReportsServer                           : https://VSTSNC-020/Reports
ServicesReportsServerCheck                      : Successful
ServicesReportsWebService                       : https://VSTSNC-020/ReportServer/ReportService.asmx
ServicesReportsWebServiceCheck                  : Successful
ServicesReportsWebServiceListSecureMethods      : https://VSTSNC-020/ReportServer/ReportService.asmx/ListSecureMethods
ServicesReportsWebServiceListSecureMethodsCheck : Successful
ServicesSharepoint                              : https://VSTSNC-020:80
ServicesSharepointCheck                         : Successful
ServicesSharepointSitesAsmx                     : https://VSTSNC-020:80/_vti_bin/sites.asmx
ServicesSharepointSitesAsmxCheck                : Successful
ServicesSharepointAdmin                         : https://VSTSNC-020:17012/_vti_adm/admin.asmx
ServicesSharepointAdminCheck                    : Successful

The script:

 function runQuery([system.data.sqlclient.SqlConnection] $sqlConn, [string] $query) {
    $sqlCommand = new-object 'system.data.sqlclient.SqlCommand' $query, $sqlConn
    $rdr = $sqlCommand.ExecuteReader()
    while($rdr.Read())
    {
        if ($rdr.FieldCount -eq 1)
        {
            # single value, just return it
            $rdr.GetValue(0)
        }
        else
        {
            # multiple columns, create a custom result object
            $row = new-object psobject
            $row.psobject.typenames[0] = "SqlResultObject"
            for($c = 0; $c -lt $rdr.FieldCount; $c++) 
            { 
                $row | add-member noteproperty $rdr.GetName($c) $rdr.GetValue($c) 
            }
            $row
        }
    }
    $rdr.close();
}

function print-header([string] $header)
{
    write-host ''
    write-host $header
    write-host ('=' * $header.length)
}

$regPath = 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Microsoft Visual Studio * Team Foundation Server (services) - ENU'

$tfsStatus = new-object psobject
$tfsStatus.psobject.typenames[0] = "TfsServerConfigurationObject"

### App Tier
$installLocation = $(get-itemproperty $regPath).InstallLocation
$tfsStatus | add-member noteproperty AppTierInstallLocation $installLocation

$topWebConfigPath = join-path $installLocation 'Web Services\web.config'
$tfsStatus | add-member noteproperty AppTierMainWebConfigPath $topWebConfigPath

$servicesWebConfigPath = join-path $installLocation 'Web Services\Services\web.config'
$tfsStatus | add-member noteproperty AppTierServicesWebConfigPath $servicesWebConfigPath

$topWebConfig = [xml](get-content $topWebConfigPath)
$appSettings = $topWebConfig.configuration.appSettings
$tfsNameUrl = $appSettings.SelectSingleNode('add[@key="TFSNameUrl"]').value
$tfsStatus | add-member noteproperty AppTierMainWebConfigTfsNameUrl $tfsNameUrl
$tfsStatus | add-member noteproperty AppTierMainWebConfigAllSettings $appSettings.add

$servicesWebConfig = [xml](get-content $servicesWebConfigPath)
$appSettings = $servicesWebConfig.configuration.appSettings
$connectionString = $appSettings.SelectSingleNode('add[@key="ConnectionString"]').value
$tfsStatus | add-member noteproperty AppTierServicesWebConfigConnectionString $connectionString
$tfsStatus | add-member noteproperty AppTierServicesWebConfigAllSettings $appSettings.add

### Data Tier
$sqlConn = new-object 'system.data.sqlclient.SqlConnection' $connectionString
$sqlConn.Open()

$dbConnections = runQuery $sqlConn 'select name,dbname,servername,connection from tbl_database'
$tfsStatus | add-member noteproperty DataTierDatabaseConnections $dbConnections

$serviceInterfaces = runQuery $sqlConn 'select name,url from tbl_service_interface'
$tfsStatus | add-member noteproperty DataTierServiceInterfaces $serviceInterfaces

# AT Stamp
$atStamp = runQuery $sqlConn "select value from tbl_registration_extended_attributes where name='ATMachineName'"
$tfsStatus | add-member noteproperty DataTierAppTierStamp $atStamp
$tfsStatus | add-member noteproperty DataTierAppTierStampPingCheck (ping-hostname $atstamp)

$reportsUrl = runQuery $sqlConn "select url from tbl_service_interface where name='BaseReportsUrl'"
$tfsStatus | add-member noteproperty ServicesReportsServer $reportsUrl
$tfsStatus | add-member noteproperty ServicesReportsServerCheck (ping-url $reportsUrl)

# Reports
$reportsService = runQuery $sqlConn "select url from tbl_service_interface where name='ReportsService'"
$tfsStatus | add-member noteproperty ServicesReportsWebService  $reportsService
$tfsStatus | add-member noteproperty ServicesReportsWebServiceCheck (ping-url $reportsService)

$reportsListMethods = $reportsService + '/ListSecureMethods'
$tfsStatus | add-member noteproperty ServicesReportsWebServiceListSecureMethods $reportsListMethods
$tfsStatus | add-member noteproperty ServicesReportsWebServiceListSecureMethodsCheck (ping-url $reportsListMethods)

# Sharepoint
$sharepointUrl = runQuery $sqlConn "select url from tbl_service_interface where name='BaseServerUrl'"
$tfsStatus | add-member noteproperty ServicesSharepoint $sharepointUrl
$tfsStatus | add-member noteproperty ServicesSharepointCheck (ping-url $sharepointUrl)

$sharepointSitesAsmx = $sharepointUrl + '/_vti_bin/sites.asmx'
$tfsStatus | add-member noteproperty ServicesSharepointSitesAsmx $sharepointSitesAsmx
$tfsStatus | add-member noteproperty ServicesSharepointSitesAsmxCheck (ping-url $sharepointSitesAsmx)

$sharepointAdmin = runQuery $sqlConn "select url from tbl_service_interface where name='WssAdminService'"
$tfsStatus | add-member noteproperty ServicesSharepointAdmin $sharepointAdmin
$tfsStatus | add-member noteproperty ServicesSharepointAdminCheck (ping-url $sharepointAdmin)

$sqlConn.Close()

$tfsStatus