Finding the Renamed Domain Admin Account

A good practice is to rename the domain administrator account in an Active Directory domain.  When doing auditing, you will want to know what the name of that account really is.  Get-DomainAdmin.ps1 is a script which will give you this information for any domain in your forest.  It requires the ActiveDirectory module to run.

Script Text

Get Holidays for Any Year and Any Country as PowerShell Object

This script started out as a project to figure out how to automate Excel web queries in PowerShell, so MS Excel is a prerequisite for this script.  If you have Excel, but have never used query tables, it is accessible by going to the Data tab, and then choosing “From Web”.  An overview from TechRepublic is here.  Web Queries have been around since Excel 2000, but I think it is a pretty obscure feature.

I was hoping to use the functionality for an internal project which had failed to work satisfactorily with any of the standard ways of pulling down web pages.  When I began to automate Excel in PowerShell, I found that things which I had mastered in VbScript were actually a bit harder in PowerShell.  This script shows the techniques of how to force Excel to close, and how to use SaveAs a CSV file.

Using a Web Query did not work for my internal project either, and this languished in my half-written pile for some time.  Earlier in the week, I had to stay late at the office while waiting for an appointment and decided to take the time to make something practical of what I had worked on.

Holidays can be a difficult to work with when calculating workdays.  I use TimeAndDate.com, which not only pulled off a most excellent domain name, but also is a very good resource for time and date information.  I urge you to visit this page often, especially if you plan to use this script which takes holiday data from them. They have put the data in a very friendly format for web queries; go to the website at http://www.timeanddate.com/holidays/ to see the countries available and their URLs.  Visit the URL for your country to see what sort of data is available from the website.

Get-Holidays.ps1 begins by getting a list of already open Excel process IDs and saves them.  It then opens Excel, does the web query and saves the results as a randomly named CSV file.  I try to close Excel gracefully, then delete the Excel PID which was not open when the script started.  The Import-CSV is used to import the data. (Yes, I know I could have collected it from the spreadsheet directly).  The temporary file is deleted.  The data is manipulated so that the text dates become date objects, and some of the fields are renamed.

The demo date stuff at the bottom shows how to select for a particular type of holiday and how to format the date object to show the date without the timestamp.

I have this written for the US http://www.timeanddate.com/holidays/us/, but you can see that the URL for the  UK it is www.timeanddate.com/holidays/uk/,  and for Canada is www.timeanddate.com/holidays/Canada/.  As written you have to change the the URL in the code to change the country.  I have added example code which gets the US National Holidays for 2015.

Script Text

Combine Multiple Excel Spreadsheets

I create a lot of Excel audit reports in a multi-domain environment.  When they go out, I need to combine the reports from each domain into a single Excel workbook with multiple worksheets.  I found some VBA code on mrexcel.com which was pretty easy to port to vbscript.  I put the script on the desktop and then select all of the files which I want combined, and drop them onto the script.  A new workbook opens with each of the original files as a worksheet page.  The original files are not deleted.  Note that if you line the files up in the order you want them before selecting them, you will get them in the order you want.  I hope you find CombineXLSheets.vbs makes this task a little easier. Script Text

Remote Windows Update 3.1

WindowsUpdate.hta version 3.1 is an HTML application which allows you to connect to a remote machine, determine what patches it requires from Windows Update, and install the patches.  You can schedule a reboot time. This version allows you to look at he Windows Update log, and the log created by the program itself.  There is a button to allow you to change the update source to windowsupdate.com, which is helpful in places where WSUS or SUP is not working properly.  You can install all security patches, or select patches individually.

HTA files are best run from your local drive. Version 3.0 was released in 2011, version 3.1 only changes the background color to blue.  The transition color method I had used for the background is no longer supported in IE, and the program appeared to be broken.

Change _hta.txt extension to .HTA.
Script Text

Get Computer’s Distinguished Name in PowerShell (Revised)

Getting the DN for an individual computer is not a difficult task in PowerShell, ex:

Import-Module activedirectory                                                                            
$(Get-ADComputer $env:COMPUTERNAME).distinguishedName                                                    



									

Doing this fast for a long list of machines in a big forest is less easy.  Get-ComputerDN.ps1 gets the distinguished name of a list of computers using WorkFlows. I spent some time trying out how to figure out the best way to do Active Directory Global Catalog searches in an Active Directory forest with many domains, such as level2.level1.root.com.  I don’t want to query root.com when I know that the computer account exists under level1.root.com.  Similarly, I don’t want to query outside my domain, level2.levels1.root.com when I know the computer account exists there.

What this script did for me was to get some hands on with parallel processing using workflows and  to help me to decide how I want to manage Global Catalog queries in the future.  The original version of this script looked pinged the computer name and looked for the DN.  This version is much more sophisticated.  If you give it a list of NetBIOS names it will figure out the FQDN based on the LDAP query.  It will figure out the closest Global Catalog Server for the domain you are querying by using the ping response time.  I am using ParameterSetName to manage the choice of the domain level to query.  You can see from the commented code I default to the forest, but let the user select the current domain or the parent domain.  I also accept either a computer name or a file path as input. Unlike prior versions, the script will always ping the computer even if it is not in AD.  It requires PS Version 3.

Updated: 1/30/14 to fix ping problems. 2/15/14 major rewrite.

Batch file to Run PowerShell Scripts

I have a couple of PowerShell scripts which I run daily, and the number keeps growing.  I wanted a quick way to run scripts which support alternate credentials.  Drag and drop a PowerShell script onto  the RunPS.cmd  batch and you are prompted to run with it with your current credentials, alternate credentials, using RunAs, or SmartCard authentication.  If you drop a shortcut to a script onto it, the batch file will find the target and launch that by creating a temporary vbscript on the fly.