For this posting, I present an uptime command. This is the sort of thing that most scripters wind up writing, I suppose. For me, the neat thing was how just a few lines of script produced a program that was more flexible and usable than an old, compiled EXE program that I had been carting with me around on a USB stick for years.
A little bit of this code is a refactoring of code posted at BSOnPosh, but that page seems to have been taken down during a site re-arrangement and my search didn't find anything. IIRC, I wanted an easier way to get uptime information for more than one server, so I refactored some of his code (the WMIDate conversion, which I wanted to be able to re-use for other things, is sort of fiddly) and added my own code to loop over the pipe line input, to produce the TextDescription and to make the select-object code more readable by using the $fields variable to hold all of the necessary script blocks.
Anyway, I present what I've got for your perusal. Personally, I like to see different approaches to the same problem as it often gives additional insight into what is possible. Those "Wow, that's much easier than what I've been doing" moments can be embarrassing, but they also mean that I'm learning and IT isn't an industry where you can afford to stop learning.
The code is here. As usual, it's pretty straightforward. (My most ambitious scripts are really too site-specific to be of use to anyone else.) There is a helper function, Convert-WMIDateToSystemDate, which does exactly what you'd think and there is the main function, Get-Uptime, which also does as you'd expect.
You could invoke the function like this:
("server1", "server2") | get-uptime | sort TotalSeconds | ft -a
The more interesting columns are the "TotalSeconds" column, which is more useful for sorting than actually being read and interpreted by humans, and the "TextDescription" column, which describes the uptime of a server in days, hours, minutes and seconds and is easily understood by the most P of PHBs.
Saturday, June 28, 2008
Tuesday, June 24, 2008
A (simplistic) implementation of a watch command
Today's posting covers a function which mimics the 'watch' command, which is popular on Unix and Linux systems. The watch command will repetitively run a command line and display the output. This is useful for watch the progress of systems writing a series of files or waiting for a server to come online. Just start a shell, run the command and park the shell so it is still visible, but out of the way. Even though my function does not do a particularly thorough job mimicking the Unix watch command, I still get a lot of mileage out of it.
This one is so simple that I am just going to paste it in here and hope that the blogging software doesn't drop the formatting. I'm still learning the quirks of blogger, so please bear with me. Here goes:
##############################################################################
# Purpose: More-or-less mimic the popular unix "watch" command
function Watch-Command {
param (
[string] $command,
[int] $interval = 2
)
do {
clear
invoke-expression $command
# Just to let the user know what is happening
"Waiting for $interval seconds. Press Ctrl-C to stop execution..."
sleep $interval
}
# loop FOREVER
while (1 -eq 1)
}
That's it. I normally alias this to watch.
Let's say that you were waiting for a particular file to show up in the temporary directory of your workstation. A simple solution might be:
watch "dir c:\temp\somefile.txt"
This isn't as cute as writing a function to ring the system bell, play a wave file or say "You've got a file.", but it is quick, functional and flexible, which are things we value highly here at the Ashdar Partners blog. It's also already written. I could write a custom-built function to do those things, but why bother when I already have watch?
One quirk with my version of watch is that the quoting of the -command parameter works differently than what you get on bash. On bash, you can often avoid enclosing the whole command in quotes. With my Powershell version, you should probably just go ahead and wrap the command in double quotes because any embedded spaces will require the use of quotes. Stopping a moment to consider the question "should I or shouldn't I add quotes" is more work than just conditioning yourself to do it. It's usually simple enough to wrap the command line in quotes. If you already have complicated quoting in place, or you want to do something that requires some more processing you can get the same effect by writing a little function.
As a simple example, lets say that I want to repetitively perform two tasks. Firstly, I want to output out the time to the console using the following command:
date
and, secondly, I want to look for a particular file using the following command:
dir c:\sqlserver-list.txt
I can do this one of (at least) two ways. The first, and more obvious way, is to wrap everything in quotes and separate the two command with a semicolon.
watch "date ; dir c:\sqlserver-list.txt"
The second, and ultimately more flexible way, is to built a little function with those commands via the command line. This also provides another viewpoint on how flexible Powershell really is. The function can be named anything; for this example I will use the ever-popular foo. Just type in the following in a shell window, hitting return where you see the line breaks here:
function foo {
date
dir c:\sqlserver-list.txt
}
After the closing "}", you will need to hit return twice. This will bring you back to an interactive command line. Then, to prove that we really have a new function, you can run the following two commands to see information about our new function:
dir function:foo
(dir function:foo).Definition
The first command will show you properties of the foo function object. The second command will show you the body ("definition" in Powershell-speak) of the foo function object. Lastly, you can use the function with watch:
watch foo
Lastly, the -interval parameter controls how long the function waits before running the command again. To run our foo function once every 60 seconds, you could use it like this:
watch -command foo -interval 60
Two seconds is a pretty short duration, but it is the default on Linux systems and I saw no real reason to change it.
That's all for this time. Thanks for reading.
This one is so simple that I am just going to paste it in here and hope that the blogging software doesn't drop the formatting. I'm still learning the quirks of blogger, so please bear with me. Here goes:
##############################################################################
# Purpose: More-or-less mimic the popular unix "watch" command
function Watch-Command {
param (
[string] $command,
[int] $interval = 2
)
do {
clear
invoke-expression $command
# Just to let the user know what is happening
"Waiting for $interval seconds. Press Ctrl-C to stop execution..."
sleep $interval
}
# loop FOREVER
while (1 -eq 1)
}
That's it. I normally alias this to watch.
Let's say that you were waiting for a particular file to show up in the temporary directory of your workstation. A simple solution might be:
watch "dir c:\temp\somefile.txt"
This isn't as cute as writing a function to ring the system bell, play a wave file or say "You've got a file.", but it is quick, functional and flexible, which are things we value highly here at the Ashdar Partners blog. It's also already written. I could write a custom-built function to do those things, but why bother when I already have watch?
One quirk with my version of watch is that the quoting of the -command parameter works differently than what you get on bash. On bash, you can often avoid enclosing the whole command in quotes. With my Powershell version, you should probably just go ahead and wrap the command in double quotes because any embedded spaces will require the use of quotes. Stopping a moment to consider the question "should I or shouldn't I add quotes" is more work than just conditioning yourself to do it. It's usually simple enough to wrap the command line in quotes. If you already have complicated quoting in place, or you want to do something that requires some more processing you can get the same effect by writing a little function.
As a simple example, lets say that I want to repetitively perform two tasks. Firstly, I want to output out the time to the console using the following command:
date
and, secondly, I want to look for a particular file using the following command:
dir c:\sqlserver-list.txt
I can do this one of (at least) two ways. The first, and more obvious way, is to wrap everything in quotes and separate the two command with a semicolon.
watch "date ; dir c:\sqlserver-list.txt"
The second, and ultimately more flexible way, is to built a little function with those commands via the command line. This also provides another viewpoint on how flexible Powershell really is. The function can be named anything; for this example I will use the ever-popular foo. Just type in the following in a shell window, hitting return where you see the line breaks here:
function foo {
date
dir c:\sqlserver-list.txt
}
After the closing "}", you will need to hit return twice. This will bring you back to an interactive command line. Then, to prove that we really have a new function, you can run the following two commands to see information about our new function:
dir function:foo
(dir function:foo).Definition
The first command will show you properties of the foo function object. The second command will show you the body ("definition" in Powershell-speak) of the foo function object. Lastly, you can use the function with watch:
watch foo
Lastly, the -interval parameter controls how long the function waits before running the command again. To run our foo function once every 60 seconds, you could use it like this:
watch -command foo -interval 60
Two seconds is a pretty short duration, but it is the default on Linux systems and I saw no real reason to change it.
That's all for this time. Thanks for reading.
Thursday, June 19, 2008
Powershell Function: Get-LogicalDisk
In this post, I will discuss Get-LogicalDisk. Get-LogicalDisk is a function that retrieves disk information from remote servers via WMI. This allows for quick and flexible reporting on storage usage and availibility.
This is a bit of a departure from "SQL Server"-oriented functionality that I have been describing in recent posts. In some shops, free disk space or, more likely, the lack of it is a daily battle. Every morning, I run a script that checks for many different kinds of "negative events" that might have happened over night. A "negative event" might be a failed job, database growth or a server running low on disk space. The latter check calls Get-SQLServerList and Get-LogicalDisk to do all of the heavy work.
The script is straightforward, so I'll just continue with my tradition of a brief overview with some examples. (This is a blog, after all, and not really suited to voluminous detail. FWIW, it is my opinion that detail on what a confusing part of a script does should be in the comments in the script, not in a blog posting. If you find a part of any script confusing, let me know and I'll update the comments in the script and possibly do a blog posting to explain how it works.)
The function takes a list of computers as pipeline input, so feeding a list of SQL Servers using Get-SQLServerList (which I normally alias as "ssl") or any other method is easy. Get-SQLServerList uses WMI to retrieve the following information from each "fixed disk" on the remote system:
I would like to point out that one could calculate UsedSpace by subtracting FreeSpace from Space and that one could calculate Free-% given Used-%, yet they are returned by Get-SQLServerList. These calculations are done by Get-SQLServerList so a calling program (or something that you've hacked together on a command line) do not have to. This makes for a more flexible function and for less typing.
Here is a simple example, including some output, which finds information on the local computer:
PS> get-logicaldisk . | ft -a
Computer Letter Label Space FreeSpace UsedSpace Free-% Used-% Unit
-------- ------ ----- ----- --------- --------- ------ ------ ----
MYLAPTOP C: local.boot 33.17 7.94 25.23 24 76 GB
If you are using a naming scheme (or some other method) to discriminate between development, quality assurance and production servers, the following query might provide a list of disks where you might be able to store that 'temporary' backup:
PS> ssl de | Get-LogicalDisk | where {$_.Letter -match "c"} | sort FreeSpace | ft -a
Here is a command that looks for disks on production servers with less than 5% free space:
PS> ssl pr | Get-LogicalDisk | where {$_."Free-%" -lt 5} | ft -a
I hope that you can see the power of the Get-LogicalDisk function, which can easily be leveraged by additional simple Powershell syntax to provide complicated, real-time reports on disk space issues.
This is a bit of a departure from "SQL Server"-oriented functionality that I have been describing in recent posts. In some shops, free disk space or, more likely, the lack of it is a daily battle. Every morning, I run a script that checks for many different kinds of "negative events" that might have happened over night. A "negative event" might be a failed job, database growth or a server running low on disk space. The latter check calls Get-SQLServerList and Get-LogicalDisk to do all of the heavy work.
The script is straightforward, so I'll just continue with my tradition of a brief overview with some examples. (This is a blog, after all, and not really suited to voluminous detail. FWIW, it is my opinion that detail on what a confusing part of a script does should be in the comments in the script, not in a blog posting. If you find a part of any script confusing, let me know and I'll update the comments in the script and possibly do a blog posting to explain how it works.)
The function takes a list of computers as pipeline input, so feeding a list of SQL Servers using Get-SQLServerList (which I normally alias as "ssl") or any other method is easy. Get-SQLServerList uses WMI to retrieve the following information from each "fixed disk" on the remote system:
- Computer
- Letter
- Label
- Space, which gives the total size of the disk
- FreeSpace, which gives the size of the free space on the drive
- UsedSpace, which gives the size of the space which is being used on the drive
- Free-%
- Used-%
- Unit
I would like to point out that one could calculate UsedSpace by subtracting FreeSpace from Space and that one could calculate Free-% given Used-%, yet they are returned by Get-SQLServerList. These calculations are done by Get-SQLServerList so a calling program (or something that you've hacked together on a command line) do not have to. This makes for a more flexible function and for less typing.
Here is a simple example, including some output, which finds information on the local computer:
PS> get-logicaldisk . | ft -a
Computer Letter Label Space FreeSpace UsedSpace Free-% Used-% Unit
-------- ------ ----- ----- --------- --------- ------ ------ ----
MYLAPTOP C: local.boot 33.17 7.94 25.23 24 76 GB
If you are using a naming scheme (or some other method) to discriminate between development, quality assurance and production servers, the following query might provide a list of disks where you might be able to store that 'temporary' backup:
PS> ssl de | Get-LogicalDisk | where {$_.Letter -match "c"} | sort FreeSpace | ft -a
Here is a command that looks for disks on production servers with less than 5% free space:
PS> ssl pr | Get-LogicalDisk | where {$_."Free-%" -lt 5} | ft -a
I hope that you can see the power of the Get-LogicalDisk function, which can easily be leveraged by additional simple Powershell syntax to provide complicated, real-time reports on disk space issues.
Tuesday, June 17, 2008
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:
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.
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.
Thursday, June 12, 2008
Powershell Function: Get-SQLServerList
In my last post, I described Invoke-SQLQuery. In this post, I describe Get-SQLServerList, which retrieves a list of SQL Servers. Get-SQLServerList leverages Invoke-SQLQuery and provides an easy way to provide server names to a pipeline.
This time, the zip file for Invoke-SQLQuery contains two functions. The first function is Query-ServerCatalog. This function builds a simple SELECT statement and then uses Invoke-SQL Query to run it. This is useful as an example of the sort of thing that you can do with Invoke-SQLQuery. Query-ServerCatalog will return a result set full of servernames, assuming that you have a particular table in a particular database on a particular server that holds these names. Setting that up and keeping it current is a bit of work and probably is not worth doing unless you work in a large environment with a group of DBAs.
The more interesting function is Get-SQLServerList. Some of the highlights of Get-SQLServerList are:
With careful naming of servers, I can quickly select subsets of the entire server population. For our purposes, a seven character long name might consist of the following 'fields', along with some made-up examples:
Three characters indicating location of server: PHL (Philadelphia), NYC (New York City), SEA (Seattle)
Two characters indicating environment: DE (Development), "QA" (Quality Assurance), "PR" (Production)
Two characters indicating a 'server number': 01, 02, ..., 10, ..., 20, ..., 99
Using this scheme, the contents of the text file might look like this:
PHLQA05
PHLPR06
PHLPR88
NYCDE11
NYCPR11
NYCPR44
SEADE04
SEADE05
SEAPR33
SEAQA33
# end of file
I normally alias Get-SQLServerList to just "ssl", to cut down on typing. Here are some quick examples, using my made-up server names, followed with some made-up output.
# Show me all of the servers
PS>ssl
PHLQA05
PHLPR06
PHLPR88
NYCDE11
NYCPR11
NYCPR44
SEADE04
SEADE05
SEAPR33
SEAQA33
# Show me all of the QA servers. Note that the function does not use capitalization-sensitive comparisons:
PS>ssl qa
PHLQA05
SEAQA33
# Show me all of the Philly servers:
PS>ssl phl
PHLQA05
PHLPR06
PHLPR88
# Show me a particular server:
PS>ssl phlpr88
PHLPR88
And so on. By providing a -productversion value, you can run more intricate queries:
# Show me all of the sql2005 servers:
PS>ssl -ProductVersion 2005
PHLQA05
PHLPR06
PHLPR88
NYCDE11
NYCPR11
# Show me all of the sql2005 servers in Philly
PS>ssl phl 2005
PHLQA05
PHLPR06
PHLPR88
# Show me all of the servers with build 3054
PS>ssl -ProductVersion 3054
PHLPR06
So, we can easily find servers that are interesting to us. Note that both the -Computer and -ProductVersion are using Powershell's matching semantics. This means that you can create fairly complicated searches to answer questions like "show me the Philly servers with even-numbered server numbers".
That's all for now. In my next post, I'll show you some more of the commands that leverage Get-SQLServerList and Invoke-SQLQuery.
This time, the zip file for Invoke-SQLQuery contains two functions. The first function is Query-ServerCatalog. This function builds a simple SELECT statement and then uses Invoke-SQL Query to run it. This is useful as an example of the sort of thing that you can do with Invoke-SQLQuery. Query-ServerCatalog will return a result set full of servernames, assuming that you have a particular table in a particular database on a particular server that holds these names. Setting that up and keeping it current is a bit of work and probably is not worth doing unless you work in a large environment with a group of DBAs.
The more interesting function is Get-SQLServerList. Some of the highlights of Get-SQLServerList are:
- It reads a text file to find the names of servers and matches them against the $computer parameter.
- There is simplistic support for 'comments' in this text file. A '#' at the start of a line indicates that the remainder of the line should be ignored. This allows commentary information to be placed in the text file and provides a simple way to temporarily ignore a particular server.
- If the text file isn't found, Get-SQLServerList tries to grab a list of servers from a table using Query-SQLServerCatalog. I don't use this function now. Instead, I rely entirely on text files to store the server names. Text files are very easy to manage and change. I am leaving the feature alone because it's not broken, as far as i know, and it might be useful to me or someone else in the future.
- If a value is specified for -ProductVersion, Get-SQLServerList actually connects to all of the servers that match the given value of $computer and then looks for matching ProductVersion information. Even though it connects to all of the servers it still runs in just a few seconds.
With careful naming of servers, I can quickly select subsets of the entire server population. For our purposes, a seven character long name might consist of the following 'fields', along with some made-up examples:
Three characters indicating location of server: PHL (Philadelphia), NYC (New York City), SEA (Seattle)
Two characters indicating environment: DE (Development), "QA" (Quality Assurance), "PR" (Production)
Two characters indicating a 'server number': 01, 02, ..., 10, ..., 20, ..., 99
Using this scheme, the contents of the text file might look like this:
PHLQA05
PHLPR06
PHLPR88
NYCDE11
NYCPR11
NYCPR44
SEADE04
SEADE05
SEAPR33
SEAQA33
# end of file
I normally alias Get-SQLServerList to just "ssl", to cut down on typing. Here are some quick examples, using my made-up server names, followed with some made-up output.
# Show me all of the servers
PS>ssl
PHLQA05
PHLPR06
PHLPR88
NYCDE11
NYCPR11
NYCPR44
SEADE04
SEADE05
SEAPR33
SEAQA33
# Show me all of the QA servers. Note that the function does not use capitalization-sensitive comparisons:
PS>ssl qa
PHLQA05
SEAQA33
# Show me all of the Philly servers:
PS>ssl phl
PHLQA05
PHLPR06
PHLPR88
# Show me a particular server:
PS>ssl phlpr88
PHLPR88
And so on. By providing a -productversion value, you can run more intricate queries:
# Show me all of the sql2005 servers:
PS>ssl -ProductVersion 2005
PHLQA05
PHLPR06
PHLPR88
NYCDE11
NYCPR11
# Show me all of the sql2005 servers in Philly
PS>ssl phl 2005
PHLQA05
PHLPR06
PHLPR88
# Show me all of the servers with build 3054
PS>ssl -ProductVersion 3054
PHLPR06
So, we can easily find servers that are interesting to us. Note that both the -Computer and -ProductVersion are using Powershell's matching semantics. This means that you can create fairly complicated searches to answer questions like "show me the Philly servers with even-numbered server numbers".
That's all for now. In my next post, I'll show you some more of the commands that leverage Get-SQLServerList and Invoke-SQLQuery.
Tuesday, June 10, 2008
Powershell Function: Invoke-SQLQuery and Ping-Computer
In this posting, I am going to (briefly) discuss Invoke-SQLQuery, which will run a SQL Server query against a computer or a group of computers and then return the results as a Powershell object. This object can be fed to another element in a pipeline, where it can be sliced, diced and stored using Powershell's intrinsic capabilities.
About a year ago, I was looking for a way to return a result set to Powershell. It seemed that I was forever logging into a SQL Server instance, running one query, then logging out and repeating the whole thing on a different instance.
One day, I read a posting on Powershell and SMO by Muthusamy Anantha Kumar. I noticed some ado.net code towards the bottom of the page and I same the proverbial light: Powershell is to .Net objects what VBScript is to COM objects. Armed with that, I started working on a function that ultimately evolved into Invoke-SQLQuery.
I'd like to point out the following facets of Invoke-SQLQuery:
To call a SQL Server system stored procedure on a remote computer and show only some of the returned columns:
Invoke-SQLQuery -computer "server_a" -commandtext "sp_helpdb" | ft -auto Name,Status
Run pretty much any query that you'd like on a single computer:
$cmdText = "select @@servername 'Computer', count(*) 'ConnectionCount' from master.dbo.sysprocesses"
$result = Invoke-SQLQuery -computer:"server_a" -commandtext:$cmdText
$result | ft -auto
Run the same query, but this time run it against two computers. The names of the computers are placed in an array. That array is then fed to a pipeline. It would be trivial to add additional computers to the array, which will cause the command to be run on those computers as well. It is also easy to load an array of computer names from a simple text file.
$computers = ("server_a", "server_b")
$result = $computers | Invoke-SQLQuery -commandtext:$cmdText
$result | ft -auto Computer,ConnectionCount
The output of that last command might look like this:
Computer ConnectionCount
-------- ---------------
SERVER_A 35
SERVER_B 82
Note that the contents of $result are a real Powershell object. That means that you can go and run something like this:
$result | sort ConnectionCount -desc | ft -auto Computer,ConnectionCount
Computer ConnectionCount
-------- ---------------
SERVER_B 82
SERVER_A 35
You can add additional pipeline items as you wish. For example, instead of (or in addition to) applying a sort, you could apply a where clause. This would be silly in my two-server example, but imagine that you have a hundred servers and you would like to know the top five servers, by number of connections. After running the queries and placeing the results in $result, you could run something like this:
$result | sort ConnectionCount -desc | select -first 5
You could stick your results in an Excel file:
$result | sort ConnectionCount -desc | select -first 5 | Out-Excel
You are only limited by your imagination.
About a year ago, I was looking for a way to return a result set to Powershell. It seemed that I was forever logging into a SQL Server instance, running one query, then logging out and repeating the whole thing on a different instance.
One day, I read a posting on Powershell and SMO by Muthusamy Anantha Kumar. I noticed some ado.net code towards the bottom of the page and I same the proverbial light: Powershell is to .Net objects what VBScript is to COM objects. Armed with that, I started working on a function that ultimately evolved into Invoke-SQLQuery.
I'd like to point out the following facets of Invoke-SQLQuery:
- This function directly accepts computer names from pipeline input. This makes it trivial to run a query on a large number of SQL Servers.
- If you prefer, you can use the -computer parameter to specify one computer at a time. Often, this syntax is more terse than specifying a whole pipeline.
- The command provided with -commandText is executed on each computer, in turn.
- The output from each execution is aggregated into a single "mega result set".
- It is usually important to be able to determine which rows came from which computer because there is only one result set. As a matter of habit, I stick a @@SERVERNAME at the start of the SELECT clause of my query.
- The default query provides a quick overview of the server and includes version information, memory information, instance start time, etc. I use this as a connectivity test or to remind myself of what software is installed on a particular computer.
- The query that is sent to the server can be quite complex. The query can be multiline, but not multibatch. The query can create and use temporary tables. If you can run your query in SSMS, as long as you are not using the "GO" keyword, you should be able to run your query with Invoke-SQLQuery.
- The function can read a query from a file passed in with -commandFile. I have added this functionality only in the last few weeks.
- Invoke-SQLQuery uses Ping-Computer to ensure that the remote computer is at least booted before trying to connect to a SQL Server instance. This cuts down on the time it takes to fail when a remote computer is down or unreachable for whatever reason. Ping-Computer is a very close cousin of Brandon Shell's Ping-Server, having only few simple changes to match my scripting style. I'm presenting it here for your conveniance, but you should definitely check out Bradon's blog. I have learned much from it.
- Invoke-SQLQuery runs the given query on each computer, serially, one execution at a time. Clearly this will not scale to hundreds of servers, but I've been happily running this at a site with about 20 servers dozens of times a day for about a year now.
- This does not support multiple returned result sets. In other words, you can't issue two selects in your query and get two different result sets in a collection of some sort. Again, this has not hindered me.
- UPDATE, INSERT and DELETE are not supported. I have other scripts that can handle other sorts of DML.
- When loading queries from a file, you will get runtime errors if the file contains the "GO" keyword. I have not built in an fail-safe/sanity check for this yet.
To call a SQL Server system stored procedure on a remote computer and show only some of the returned columns:
Invoke-SQLQuery -computer "server_a" -commandtext "sp_helpdb" | ft -auto Name,Status
Run pretty much any query that you'd like on a single computer:
$cmdText = "select @@servername 'Computer', count(*) 'ConnectionCount' from master.dbo.sysprocesses"
$result = Invoke-SQLQuery -computer:"server_a" -commandtext:$cmdText
$result | ft -auto
Run the same query, but this time run it against two computers. The names of the computers are placed in an array. That array is then fed to a pipeline. It would be trivial to add additional computers to the array, which will cause the command to be run on those computers as well. It is also easy to load an array of computer names from a simple text file.
$computers = ("server_a", "server_b")
$result = $computers | Invoke-SQLQuery -commandtext:$cmdText
$result | ft -auto Computer,ConnectionCount
The output of that last command might look like this:
Computer ConnectionCount
-------- ---------------
SERVER_A 35
SERVER_B 82
Note that the contents of $result are a real Powershell object. That means that you can go and run something like this:
$result | sort ConnectionCount -desc | ft -auto Computer,ConnectionCount
Computer ConnectionCount
-------- ---------------
SERVER_B 82
SERVER_A 35
You can add additional pipeline items as you wish. For example, instead of (or in addition to) applying a sort, you could apply a where clause. This would be silly in my two-server example, but imagine that you have a hundred servers and you would like to know the top five servers, by number of connections. After running the queries and placeing the results in $result, you could run something like this:
$result | sort ConnectionCount -desc | select -first 5
You could stick your results in an Excel file:
$result | sort ConnectionCount -desc | select -first 5 | Out-Excel
You are only limited by your imagination.
Tuesday, June 3, 2008
Powershell Function: Change-VolumeLabel
This posting continues with my series of articles that describes my Powershell environment by describing a function that I use to change volume labels on remote machines. A file with the function is available.
I wrote this function because I needed a way to change labels associated with volumes on remote machines. Earlier versions of this function were actually written in VBScript. I rewrote that old code in Powershell because I wanted more consistency with the "standard" Powershell command line switches and I am (slowly) moving away from all of my old VBScript programs.
Before I had a script, I would use RDP to connect to the remote machine, login and then use Windows Explorer to change the volume labels. This was a lot of effort for a small change. I use the volume label at one of my clients as a quick way to discern between SAN and non-SAN volumes by prepending "san" or "local" on the volume labels. When this convention was adopted, there were a fair number of old machines to go through. I also use this script when setting up new machines, so this function has gotten a workout.
This function is pretty straightforward. The function has the following key features:
I wrote this function because I needed a way to change labels associated with volumes on remote machines. Earlier versions of this function were actually written in VBScript. I rewrote that old code in Powershell because I wanted more consistency with the "standard" Powershell command line switches and I am (slowly) moving away from all of my old VBScript programs.
Before I had a script, I would use RDP to connect to the remote machine, login and then use Windows Explorer to change the volume labels. This was a lot of effort for a small change. I use the volume label at one of my clients as a quick way to discern between SAN and non-SAN volumes by prepending "san" or "local" on the volume labels. When this convention was adopted, there were a fair number of old machines to go through. I also use this script when setting up new machines, so this function has gotten a workout.
This function is pretty straightforward. The function has the following key features:
- It leverages WMI to do the dirty work.
- It supports several of the "standard" Powershell command switches, namely "-verbose", "-confirm" and "-whatif". It does this by leveraging Jeffery Snover's "Should-Process" function. At a minimum, I try to support these three switches in all of my scripts.
- If you don't specify a drive leter, the script prints a short report showing the drives that exist. This supports the notion that an administrator should be able to "explore" or "discover" an environment with powershell and make changes interactively. It sure beats remembering things.
- There is one caveat with this script. It does not work with versions of Windows that pre-date Windows Server 2003. It seems that WMI did not provide the WIN32_VOLUME class prior to Windows Server 2003. If someone tries this funciton on Windows Vista, I'd love to hear if it works or not.
Subscribe to:
Posts (Atom)