In my last post, I introduced Get-SQLServerList. In this post, I am going to describe some more ways to leverage the Get-SQLServerList and Invoke-SQLQuery functions.

Basically, I am suggesting that you follow this formula:
Step 1. Put your server names in a text file called "sqlserver-list.txt" and put the file in the root of your C drive.
Step 2. dot-source the functions that I've given you.
Step 3. Profit.

(I'd like to remind you that I normally alias Get-SQLServerList as "ssl" and Invoke-SQLQuery as "iq". This is to save on typing. It's also worth pointing out that I normally use the "ft" alias instead of typing format-table. On with the show...)

Let's say that your boss wants to know which 64 bit servers aren't at SP2. One way to do this would be to spend a lot of time manually connecting to each system, running a query and putting the results into a spreadsheet. Another way to do this is to go to the shared Word or Excel file that is supposed to be updated (but never is) with this information, spend time looking through it and cutting-and-pasting what you have found into an email. Another way to do this would be to purchase, install and maintain some sort of enterprise management product that never seems to have quite the report that you want.

Here's what I do:
PS>$result = ssl | iq | where {$_.Edition -match '64' -and !($_.ProductLevel -eq 'SP2')}

That will take a few seconds to run. What this does is:

• Uses Get-SQLServerList to gets a list of servers to look at.
• Runs Invoke-SQLQuery, using the default command, to retrieve the relevant information from each server. The results are packed into a System.Array and handed off to the next link in the pipeline.
  
• Tests the results in the System.Array to find servers that have '64' in the Edition string, signifying a 64 bit edition of SQL Server, and are not at SP2.
• Stores the resulting array, which contains only the information we want, in the $result variable.
• Note that I do not specify ft here. If I did, $result would contain formatted data, which means that I would not be able to apply a sort or additonal where clauses to $result.
  

Now, I want to see what I have collected. So, I run this:
PS> $result | ft -a

I am specifying ft now, because I want nicely-formatted output. You can specfiy Format-List (fl) if you prefer.

If I don't have the results that I want, I can go back and change the original command, run it again and look at the contents of $result again. Or maybe I want to see the small servers first:

PS> $result | sort TotalServerMemoryMB | ft -a

Too many columns? OK, let's cut it down to the more interesting ones:

PS>$result = ssl | iq | where {$_.Edition -match '64' -and !($_.ProductLevel -eq 'SP2')} | select Computer,ProductVersion,Build,ProductLevel,Edition,totalServerMemoryMB
PS>$result | ft -a

Once I have what I want, then:
PS> $result | out-clipboard

Now, just paste the clipboard contents into an email, and it's done. Slightly fancier reporting can be done by putting the results into an Excel file, providing column headings, bolding them and setting the column widths. You could also probably save as an xml file, come up with a style sheet to apply to the data and send the resulting report off to whomever wants it. In my world, "fast" is almost always more important than "pretty".

Once again, I hope that you'll see that you are really only limited by your imagination. Well, that and your skill at constructing pipelines. It can be daunting at first, but the idea of using a Transact SQL where clause should be familiar to you, it's just that Powershell's where clause syntax is different.