Powershell - What Active Directory Sites and Subnets are being used?

Why reinvent the wheel?  The reason I ask this is I ran into an interesting challenge and wanted to share how I solved this issue. Active Directory does very little to provide Domain Admins with the capability to audit the use of sites and or even the subnets that are actually being used.  This can often cause sites and services to become bloated and unmanageable.  I set out to figure out a way as best as we can to solve this problem. 

Based on data provided from the get-adcomputer cmdlet.  The ipv4 address became my focus. but I really didn’t want to have to do the work (I'm Lazy) to try to figure out the subnet based on the IP and wanted something a little more reliable.  I ran across several forum discussion where people used nltest to get site data.  My first attempt at this, I found on the internet where someone was passing the hostname of the computer to nltest /server:computername /dsgetsite.  This worked great at first but I forgot that if the computer was offline there was no way to get this information and it seem to take forever in a large environment. 

I then ran across this on the web LINK.  It’s the last comment. They actually suggest using nltest /dsaddresstosite:ipaddress /dsgetsite

What I like about this is I can use the function to pass any IP address both offline and online.  So I combine this with the stale computer object script I put together.  I like having the stale account info to help me validate the report.

My Guidance on Identifying Stale Computers Objects in Active Directory using PowerShell

Here is what this looks like when I added the function and two new properties in the select:

 #---newely added function to translate ip to site with nslookup----
  
 function Get-ipSite
 {
     param([string]$ip
     )
     #Great idea from https://superuser.com/questions/758372/query-site-for-given-ip-from-ad-sites-and-services/758398
     $site = nltest /DSADDRESSTOSITE:$ip /dsgetsite 2>$null
     if ($LASTEXITCODE -eq 0) {
         $split = $site[3] -split "\s+"
         # validate result is for an IPv4 address before continuing
         if ($split[1] -match [regex]"^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$") {
             "" | select @{l="ADSite";e={$split[2]}}, @{l="ADSubnet";e={$split[3]}}
         }
     }
  
 }
  
 $d = [DateTime]::Today.AddDays(-90)
 $default_log = $env:userprofile + '\Documents\Computer_Site_Report.csv'
  
 Foreach($domain in (get-adforest).domains){
     Get-ADComputer  -Filter {(isCriticalSystemObject -eq $False)} -Properties UserAccountControl,`
         PwdLastSet,WhenChanged,SamAccountName,LastLogonTimeStamp,Enabled,admincount,IPv4Address,`
         operatingsystem,operatingsystemversion,serviceprincipalname  -server $domain |
         select @{name='Domain';expression={$domain}}, `
         SamAccountName,operatingsystem,operatingsystemversion,UserAccountControl,Enabled, `
         admincount,IPv4Address, `
         @{Name="Site";Expression={if($_.IPv4Address){(get-ipsite $_.IPv4Address).ADSite}}}, `  #<----Site
         @{Name="Supnet";Expression={if($_.IPv4Address){(get-ipsite $_.IPv4Address).ADSubnet}}}, ` #<----Subnet
         @{Name="Stale";Expression={if((($_.pwdLastSet -lt $d.ToFileTimeUTC()) -and ($_.pwdLastSet -ne 0)`
         -and ($_.LastLogonTimeStamp -lt $d.ToFileTimeUTC()) -and ($_.LastLogonTimeStamp -ne 0)`
         -and ($_.admincount -ne 1) -and ($_.IPv4Address -eq $null)) -and `
         (!($_.serviceprincipalname -like "*MSClusterVirtualServer*"))){$True}else{$False}}}, `
         @{Name="ParentOU";Expression={$_.distinguishedname.Substring($_.samaccountname.Length + 3)}} `
         | export-csv $default_log -append -NoTypeInformation
 }

After running the script I have a nice little csv report I can perform some excel magic with.

Open the Computer_Site_Report.csv, which should be in your documents folder, in Excel.

Lets create some reports with the data now

Click in the upper right corner of the spreadsheet. to highlight all the rows and columns. select insert on the menu bar, select pivot chart , and then pivot chart again.

Select OK on the create Pivot Chart windows

Make the chart 1 area larger, then on the right side of the screen under pivot chart fields make it look like the following.

This is what your graph should look like.

*N means subnet not defined to a site. 

For grins you can also drag Domains or Operating Systems into the Legend field to display data differently.

This will change the chart to look like this.

Based on this information I can decide if all the sites I have created are truly needed. 

What about Subnets?

Make the fields look like this

Using the table part of the graph I'm going to sort the subnets descending by selecting the down arrow by row labels, then more sort options.

Select descending, and the drop down for count of name

Now I have this.

Next step will be to compare these to the actual Subnets defined in sites and services.  I will save that for another blog entry.

This isn't a perfect solution but in a pinch is a quick and easy solution. I hope you find this useful I sure did.

-Chad

Comments

• Anonymous
  January 18, 2017
  Hello!Thanks for the snippet. It’s very helpful to me!I need to grant access to a particular user and machien for a ServerContainer (“Servers” node) under a specific site. Is it also doable via PowerShell? Or should I use WMI to achieve it?I have browsed lots on the internet but I cannot find any good solution.Any suggestion will be appreciated.Many thanksMichal
  • Anonymous
    February 13, 2017
    did you get an answer to this?
• Anonymous
  February 26, 2017
  Great snippet. I needed site and subnet info.. and this is perfect!