{"id":83124,"date":"2019-01-29T19:37:13","date_gmt":"2019-01-29T19:37:13","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=83124"},"modified":"2021-08-24T13:39:17","modified_gmt":"2021-08-24T13:39:17","slug":"using-a-server-list-to-control-powershell-scripts","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/powershell\/using-a-server-list-to-control-powershell-scripts\/","title":{"rendered":"Using a Server List to Control PowerShell Scripts"},"content":{"rendered":"
\n

NOTE: The sqlps utility has been deprecated, but it’s still in use on older servers. Be sure to check out the SqlServer PowerShell module<\/a> which has much greater functionality. <\/em><\/p>\n<\/div>\n

As I started to use PowerShell more and more, one of the reasons I found it useful, as have many DBAs, is that it permits running the same script across multiple servers. The scripts in my last article<\/a> took advantage of this capability.<\/p>\n

One of the reasons I automate is because I hate to repeat work. But here is a potential problem. What happens when I have 20 different scripts to perform various audits, updates and the like of my SQL Servers and I now add a new SQL Server to the mix?<\/p>\n

Well, if I haven\u2019t planned things out, I now need to update 20 different scripts. And I have to hope I get them all right! That sounds like more work than I care to do.<\/p>\n

In this post, I\u2019m going to show you two possible solutions you can use to make your life easier: one using a simple text file imported into an array and the second using JSON to store additional data related to each server.<\/p>\n

String and Arrays Primer<\/h2>\n

Before I begin, I want to explore a bit how PowerShell handles strings and how to create arrays. PowerShell has multiple ways to create an array, but initially, I want to create an array of strings, and my preferred way is to use the explicit syntax, but I will demonstrate both.<\/p>\n

Run the following script in the PowerShell ISE:<\/p>\n

$explicitArray = @(\"one\",\"two\",\"three\")\r\nWrite-Host \"explicit: \" $explicitArray\r\n$implicitArray = \"one\",\"two\",\"three\"\r\nWrite-Host \"implicit:  $implicitArray\"<\/pre>\n
You should get output like:<\/p>\n
<\/p>\n
Note that the two statements act the same way. However, I prefer using the explicit syntax as it makes it more obvious that I\u2019m working with an array. If you look closely, you will notice that in the case of writing out the $implicitArray<\/code>, instead of concatenating it to the output for Write-Host<\/code>, I put it inside the double-quotes. PowerShell is smart enough to expand the variable inside double-quotes. Note that single-quotes will treat the string exactly as shown and will not expand the variable.<\/p>\n
Write-Host 'implicit:  $implicitArray' <\/pre>\n
<\/p>\n
Note also that you can write out a string directly, without Write-Host<\/code> simply by referencing it. However, in this case, you have to use a+<\/code> to perform concatenation.<\/p>\n
\"explicit direct write: \" + $explicitArray\r\n\"implicit direct write: $implicitArray\" <\/pre>\n
<\/p>\n
Now that I\u2019ve covered some string and array basics, I will dive into how to use arrays and more to help automate your servers.<\/p>\n
Create a Server List<\/h2>\n
Before you can begin, create a small file in your home directory called sqlserverlist.txt<\/em> and put in the following:<\/p>\n
one
\ntwo
\nthree<\/p>\n
Save the file. To read the file back, you can run<\/p>\n
get-content -Path \".\\sqlserverlist.txt\" <\/pre>\n
One issue you may realize right away is that if you are not in the right directory, you may get an error like the following:<\/p>\n
<\/p>\n
You can solve that by hardcoding the path:<\/p>\n
get-content -Path \"c:\\temp\\sqlserverlist.txt\"<\/pre>\n
Slight Detour: Environment Variables<\/h3>\n
I want to take a slight detour here and remind you of my last article<\/a> where I demonstrated that you could use get-psdrive<\/code> to see a list of objects that PowerShell can access.<\/p>\n
get-psdrive<\/pre>\n
<\/p>\n
You may notice two interesting names, Env and Variable. Again, as a reminder, PowerShell treats everything as an object and Env is an object you can access.<\/p>\n
On your system run the following command:<\/p>\n
get-childitem ENV:<\/pre>\n
You will see it returns a list of your environment variables. So, any environment variable set on your machine can be used within a PowerShell Script.<\/p>\n
If you run<\/p>\n
get-childitem Variable:<\/pre>\n
You will see a list of all the variables currently within your PowerShell session. Some of these are set by PowerShell, and the rest are the ones you created.<\/p>\n
To access your environment variables, you need to tell PowerShell you want to access that object as follows:<\/p>\n
$ENV:variable<\/pre>\n
For example, $ENV:COMPUTERNAME <\/code>should return the name of your computer.<\/p>\n
Detour Ends<\/h3>\n
If you want to keep the above file in your home directory you can also use a hardcoded path or dynamically generate one as follows:<\/p>\n
get-content -Path \"$env:HOMEDRIVE$env:HOMEPATH\\sqlserverlist.txt\" <\/pre>\n
Note here you are taking advantage of the fact that when using double quotes, PowerShell will expand variables inside the string. Of course, you can put the file wherever works best for your needs.<\/p>\n
You can take advantage of any environment variables you want that are set on your machine or by your domain login to access files in a common location.<\/p>\n
So now you\u2019re a step closer to having a central repository of all your servers in a simple text file.<\/p>\n
The next step is to assign the values to an array as follows:<\/p>\n
$serverlist = @(get-content  -Path \"$env:HOMEDRIVE$env:HOMEPATH\\sqlserverlist.txt\") <\/pre>\n
Note here I used the explicit definition of an array as it makes it more clear to me later on that I\u2019m creating an array of strings, not simply assigning the contents of this file to a simple string.<\/p>\n
Next run:<\/p>\n
foreach ($server in $serverlist)\r\n{\r\n    write-host \"server: $server\"\r\n} <\/pre>\n
You should have the following results, just as you would expect:<\/p>\n
<\/p>\n
Obviously instead of write-host<\/code> you could substitute a SQLcmd (if you run this now, you\u2019ll get errors since since the file doesn\u2019t contain real server names at this point):<\/p>\n
$serverlist = @(get-content  -Path \"$env:HOMEDRIVE$env:HOMEPATH\\sqlserverlist.txt\") \r\nimport-module sqlps\r\nforeach ($sqlserver in $serverlist) \r\n{\r\ncd sqlserver:\\sql\\$sqlserver\r\nls logins -force| select-object name,ispasswordexpired, MustChangePassword, isdisabled, passwordexpirationenabled | format-table\r\n} <\/pre>\n
You should recognize this script from the previous article, but this time instead of explicitly creating the array in the script, it\u2019s now reading in from an external file. This means that, if you add a server to your network, you only need to update a single file, sqlserverlist.txt<\/em> and all your scripts should work as before, but now also work on the new server.<\/p>\n
That\u2019s definitely an improvement over updating 20 different scripts every time you add or remove a server from your network. However, if you\u2019re like most companies, you may have servers in different groups that you need to treat differently. For example, you might need to treat Production servers differently from Dev servers. You also might want to do things differently depending on the version of the OS or SQL Server.<\/p>\n
Objects to the Rescue<\/h2>\n
Once again the key to the solution is to think in terms of objects. Specifically, your SQL Server is more than simply a name; it\u2019s an object and should be treated as one.<\/p>\n
Up until now, all the scripts you\u2019ve run have used built-in objects. However, you can also create your own objects. Like string handling, there are multiple ways to create an object. For now, though, I will show you how to create an object explicitly. Once you get the hang of it, you can use some of the other methods of creating objects that use less verbiage.<\/p>\n
Run the following code to create an object:<\/p>\n
$serverObject = New-Object -TypeName psobject\r\n$serverObject | Add-Member -MemberType NoteProperty -Name ComputerName -Value \"one\"\r\n$serverObject | Add-Member -MemberType NoteProperty -Name Environment -Value \"Production\"\r\n$serverObject | Add-Member -MemberType NoteProperty -Name SQLVersion -Value \"2016\"\r\n$serverObject | Add-Member -MemberType NoteProperty -Name OSVersion -Value \"2012\" <\/pre>\n
If you then run:<\/p>\n
Write-Host $serverObject <\/pre>\n
You should see:<\/p>\n
<\/p>\n
You can also run:<\/p>\n
$serverObject | gm | ogv<\/pre>\n
Now you should see:<\/p>\n
<\/p>\n
You will notice that your object automatically has some methods built in.<\/p>\n
For example, if you run:<\/p>\n
$serverobject.GetType() <\/pre>\n
You will see:<\/p>\n
<\/p>\n
Notice the base type is a System.Object<\/code>.<\/p>\n
If you run:<\/p>\n
$explicitArray.GetType() <\/pre>\n
You will get<\/p>\n
<\/p>\n
This makes sense, because $explicitArray<\/code> is an array. It was explicitly created above. Additionally, if you run:<\/p>\n
$implicitArray.GetType() <\/pre>\n
You will get the same results:<\/p>\n
<\/p>\n
In other words, PowerShell is smart enough to recognize that both objects, $explicitArray<\/code> and $implicitArray<\/code> are objects of type System.Array<\/code>, even though you created them using different syntax.<\/p>\n
You may find yourself using the .GetType()<\/code> method often to keep track of what type of objects you are working with.<\/p>\n
Finally run:<\/p>\n
Write-host $serverObject.ComputerName <\/pre>\n
You should see:<\/p>\n
<\/p>\n
If you type<\/p>\n
write-host $serverobject.Environment <\/pre>\n
You should see:<\/p>\n
<\/p>\n
You now have an object that provides more than simply the name of a server. You can add more properties if you want, but the ones here are enough for moving forward.<\/p>\n
To make use of this object, you will need to save it to a file.<\/p>\n
Your first inclination might be to do something like:<\/p>\n
$serverObject | out-file -FilePath \"$env:HOMEDRIVE$env:HOMEPATH\\sqlserverobjectlist.txt\"  <\/pre>\n
That appears to work until you look at the file and see:<\/p>\n
<\/p>\n
This output is not ideal. With some effort, you could get rid of the header information, but then you might face other issues. For example what if your Environment for another server is called Dev and QA<\/strong>. This means you can\u2019t rely on spaces to delimit your properties. You can perhaps then wrap your properties in single quotes and do other fancy stuff, but very quickly this becomes complex. I\u2019d prefer a simpler solution.<\/p>\n
JSON is the rage among all the kids these days, and this provides a simple and convenient solution.<\/p>\n
Run:<\/p>\n
ConvertTo-Json $serverObject <\/pre>\n
You should see the following:<\/p>\n
<\/p>\n
If you pipe that to Out-file as follows:<\/p>\n
ConvertTo-Json $serverObject | out-file -FilePath \"$env:HOMEDRIVE$env:HOMEPATH\\sqlserverobjectlist.json\"  <\/pre>\n
Now if you look at the output file you will see:<\/p>\n
<\/p>\n
This output looks useful.<\/p>\n
To continue, replace the contents of the file that was created with this JSON:<\/p>\n
[\r\n{\r\n    \"ComputerName\":  \"one\",\r\n    \"Environment\":  \"Production\",\r\n    \"SQLVersion\":  \"2016\",\r\n    \"OSVersion\":  \"2012\"\r\n},\r\n{\r\n    \"ComputerName\":  \"two\",\r\n    \"Environment\":  \"DEV\",\r\n    \"SQLVersion\":  \"2016\",\r\n    \"OSVersion\":  \"2012\"\r\n},\r\n{\r\n    \"ComputerName\":  \"three\",\r\n    \"Environment\":  \"TEST\",\r\n    \"SQLVersion\":  \"2017\",\r\n    \"OSVersion\":  \"2012\"\r\n}\r\n]<\/pre>\n
Notice now you still have three servers, but you have more complete information about them. Save the above file.<\/p>\n
Now run the following:<\/p>\n
Get-Content -Raw -Path \"$env:HOMEDRIVE$env:HOMEPATH\\sqlserverobjectlist.json\" | ConvertFrom-Json <\/pre>\n
You should see the following output:<\/p>\n
<\/p>\n
Now you\u2019ve made progress!<\/p>\n
Assign that input to a variable:<\/p>\n
$serverobjlist = Get-Content -Raw -Path \"$env:HOMEDRIVE$env:HOMEPATH\\sqlserverobjectlist.json\" | ConvertFrom-Json <\/pre>\n
Now you can actually do something useful:<\/p>\n
$serverobjlist = Get-Content -Raw -Path \"$env:HOMEDRIVE$env:HOMEPATH\\sqlserverobjectlist.json\" | ConvertFrom-Json\r\nforeach ($server in $serverobjlist) \r\n{\r\n    if ($server.environment -eq \"Production\")\r\n    {\r\n        Write-host \"$($server.ComputerName) is a production server\"\r\n    }\r\n    else\r\n    {\r\n        Write-Host \"$($server.ComputerName) is a $($server.Environment) server\"\r\n    }\r\n    if ($server.SQLVersion -lt \"2017\")\r\n    {\r\n        Write-Host \"$($server.ComputerName) doesn't have the latest and greatest. Send email to finance to request money to upgrade!\"\r\n    }\r\n}<\/pre>\n
 <\/p>\n
You could have more useful commands in there, but this shows how you can do different things depending on the environment or version. One thing that you should notice is that you have to wrap the $server.ComputerName<\/code> and $server.Environment<\/code> in parenthesis preceded by an additional $<\/code>. This seems strange, but the reason is to force the $server<\/code> object to expand the property and then treat that as a variable to embed in the string. If you left off the encapsulation, you would see something like:<\/p>\n
<\/p>\n
This is because PowerShell would expand the entire object and then append the string .computername<\/code> to it.<\/p>\n
The above script is a bit silly but illustrates how you can use the $server.environment<\/code> property to perform useful operations. Most likely though, you will want to use the same script, but in different environments.<\/p>\n
Save the following script to a file called Server Script with passed in parameter.ps1.<\/em><\/p>\n
if ($args.count -eq 0)\r\n{\r\n    Write-Host \"You must enter an environment name!\"\r\n    return\r\n}\r\nelse\r\n{\r\n    $environment = [string]$args[0]\r\n    $serverobjlist = Get-Content -Raw -Path \"$env:HOMEDRIVE$env:HOMEPATH\\sqlserverobjectlist.json\" | ConvertFrom-Json\r\n    foreach ($server in $serverobjlist) \r\n    {\r\n        if ($server.Environment -eq $environment)\r\n        {\r\n            Write-Host \"The following operation will be done on server $($Server.ComputerName)\"\r\n        }\r\n    }\r\n} <\/pre>\n
If you simply run the script from within the Windows Powershell ISE, you should get<\/p>\n
<\/p>\n
This makes sense since you did not pass in an environment name.<\/p>\n
From the lower command window in ISE enter<\/p>\n
& '.\\Server Script with passed in parameter.ps1' \"DEV\" <\/pre>\n
The & <\/code>is telling the ISE that you\u2019re calling a script.<\/p>\n
You should see:<\/p>\n
<\/p>\n
Here are two changes you can make to the script:<\/p>\n
param([string]$environment)\r\nif ($environment -eq \"\")\r\n{\r\n    Write-Host \"You must enter an environment name!\"\r\n    return\r\n}\r\nelse\r\n{\r\n    $serverobjlist = Get-Content -Raw -Path \"$env:HOMEDRIVE$env:HOMEPATH\\sqlserverobjectlist.json\" | ConvertFrom-Json\r\n    foreach ($server in $serverobjlist | where-object {$_.Environment -eq $environment})\r\n    {\r\n            Write-Host \"The following operation will be done on server $($Server.ComputerName) in the $environment Environment\"\r\n    }\r\n} <\/pre>\n
Note in the first, there is a new block, which has to be the first block in the file, a param block. In this case, it only specifies a single parameter called $environment<\/code>. However, you could specify multiple parameters (such as $OSVersion<\/code> or $SQLVersion<\/code>). You can also assign default values if you wish.<\/p>\n
The advantage of this solution is, when you\u2019re typing the command from the PowerShell Command Line, if you enter \u2013 after the command, you will be prompted for the parameter name. This makes it much easier for a new user to know what parameters are required. As long as you enter the full parameter name as shown below, you can enter parameters in any order you want. This reduces errors.<\/p>\n
Save the modified script to Server Script with passed in parameter version 2.0.ps<\/em>1 and run the next command in the ISE command window:<\/p>\n
& '.\\Server Script with passed in parameter version 2.0.ps1' -environment \"dev\" <\/pre>\n
And you should get:<\/p>\n
<\/p>\n
The second change made was to pipe the loop through a Where-object<\/code> cmdlet. This lets you reduce the verbiage a bit. Either this method or the previous one will give the same results, but this shortens the script a bit. I tend to prefer this method.<\/p>\n
This also allows one more minor change:<\/p>\n
param([string]$environment)\r\nif ($environment -eq \"\")\r\n{\r\n    Write-Host \"You must enter an environment name!\"\r\n    return\r\n}\r\nelse\r\n{\r\n    $serverobjlist = Get-Content -Raw -Path \"$env:HOMEDRIVE$env:HOMEPATH\\sqlserverobjectlist.json\" | ConvertFrom-Json\r\n    foreach ($server in $serverobjlist | where-object {$_.Environment -in $environment.split(\",\")})\r\n    {\r\n            Write-Host \"The following operation will be done on server $($Server.ComputerName) in the $($Server.Environment) Environment\"\r\n    }\r\n} <\/pre>\n
Notice the user of .split(\u201c,\u201d)<\/code> on the $environment<\/code> variable, and I\u2019ve changed the output string to use $($Server.Environment)<\/code> value since now it is specific to the server in question.<\/p>\n
This allows you to run the following after saving the new file:<\/p>\n
& '.\\Server Script with passed in parameter version 3.0 with split.ps1' -environment \"dev,production\" <\/pre>\n
You should see:<\/p>\n
<\/p>\n
This allows you to run the script against more than one environment at the same time. One caveat is, in this case, you must wrap your list of environments in quotes.<\/p>\n
Conclusion<\/h2>\n
This article has given you the tools to take any existing PowerShell Scripts you\u2019ve created to manage your SQL Servers and expand them so you can maintain a central list of SQL Servers. You can also store related information control how the scripts run based on the environment or other factors.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
PowerShell is the preferred tool for many DBAs when automating SQL Server administration. In this article, Greg Moore demonstrates how to use a server list to control which tasks are performed on which servers.…<\/p>\n","protected":false},"author":319367,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,53,35],"tags":[95506],"coauthors":[61343],"class_list":["post-83124","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","category-featured","category-powershell","tag-automate"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83124","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/319367"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=83124"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83124\/revisions"}],"predecessor-version":[{"id":83676,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83124\/revisions\/83676"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=83124"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=83124"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=83124"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=83124"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}