Wednesday, April 25, 2012

VBScript to extract and document AD Site Information

I have written a vbscript to help me to extract my AD Site Information with hundreds of Sites, Subnets, Domain Controller Servers and Site Links.  The extracted information will be populated into an Excel spreadsheet on the fly.

Here is a sample of the Excel spreadsheet.

AD Site Information

The above sample shows that there are five AD Sites and the Subnets associated with each AD Site.  It also shows the Domain Controller Servers in each of the AD Site.  The Site Links column shows the Site Links which the AD Site is part of it.  The Sites In Site Link column shows the AD Sites that are connected by the Site Link.

Take VMSITE1 as an example:

  • The Domain Controller is VMDC01.
  • 10.10.5.0/24 and 10.172.25.0/24 are the subnets associated to it.
  • It is part of VMSITE1-VMSITE2 and VMSITE1-VMSITE5 Site Links.
  • VMSITE1 and VMISTE2 are connected by VMSITE1-VMSITE2 Site Link.
  • VMSITE1 and VMISTE5 are connected by VMSITE1-VMSITE5 Site Link.

If you are interested in using this vbscript, you can download a copy from http://www.mediafire.com/download.php?b27rl2ce48xqsay.  Extract the zip file to a folder.  Go to the command prompt and change to the folder having the vbscript.  In the command prompt, use the following command to run it.

Cscript SitesInfo.vbs

Note: The computer running the vbscript needs to have Microsoft Excel installed.

2 comments:

Monsieur RaKah said...

Hi Sir..
Since currently i'm in similar research, could you please share with me about the method of parsing specific data from AD into XLS that you used.
I already made VBA to get "FullName" and "UserName" by method: Extract from DC within CMD into TXT, then my VBA process TXT into XLS.
Thanks for your kind attention. :)

Alex Siow said...

Hi Rha-K,

To get information out of AD, I did a LDAP query using ADO connection and ADsDSOObject as the provider. After the query has been executed, I will loop through the recordset to read the value from the fields I want.

To pump the data into Excel, the script creates COM object using the statement WScript.CreateObject("Excel.Application"), add a workbook and use the Cells property to set value in a cell of a certain position. http://www.activexperts.com/activmonitor/windowsmanagement/scripts/msoffice/excel/ has a good tutorial on how to do this.

You can also download a copy of my script at http://www.mediafire.com/download.php?b27rl2ce48xqsay and use it as a reference.