Using PowerShell to represent Base 26 as the uppercase English Alphabet
Today I was asked to do something that seemed simple, until I actually had to do it. A coworker had a database with two fields he wanted renamed in a specific way. For our example, lets call them ProductNumber and ProductName. He wanted ProductNumber to be sequential (1, 2, 3 . . .) and the ProductName fields to be called “Product A”, “Product B” . . . “Product Z”, “Product AA” etc. So this suddenly became a non-trivial problem if you had more than 26 rows, which of course I did.
So I rolled up my sleeve, got a fresh cup of coffee, and got to work. Populating ProductNumber was easy enough using a Common Table Expression (CTE) with a ROW_NUMBER(). Then I realized I could think of the English alphabet as symbols for a base 26 number system, with AA following Z and so on. The only problem was I couldn’t express that in a set based way for a clean T-SQL implementation. No problem, I’d just generate the T-SQL to make a giant mapping table in PowerShell!
I am ashamed to admit I had to look up the algorithim for converting from base 10 to another number. I was also surprised to discover that the first result google returned me was this tripod page.
The algorithm is as follows.
- Start with an empty string which becomes the return value
- While the value is greater than the base get the remainder of the value divided by the base. Convert that to its letter and prepend that to the return value
- Repeat step 2 with the quotient of the value over the base.
- When the quotient is less than the base, prepend that to the string instead.
It seemed simple enough, but there were some headaches.
The first thing I discovered was that when you divide integers in PowerShell, you get a float as a result. Also casting it back to an int rounds instead of truncating the results. I was expecting the opposite in both cases, because that is how C# behaves. I ended up using the unwieldy combination of Math.Floor() and a cast in the form [int][math]::Floor($currVal / 26) to resolve this. The MSDN technet has an article that recommends the more unwieldy [Math]::floor([int] $currVal / [int] 26), but I proved that my terser method gives the same results.
Then I had problems with how to display powers of 26. The way it was supposed to work was that 1 = A, 24 = X, 25 = Y, 26 = Z and 27 = AA. However, depending on how I did it I ended up with 26 = AZ or 27 = BA. I could not account for this edge case, nor compensate for it with special conditions.
Then it dawned on me, A needed to be equal to zero not one. A base 10 system deals with the digits 0-9. Base 2 deals with 0 and 1. Base 16 deals with 0-F and F is 15. Once I rewrote my script to work that way, edge cases disappeared, and things just worked.
The script
function Convert-ToLetters ([parameter(Mandatory=$true,ValueFromPipeline=$true)][int] $value) {
$currVal = $value;
$returnVal = '';
while ($currVal -ge 26) {
$returnVal = [char](($currVal) % 26 + 65) + $returnVal;
$currVal = [int][math]::Floor($currVal / 26)
}
$returnVal = [char](($currVal) + 64) + $returnVal;
return $returnVal
}
If its not clear how I generated upper case letters, the ASCII codes for A through Z are 65 through 90, and casting an integer to a char converts it to its ASCII code. Ergo, the expression [char]65 evaluates to “A”.
So now here’s the function in action:
1 .. 100 | ForEach-Object {
$_ | Convert-ToLetters
}
Happy Scripting!
Making an RDP connection to a server you just rebooted with powershell
It’s an all to familiar story. You need to reboot a server, and then you need to start a remote desktop connection into it. So what do you do? You open up a command prompt, type ping -t <HOSTNAME> and wait until the server responds to pings. When that happens, you keep trying to connect via remote desktop, until it works. There’s got to be an easier way. You should write a PowerShell script to automate the process. However, its one of those things that not quite annoying enough to get you to actually take action and write the script. Luckily, thanks to the power of twitter, I reached a tipping point this week, and wrote the script. It all started out with some innocent whining:
@zippy1981
Justin DearingYou know what I need in an RDP client. I need an “”I just rebooted so ping it for me and autoreconnect”.
2 days ago via web · powered by @socialditto
Then d0tk0m and Yanni Robel retweeted my whining. They say necessity is the mother of invention. In this case the commiseration off two tweeps was the father. So I spent a Saturday with PowerGUI, and came up with a script to solve the problem.
Planing stage
I wanted my script to automate what I already did. From the perspective a system administrator that wants to reboot a server and then remote desktop into it, the following happens.
- All the processes, including the remote desktop service (termsrv.dll) are shut down. When that happens the remote desktop port (default 3389) no longer has anything listening on it.
- Eventually the network adapter is shutdown and it will stop responding to ICMP echo requests, or pings.
- The server will finish shutting down, the BIOS will POST, and Windows will begin booting
- Eventually the network adapter will come up and start responding to pings.
- The remote desktop service will start and bind to the remote desktop port.
Therefore, I made my script to do the following.
- Ping the server until it answered five successive ping requests. Yes this might be naive and optimistic in many cases. However, it worked in my use case. I used the Send() method of the .NET System.Net.NetworkInformation.Ping class to do this.
- Once I was sure the host was up, I’d try to connect to it on port 3389, or another port if I passed a different port as a parameter. To do this I used the System.Net.Sockets.TcpClient class.
- After this it was simply a matter of passing the right parameters to the remote desktop client, mstsc.exe. I initially attempted to use the simple
& mstsc <Arguments>. However, that didn’t work to well so I ended up resorting to Invoke-Expression.
The script
Below is the current version of the script, hosted on poshcode.org. The current version is stored in gist repository. While you are free to post changes to poshcode.org (or anywhere), in accordance with the license, I’d prefer if you notified me of any changes so that they may be placed in the gist git repo.
Using MongoDB in Powershell
Readers of this blog know that I’ve been using MongoDB for a while, and I’ve recently become very excited about Powershell. Well recently I’ve been able to combine the two together for pure dynamically typed, schema-less, non-relational awesomeness. Such awesomeness is begging to be shared.
To get started you will need version 1.1 of the official 10gen CSharp driver for Mongodb. At the time of this writing 1.1 has not been released, so your going to have to pull the latest version from github and compile it yourself. The minimal revision of the trunk you will need is this one. First download and install the MSI (or download the source, build the MSI and install it). Then open up your favorite text editor or Powershell IDE. Personally I am a fan of Powershell ISE or plain old vim, with some configuration modifications. Now we will load the bson and driver DLLs via the Add-Type cmdlet.
# Check to see if we are running the 64 bit version of Powershell.
# See http://stackoverflow.com/questions/2897569/visual-studio-deployment-project-error-when-writing-to-registry
if ([intptr]::size -eq 
{
$mongoDriverPath = (Get-ItemProperty "HKLM:\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v3.5\AssemblyFoldersEx\MongoDB CSharpDriver 1.0").'(default)';
}
else {
$mongoDriverPath = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\.NETFramework\v3.5\AssemblyFoldersEx\MongoDB CSharpDriver 1.0").'(default)';
}
Add-Type -Path "$($mongoDriverPath)\MongoDB.Bson.dll";
Add-Type -Path "$($mongoDriverPath)\MongoDB.Driver.dll";
Since the Csharp Driver MSI is 32 bits, it creates the registry entries in the Wow6432Node. Therefore, we have to check to see if we are running in the 32 or 64 bit version of Powershell . Credit to an anonymous commenter on the msgoodies blog for providing this size of a pointer trick to determine if you are running a 32 or 64 bit system.
The next thing we want to do is to create a BSON document. This is surprisingly easy.
[MongoDB.Bson.BsonDocument] $doc = @{
"_id"= [MongoDB.Bson.ObjectId]::GenerateNewId();
"FirstName"= "Justin";
"LastName"= "Dearing";
"PhoneNumbers"= [MongoDB.Bson.BsonDocument] @{
'Home'= '718-555-1212';
'Mobile'= '646-555-1212';
};
};
As you can see Powershell can convert a HashTable to a BsonDocument. This is because of the public constructor BsonDocument(IDictionary hashTable). Powershell can use these one parameter constructors to cast an object. You can use the same Hashtable trick for the QueryDocument and UpdateDocument classes.
Now that we have our BsonDocument, its time to perform basic crud operations.
$db = [MongoDB.Driver.MongoDatabase]::Create('mongodb://localhost/powershell');
$collection = $db['example1'];
Write-Host "Insert";
$collection.Insert($doc);
$collection.FindOneById($doc['_id']);
$updates = @{'$set' = @{'email'= 'justin@mongodb.org'}};
$query = @{"_id"= $doc['_id']}
Write-Host "Update";
$collection.Update([MongoDB.Driver.QueryDocument]$query, [MongoDb.Driver.UpdateDocument]$updates);
$collection.FindOneById($doc['_id']);
Write-Host "Delete";
$collection.Remove([MongoDB.Driver.QueryDocument]$query);
$collection.FindOneById($doc['_id']);
This gives us the output below.
_id 4dc5e5a43b42bd41d0154679
LastName Dearing
PhoneNumbers {Mobile=646-555-1212, Home=718-555-1212}
FirstName Justin
Delete
PS E:\src\gist\gist-854911> powershell.exe -file .\MongoTest.ps1
Insert
Name Value
---- -----
_id 4dc636c33b42bd3d7c8dc3a4
LastName Dearing
PhoneNumbers {Mobile=646-555-1212, Home=718-555-1212}
FirstName Justin
Update
FirstName Justin
LastName Dearing
PhoneNumbers {Mobile=646-555-1212, Home=718-555-1212}
_id 4dc636c33b42bd3d7c8dc3a4
email justin@mongodb.org
Delete
As you can see, its not very hard to use the 10Gen MongoDB Csharp driver from within Powershell. Using Powershell with the MongoDb C-Sharp driver has many possibilities. First of all, adhoc mongodb queries from inside of powershell. Secondly, The code for this example is available in its entirety here.
An Array of one item in Powershell
Powershell is a great scripting language. One of its greatest features is that you have the full power of the .NET framework at your disposal when you use it. However, Powershell is a dynamically language, and the .NET CLR was written for static languages. Because of this, sometimes when you call CLR assemblies in Powershell, the types do not get resolved correctly.
I ran into this recently while calling New-Object to invoke the MemoryStream constructor that takes an array of bytes as a parameter. Powershell did not automatically turn byte[] into object[] {byte[]}. Instead I got an error.
I initially solved my problem the hard way. I created a List<byte>, add the the array to the list and called List.ToArray(). Eventually, I figured out how to do it correctly thanks to this MSDN Powershell Tip of the Week. To make an array of one item you precede it with a comma (I tried placing the comma afterwards during my initial experiments). Since the argument list is not the first parameter of New-Object, I needed to wrap it in parenthesis. So I ended up with New-Object System.IO.MemoryStream (,$bytes) which worked as illustrated below.
How many ways can we ‘script’ a stored proc or UDF from the command line?
Update: Added link to the scripts on github.
If you’ve worked with SQL Server for any length of time, you’ve probably written some stored procedures and user defined functions. If you’re like me, you’ve written many. Usually, when you edit a stored proc or UDF. you do it from Visual Studio or Sql Server Management Studio (SSMS). However, what if you want to script the sproc or udf from the command line? Well, there are quite a few ways to skin this cat, and I’ll go through a few.
Using sqlcmd
The simplest way to do this is the command line client that ships with SQL server 2005 and up, sqlcmd, and a system stored proc called sp_helptext. For our example lets have sp_helptext script itself.
sqlcmd -S .\sqlexpress2k8r2 -d master -h-1 -s"" -W -w 1024 -Q "EXEC sp_helptext 'sp_help'" -o sp_helptext.sql
Lets breakdown the arguments:
- -S .\sqlexpress2k8 this is the named instance of sql server I am connecting to. If you want to connect to the local default instance, you can omit this
- -d master This is the database we want to connect to. This can be omitted if you want to script an object from the users default catalog.
- -h-1 -s”" -W -w 1024 These format the output of sqlcmd in a way that makes sense for this task. If you want to understand these options, look at the sqlcmd msdn page, or this stackoverflow question.
- -Q “EXEC sp_helptext ‘sp_helptext’” -Q means “execute this T-SQL and exit.
- -o sp_helptext.sql This writes the command output to the text file sp_helptext.sql
Using a batch file
Now we could easily wrap this in a batch file as I demonstrate here:
C:\Users\zippy\Documents\deleteme>type scriptProcOrUdf.cmd
@echo off
sqlcmd -S %1 -d %2 -h-1 -s"" -W -w 1024 -Q "EXEC sp_helptext '%3'" -o "%3.sql"
So we saved ourselves a few keystrokes with a 2 line batch file.
Using Powershell
Batch files work, but they’re so 2002. Powershell is the CLI of choice these days on windows. So lets see how we would do this in powershell:
So apparently it took me 26 lines of Powershell (if you remove the BSD License) to accomplish what I could have done in a 2 line batch file. Whats the point you might be asking? Well, honestly if you just want the quickest way to script stored procedures to a file from the command line, I’d use the batch file. However, if you wanted to make changes to this script, for example, make it script all stored procedures and udfs in the database. You’d have an easier job with Powershell. You could turn the body of the script into a Poweshell function that takes a SqlConnection object and object name as a parameter. You could then get a list of all the stored procedures and functions with the T-SQL statement SELECT [ROUTINE_NAME], [ROUTINE_TYPE], [ROUTINE_DEFINITION] FROM [INFORMATION_SCHEMA].[ROUTINES]. I’ll leave writing that script as an exercise to the reader.
Using Powershell and “Original Thinking” to do it in less than 26 LOC
In the fictional Star Trek universe, Denny Crane, James T. Kirk cheated on the Kobayashi Maru by reprogramming the no-win scenario to allow him to win. He was awarded for his original thinking. Our scenario is less dire. However, like Kirk, we can change the rules. I chose to use a the open source .NET assemble Atlantis.SchemaEngine.dll. This dll is used for synchronizing schema between two SQL server databases. We don’t want to synchronize or compare anything. However, the library synchronizes database schema’s by generating SQL scripts. Therefore, it can generate DDL for any script-able object in a database. So lets take a look at our leaner, meaner script.
So we went from 26 to 15 lines. That’s a savings of 42%. Some of those are pretty long lines since we have to fully qualify our namespaces in Powershell. However, its a net simplification compared to making the ADO.NET calls directly.
Conclusion
I showed you 4 ways to script a stored proc. I also left plenty of room for you the reader to explore on your own. The code for ScriptProc.ps1 and Script-Object.ps1 lives in the justaprogrammer powershell github repo. If you have any improvements, feel free to leave a comment or pingback below. Also, pull requests are always welcome. Happy Scripting!
Cleaning up your path in PowerShell, and adding stuff to it.
A while back, I demonstrated some PowerShell one-liner-fu for path management. I also pointed out that my machine had duplicate entries in its path. Most people would not care about this at all. Most of the few people that do care would cleanup their path by hand. However, there are a mentally deranged few who realize the world needs a PowerShell script to clean up our paths for us. Luckily for you, I am that kind of crazy. However, I don’t stop there. I also show you how to search the registry to add items to your path.
Warning: I don’t know much about PowerShell and I’ve not tested this script nearly enough. This thing messes with your system path, and things can go really bad if you run it and it messes up your path. Be very careful running it. If you run it your the fool who follows a fool, which makes you Han Solo, not Ben Kenobi. However, your shooting your %PATH% not Greedo.
The script makes use of multi dimensional arrays and foreach loops. I’d be lying if i said that it was efficiently written or bug free. However, it works and does a few clever things with PowerShell.
So with that out of the way, the script is located in a github repository in our newly created github organization I mentioned before. The script is called Path-Fixer.ps1. It is available under the MIT license. I hope to improve it as time goes on. If you fork it and improve the script, please send us a pull request.
Notes about this script
Your path is a combination of two Environment variables. Those are the machine %PATH% and the user %PATH%. My script only concerns itself with the machine level path. Therefore I don’t use $ENV:PATH, like I do in my one-liner example to get the original path. Instead I use [Environment]::GetEnvironmentVariable(‘PATH’, ‘machine’).
Another thing I do is trim off trailing slashes at the end of the path. This means C:\windows\ becomes C:\windows. This may seem like excess, but it also allows me to remove duplicates that only differ because one has a trailing path, and one lacks it.
Two other caveats exist with regard to comparison. One is differences in case. Luckily, String.Replace() is case insensitive by default so my script handles it. The other is environment variable expansion. On windows if you stick %SYSTEMROOT%\system32 in your path, the OS will expand that to c:\windows\system32. My script does not handle this at the moment. Hopefully it will in the future.
I have some very simple registry detection to detect if a few programs are installed. This will probably remain a hard coded list of programs, and registry entries that determine the installation path. I’m hoping to also add detection through windows services. This would be good for something like mongodb that lacks an installer, but can self install as a windows service.
Finally, I’d like to point out that the script is UTF-16 encoded. This is simply because I wrote it script in PowerShell ISE, which decided to save it in UTF-16 format. Rather than change it to UTF-8 so git could easily diff it, I decided to fix git. This stackoverflow question provided the guidance I needed.
Conclusion
Once again, I’d like to reiterate that you must be careful when using this script. There might be some serious bugs, and messing with your path can lead to bad things happening. Also, while I will probably blog about updates to the script, the fastest way to keep track of its changes is to look at the github commit notes, and the source code itself. Finally, if you find a bug in my script or make and improvement, patches will be accepted, and due credit given.
Two Powershell One-Liners: Appending to my %PATH% and Whats in my %PATH%
To say I’ve fallen in love with PowerShell is an understatement. PowerShell is what perl would be; if perl was object based instead of stream based, and lacked all the “culture” of perl. I use PowerShell for a lot of things lately. Recently, I’ve been using it to manage my path, thanks to this PowerShell tip of the week. This has inspired me to share two one-liners related to path management.
The first allows you to add an item to your path. Few things annoy me more than windows programs that don’t include installers, and few installers annoy me more than those that install console programs and don’t offer to update your system path. Correcting this used to be a matter of going into nested levels of dialog windows. However, I can now do it from the PowerShell console. The command to append to your path is as follows:
[Environment]::SetEnvironmentVariable("PATH", [Environment]::GetEnvironmentVariable("PATH", 'Machine') + ';c:\Program File\foo\bin',"Machine")
A brief dissection:
- [Environment]::SetEnvironmentVariable calls the static .NET method Environment.SetEnvironmentVariable()
- “PATH” is the variable we are setting
- $ENV: is a powershell drive for environment variables.
- $ENV:PATH + ‘;c:\Program File\foo\bin’ concats your current path with a string.
- “Machine” makes this environment variable apply to the entire machine, as opposed to the current user.
So there you have it, instead of digging three levels deep into menus to edit your path, you simply dig up this blog article and copy and paste that one line into PowerShell 
The next one-liner is to determine what’s in your path. Simply typing $Env:PATH will print out your path in the same manner as the cmd.exe command path does. However, you probably want something more human readable. How about if each folder on your path was on a new line and the paths were sorted? Well thats quite easy:
$ENV:Path.Replace('"', '').Split(';') | Sort-Object -unique
On one of my machines this outputs:
%SystemRoot%\system32\WindowsPowerShell\v1.0\ C:\Program Files\Common Files\DivX Shared\ C:\Program Files\Common Files\Microsoft Shared\Windows Live C:\Program Files\Cppcheck\ C:\Program Files\Git\cmd C:\Program Files\GNU\GnuPG\pub c:\Program Files\GnuWin32\bin C:\Program Files\Java\jdk1.6.0_18\bin C:\Program Files\KDiff3 C:\Program Files\Microsoft SQL Server\100\DTS\Binn\ C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ c:\Program Files\Microsoft SQL Server\90\Tools\binn\ C:\Program Files\mongodb-win32-i386-1.6.1\bin C:\Program Files\mtasc-1.14 C:\Program Files\Subversion\bin C:\Program Files\TortoiseGit\bin C:\Program Files\TortoiseHg\ C:\Program Files\TortoiseSVN\bin c:\Program Files\Vim\vim72 C:\Program Files\WinMerge C:\Program Files\Zend\MySQL51\bin C:\Program Files\Zend\ZendServer\bin C:\Program Files\Zend\ZendServer\share\ZendFramework\bin C:\Python26\Scripts C:\Windows C:\Windows\system32 C:\Windows\System32\Wbem C:\Windows\System32\WindowsPowerShell\v1.0\ D:\apps\apache-ant-1.8.0\bin D:\apps\jboss-4.2.2.GA\bin
So lets break this down:
- $ENV:Path is our path as before.
- .Replace() is string.Replace(). Some of your path folders might be quoted, removing the quotes will cause them to be properly sorted.
- .Split(‘;’) is String.Split(). We want to turn our path into an array of strings, one folder per string.
- | Is a pipe. This works like cmd.exe and unix piping, excet its object based, not stream based.
- Sort-Object -unique does exactly what you think it does. It sorts the folders in your path and removed duplicates. Sometimes paths contain a folder multiple times. Cleaning up the duplicates will be addressed in a future blog post.
This one liner is good to run when you inherit someone else’s workstation, or a server setup by someone else. One limitation it has is it does not expand environment variables. Ideally I’d like it to expand %SystemRoot% to C:\Windows. However, Sort-Object is case insensitive by default, like the NTFS file system, so casing issues are not a problem.
Thats all for now folks. Happy scripting!
Impressions of PowerShell: Taking Buck Woody’s EventLog example further.
This is not quite a PowerShell first impressions article. I’ve toyed with PowerShell a few times before previously. Most notably, I toyed with the PowerShell TFS cmdlets that come with Team Foundation Server Power Toys a little under a year ago. However, I never stuck with it long enough to retain any of the syntax. Recently, I discovered a one liner by chance in Buck Woody’s blog. This lead me to do some serious PowerShell tinkering today. I’m not quite a seasoned PowerShell novice, but I believe I am now on my way there.
So here is the script in question:
Get-EventLog System | Where-Object { $_.EntryType -eq “Error” }
It is a simple one liner to get all the errors in your event log. The main work horse is the Get-EventLog cmdlet. I spent a good chunk of time playing with it. I came up with a few iterations:
- Get-EventLog -Log System -EntryType ‘Error’ # Skip the step of piping through the event logs
- Get-EventLog -Log System -EntryType ‘Error’ -After (Get-Date).Date.AddDays(-5) # Errors From the past 5 days
- Get-EventLog -Log System | Where-Object { $_.EntryType -eq ‘Error’ -and $_.TimeGenerated -gt (Get-Date).Date.AddDays(-5) } # Alternate one liner to get errors from the past 5 days with Where-Object
I tried several other permutations, most of which did nothing. I will make note of one thing here, which I will delve into more in a followup post. That something is the pipe “|” character. It works almost exactly as any windows or unix command line guru would expect. Namely, it “pipes” the output from the program on the left into the input of the program on the right. However, PowerShell is object based, unlike unix, dos and windows shells, which are based on streams of text. Therefore, you can pipe objects as well as strings with PowerShell. When piping to and from cmdlets, you are piping objects. The examples above that use the Where-Object cmdlet makes the implications of this clear.
One thing to note here is speed of return. One would think that the event log is indexed by date, and that I could reduce my query time by only returning recent entries. Searching the whole event log should be expected when using the Where-Object cmdlet, since all log entries are being queried and piped to another program. However, one would think that the Event Log would be indexed and the Get-EventLog command written in such a way that only a subset of the log entries would have to be traversed when you specified the -After parameter. However, when you run both examples, the command “hangs” for a bit between the last row output and the command prompt being displayed.
In my next article we will throw grep and less into the mix, and see what happens when we mix object piping with text piping.
Further Reading
Links that will be useful to play around with this stuff.
- http://technet.microsoft.com/en-us/library/ff730960.aspx Date/Time manipulation in powershell.
- http://msdn.microsoft.com/en-us/library/system.diagnostics.eventlogentry_members.aspx The MSDN Page that lists all the members of the .NET class EventLogEntry.