DFS Monitor and International DateTimes
So the Surly Admin readership is growing by leaps and bounds. That’s right, we’re up to 3 now! And Garth gets two prizes, one for being #3 and the second for finding a bug in the DFS Monitor with History. His prize, you might ask? Getting to help me fix the bug, how exciting is that? Read on to find out.
Internationalization and Powershell
The Interesting thing here, is that the ultimate culprit here is Powershell herself. That’s my story, and I’m sticking to it. As anyone in the U.S. knows, we usually notate our dates like so: MM/dd/yyyy, unless you’re in the Quality business in which case you could have this problem too. Unfortunately, the rest of the world tends to notate the date like so: dd/MM/yyyy. And for the most part, Powershell doesn’t really care. A [Datetime] field is a [Datetime] field. It’s format is completely flexible.
But in this instance, we’re also working with a CSV for data storage and CSV’s are about as dumb as you get. A field is a string. Period. And this is where I ran into the problem. DFS Monitor creates a PSObject and then pipes that into the Export-CSV cmdlet, which then takes the Datetime field for the Run time and creates a string for it in the localized format. Notice I said localized. So all the U.S. people using it get the “MM/dd/yyyy hh:mm:ss tt” format. But if you’re international you get “dd/MM/yyyy hh:mm:ss tt”. When DFS Monitor reads the data back in one of the first things it does is run through the data and convert the RunDate back to a [Datetime] format. Still works wonderfully until the date goes beyond 12. Once that happens (on the 13th of the month, for instance) the conversion from string to datetime will fail.
I thought this would be pretty easy to solve, but my first crack at it didn’t work too well. I simply took control of the datetime to string conversion when creating the CSV. Now we create a string, use the Get-Date function and reformat to FORCE saving in the MM/dd/yyyy format.
[string]$tempDate = Get-Date $ScriptRunDate -Format "MM/dd/yyyy hh:mm:ss tt"
And this worked great. For the NEW data. The problem was Export-CSV still converts the OLD data to the localized format. So with a blank data file your first run looks perfect. Second run will complete without error, but if you look at the DFSData.CSV file you’ll notice the first run is now formatted with the localized format and the second run has the correct format from above. Guess what happens when you try a third run? It fails again.
This time I reversed the code change above and completely took it out then I put in a simple loop just before saving the data to convert every [Datetime] field to string with the enforced format. Unfortunately, this failed too. The second problem is that normally I saved the data file before creating the HTML pages, and that code relies on that field being a [Datetime] type, not a [string] type.
That means we now have to move the saving the data file to the very END of the script, convert the [Datetime] fields to [string] and save to CSV. My good friend Garth tested for a couple of days just to make sure nothing else snuck up on us, and luckily it looks like everything is working fine now.
Moral of the story? You’re never really done with a script! I’ve already learned a lot more about Powershell since I wrote this script? Do I fix a few things to make it more elegant? Or do I just walk away because it’s working pretty well now?!
You can find the updated source code here.