PowerShell Remoting and SQL

We needed to get the following information for all of our sites: title, url, environment, database name, owner, etc. in all of our production and non-production environments. The constraints are that (1) each environment has different admin accounts and password (2)how does script know all of our server names? (3) we want to just have 1 script and no manual work (4) how do we also output the information in a SharePoint List, SQL table, and csv file? Possible? Yep!

Due to confidential purposes, I will just skip to methods and some input but can definitely assist if reached out to!

Requirements:

• PowerShell
• Admin Account information
• List of Servers
• Windows Server 2010 – 2013
• SharePoint Server 2010 – 2013
• Microsoft SQL Server 2008 – 2012 with a table where you want to store data

Solution:

1. This is not necessary but we had stored all of our pw/account information in a table and encrypted them to make it secure. An alternative is to read from another file like XML or excel with all your account information. Our PowerShell Script reads both pw/account info.
2. We also had a table already created and our PowerShell Script does a backup, drop,  and recreate table when new data is pulled.
   

   ########################## 1. Back up table in excel first########################################

   Write-host “Backing up farm info table…”

   Try
   {
   $selectAllInfoFromDb = Invoke-sqlCmd -Query “SELECT * FROM TABLE_NAME” -ServerInstance $serverOfDatabase -database $database -username $userNameToLogin -password $passWordToLogin
   $selectAllInfoFromDb | Export-csv $backupFarmInfoTable -NoTypeInformation
   Write-host “Backed up table located at:” -foregroundcolor “green”
   Write-host “$backupFarmInfoTable” -foregroundcolor “green”
   }
   Catch [system.exception] {
   Write-host “Unable to back up table. Script will exit” -foregroundcolor “red”
   exit
   }
   ########################## 2. Drop and recreate table ########################################

   $dropAndRemakeTableQuery = “drop table TABLE_NAME
   use DATABASE_NAME;

   CREATE TABLE TABLE_NAME
   (
   id int NOT NULL IDENTITY(1,1),
   environment nvarchar(100),
   farm nvarchar(100),
   web_application nvarchar(100),
   content_database nvarchar(100),
   host_name nvarchar(100),
   server_relative_url nvarchar(100),
   url nvarchar(100),
   root_web nvarchar(100),
   owner nvarchar(100),
   secondary_owner nvarchar(100),
   server_remoted_into nvarchar(100),
   PRIMARY KEY (id)
   );”

   Invoke-sqlCmd -Query $dropAndRemakeTableQuery -ServerInstance $serverOfDatabase -database $database -username $userNameToLogin -password $passWordToLogin

3. The PowerShell script next grabs list of our servers. We had them stored in a SharePoint List. Now that we had servers and pw/accounts, I created a hashmap variable with server as key and account as value to distinguish which server and account were connected.
4. Next we can remote into servers with the correct server/account info and insert into our table:
   

   Invoke-Command -Session $session -ScriptBlock {
   foreach($site in Get-spsite -limit all)
   {
   $webApplication = $site.WebApplication -replace “SPWebApplication Name=”, “”
   $contentDatabase = $site.ContentDatabase -replace “SPContentDatabase Name=”, “”
   $hostName = $site.HostName -replace “‘”, “”
   $serverRelativeUrl = $site.ServerRelativeUrl -replace “‘”, “”
   $url = $site.Url
   $rootWeb = $site.RootWeb -replace “‘”, “”
   $owner = $site.Owner -replace “‘”, “”
   $secondaryOwner = $site.SecondaryContact -replace “‘”, “”
   $SqlQuery = “insert into dbo.farm_info (environment, farm, web_application, content_database, host_name, server_relative_url, url, root_web, owner, secondary_owner, server_remoted_into) values (‘$environment’, ‘$farm’, ‘$webApplication’, ‘$contentDatabase’, ‘$hostName’, ‘$serverRelativeUrl’, ‘$url’, ‘$rootweb’, ‘$owner’, ‘$secondaryOwner’, ‘$serverRemotedInto’);”
   $sqlCmd.CommandText = $SqlQuery
   $sqlCmd.ExecuteNonQuery()
   write-host “Inserting…”
   }

   $sqlCmd.Connection.Close()
   }

   remove-pssession $session

5. Now that we had all the info we want in our table, we can retrieve it and store it anywhere like a csv or SharePoint list.
   

   #################### 6. Export Farm Info########################################

   $selectNewInfoFromDb = Invoke-sqlCmd -Query “SELECT * FROM dbo.TABLE_NAME” -ServerInstance $serverOfDatabase -database $database -username $userNameToLogin -password $passWordToLogin$selectNewInfoFromDb | Export-csv $exportFarmInfoTable -NoTypeInformation

   Write-host “Exporting farm info table…”Write-host “File located at: $exportFarmInfoTable” -foregroundcolor “green”