Share via

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

  • Article

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.

image

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.

image

Select OK on the create Pivot Chart windows

image

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

image

This is what your graph should look like.

image

*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.

image

This will change the chart to look like this.

image

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

image

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.

image

Select descending, and the drop down for count of name

image

Now I have this.

image

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!