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.
Here is what this looks like when I added the function and two new properties in the select:
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.