Building an Active Directory Inventory – Part 1

There are few parts to building an automated Active Directory Inventory.  We need to be able to inventory the DCs and pull general OS information, pull the NTDS.Dit, and any FSMO roles that they hold.  The second part of this series will cover querying the domain to find what Microsoft OSs are being used in the environment.  Not all that important for most people, but something my management is always asking for. The third part will cover Forest/Domain information and pull the other parts together, into one report.

Again, I put all my functions into a separate file called StoredFunctions.ps1, this way I can call them from any file and reuse over and over again.

This first part will pull the following information from each DC in the domains:

  • Enabled
  • HostName
  • IP
  • DomainName
  • FQDN
  • OS
  • OSVersion
  • SPLevel
  • LastBoot
  • InfrastructureMaster (Does it hold the FSMO Role)
  • PDCEmulator (Does it hold the FSMO Role)
  • RIDMaster (Does it hold the FSMO Role)
  • DomainNamingMaster (Does it hold the FSMO Role)
  • SchemaMaster (Does it hold the FSMO Role)
  • GlobalCatalog
  • Site
  • NTDSLocation
  • NTDSSize
  • NetLogon
  • OtherShares
  • DN

You will be able to call this separately or together with the master inventory function.

When Export-CSV is not enough

Creating richer reports, when Export-CSV is not enough

I needed something more than the simple Export-CSV.  While it is very useful and quick, I needed something that I could add additional worksheets to and be management ready.  I found this code to create an Excel spreadsheet.  I modified the code to be in a function format and to fit my needs.  Now I can export any data from PowerShell into a nicely formatted Excel file.

To add another worksheet, I created another function, AddExcelWorkSheet.  This function looks for the Excel variable from the previous function and adds another worksheet.  It follows the same process as the original function.

Now to export data using the new functions:

. PrettyExcelExport $ExcelSheetName, $ExcelSheetTitle, $Columns, $Data, $filename, $CloseExcel



AD Inventory-Domain

AD Inventory

DC Inventory-Domain

DC Inventory

 Tip:  When exporting the data to Excel using the functions above, export the worksheet you want first, last.

Support Multiple AD Domains with PowerShell

Since my last post, I have started a new job and had a beautiful baby girl that has kept me pretty busy.  At my new job I am responsible for managing Active Directory about 20-30 different domains spread out over 5 forests.

I wanted to be able to manage all the domains remotely without logging on to each domain separately and also wanted to be able to automate many AD tasks that were being done manually.

I created a folder structure to help me keep things organized and made it easy for me to share with my other team members.  All of my scripts are based on this structure:

  • My Documents
    • PowerShell
      • Credentials
      • InputFiles
      • Results
      • ScriptFiles

The first four functions that I wrote store the general information for all the Forest/Domains that I now support, store my credentials for all the domains, load the credentials, and the function to switch between the domains.  I placed these functions in a file called, StoredFunctions.ps1.

The first function, StoredDomains, just holds the FQDN in variables for the domains.

The second function, StoredCredentials, is used to stored credentials for the domains.  It will store them in encrypted XML files, based off the machine key.

The next function, LogIn, loads the credentials from each of the encrypted XML files for each of the domains.

The last function, SelectDomain, is used as the switching mechanism to change between all the different domains.

Once you have your functions updated and saved, you can easily call them from any script.  When creating a new script, I call these functions:

To start using these functions, you will need to save your credentials.  Type

 . StoreCredentials

This will prompt you for your userid/password.  After all of your credentials are saved, you will be able to call and log in to any of your saved domains by calling the function.

. SelectDomain


. SelectDomain Domain1

As I have time, I will start to add more of the PowerShell scripts that I have created over the last 8 months, that includes automating Forest/Domain Inventory / Domain Controller Inventory, exporting to data to Excel and making it pretty, searching for files across every DC (Which can be modify to search any amount of servers), and a patching report (in the event you don’t have SCCM or better enterprise solution)

SharePoint Site Disk Space Usage

I often have the need to help clients figure out why their SharePoint site collection is so large or the best way to break sites into new site collections. SharePoint doesn’t offer an easy way to figure out the size of each site in a site collection and I didn’t have the time to figure out how to write a PowerShell script. So I had to think outside of the box.

One of my favorite daily tools is WinDirStat. It a tool that will give you a graphical view of disk usage. You can select disk drives or even narrow it down to one specific folder. So how can we utilize this for SharePoint sites?

SharePoint utilizes a technology called WebDav. This allows us to open up SharePoint as a folder in Windows Explorer and view all the files as we would on an Operating System.

So I opened up SharePoint to the site collection I wanted to get the space usage with in Windows Explorer.


Then I open up WinDirStat and enter in the path into the folder selection. Click Ok, and let it run.


It does take while, but you will get a nice report when it is finished. I just let it run over night. Note: I was running this over a VPN.  It should be faster if you are on-site.

This is the report you end up with:

WestPoint WinDirStat

PowerShell Script to Remove Unused Email Address from Active Directory Users

Recently, I was migrating a client from Exchange 2007 to Exchange Online. During the migration, I ran into issues with the customer having old email domains that they no longer wanted/needed. I removed the Email Domain policies, but the migration was still failing because the old email addresses were still associated with the users.

I was able to script up a PowerShell script that goes through all the users in the domain and removes the email addresses that are no longer needed.

Azure SQL Data Sync

I was able to get Azure SQL Data Sync Replication to work using SQL 2012 Express and SQL 2012 SP1 Express. With SQL 2012 Express, I was not able to sync the entire database at once, but I could do it by multiple tables.

1. Log on the Azure Portal,
2. Click on the SQL Databases Tab
Azure Portal
3. Click on New, Data Services>SQL Database>Quick Create
4. Name the Database, Hub
Create DB
5. Click on New, Data Services>SQL Database>Quick Create
6. This will be the Database that will be sync’d with the local server, name accordingly
7. Click on SYNC
8. Click on Add SYNC>New Sync Agent
Create Sync Agent
9. Name the Sync Agent and Select the same region as your databases
10. Click on Add SYNC>New Sync Group
11. Name the Sync Group, click next
12. Select the Hub Database and enter in the Username and Password for the Azure Server
Create Hub Sync
13. Select if you want the Hub or Agent to win if there is a conflict, click next
14. Select the database that you are going to sync to, Enter in the Username and Password
15. Select How you want the sync to work, Directional or Bi-Directional
Add Reference DB
16. Click on the Sync Agent and click Manage Key
17. Click Generate key and copy the key for later
Create Agent Access Key
18. Install Microsoft SQL Data Sync Agent ( on the SQL server that you want to sync with Azure.
SQL Data Sync Agent
19. Paste the key into the Microsoft SQL Data Sync Agent on the server by clicking, Submit Agent Key
Agent Key
20. Click Register and fill out the credentials to access the local database
21. In the Azure Portal, click on the Sync Group and configure the rules
You can select Automatic Sync and Sync by tables of Database
Configure Sync Rules
22. Click Save, Click Sync
23. Verify Data was sync’d to the Azure Database