{"id":1127,"date":"2014-01-21T22:33:07","date_gmt":"2014-01-21T14:33:07","guid":{"rendered":"http:\/\/www.jhouseconsulting.com\/?p=1127"},"modified":"2014-01-23T20:00:54","modified_gmt":"2014-01-23T12:00:54","slug":"script-to-find-missing-subnets-in-active-directory","status":"publish","type":"post","link":"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/2014\/01\/21\/script-to-find-missing-subnets-in-active-directory-1127","title":{"rendered":"Script to Find Missing Subnets in Active Directory"},"content":{"rendered":"<p>This PowerShell script will collect all Netlogon.log files from the Domain Controllers, export the last x lines and combine it into one file of unique IP Addresses\u00a0<span style=\"line-height: 1.5em;\">in CSV format. This easily and simply allows you to then identify any missing subnets that need to be added and associated to an Active Directory Site.<\/span><\/p>\n<p>Yes, there are a couple of good examples of this type of script already available on the Internet. The trouble with them is that they would not produce reliable results, especially across environments where Domain Controllers were not all at the same Windows Server versions. Not a great practice, but it does happen in the larger environments where migrations are completed in phases. I&#8217;ve previously blogged about <a href=\"http:\/\/www.jhouseconsulting.com\/2013\/12\/13\/a-change-to-the-fields-in-the-netlogon-log-file-from-windows-2012-and-above-1029\" target=\"_blank\">the\u00a0change to the fields in the Netlogon.log file<\/a>. I also found that other scripts were quite inefficient when reading and collecting the Netlogon.log files over WAN connections. The <a href=\"http:\/\/www.lazywinadmin.com\/2013\/10\/powershell-report-ad-missing-subnets.html\" target=\"_blank\">Report the AD Missing Subnets from the NETLOGON.log<\/a> script by <a href=\"http:\/\/www.lazywinadmin.com\/\" target=\"_blank\">Francois-Xavier CAT<\/a> was the best available. So I used it as a base to help derive the results I was after.<!--more--><\/p>\n<p>The only variable to adjust is $LogsLines. You set\u00a0this to the last number of lines to read from each Netlogon.log file.<\/p>\n<p>There is an optional\u00a0TrustedDomain parameter you can use to pass a different domain name to the script, which will allow you to run this script against trusted domains. You must use the fully qualified domain name. Refer to the example in the script comments.<\/p>\n<p>The screen shot below shows the output from an environment with 16 Domain Controllers.<\/p>\n<ul>\n<li>The script connects to each Domain Controller, copies the Netlogon.log file back to an Output folder where the script is located, exports the NO_CLIENT_SITE messages from the last x lines, based on the\u00a0$LogsLines variable, and writes them to a text file.<\/li>\n<li>It then reads all text files, outputting it into 1 large file.<\/li>\n<li>From there is does a sort of all unique lines based on the IP Address field and exports the results to a CSV file. So whilst you may have hundreds or thousands of\u00a0NO_CLIENT_SITE messages across all your logs, sorting in order by uniqueness makes this a less overwhelming task to complete.<\/li>\n<li>You&#8217;ll notice that if the script will output errors to the screen if it&#8217;s unable to connect to a Domain Controller via the admin$ share to copy the Netlogon.log file, or is simply unable to ping the Domain Controller.<\/li>\n<\/ul>\n<p style=\"text-align: center;\"><a href=\"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-Script-Output.png\"><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter  wp-image-1189\" title=\"AD-Sites-MissingSubnets-Script-Output\" alt=\"AD-Sites-MissingSubnets-Script-Output\" src=\"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-Script-Output.png\" width=\"602\" height=\"640\" srcset=\"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-Script-Output.png 752w, https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-Script-Output-282x300.png 282w, https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-Script-Output-300x319.png 300w\" sizes=\"(max-width: 602px) 100vw, 602px\" \/><\/a><\/p>\n<p>The screen shot below shows the output of unique IP Addresses in the CSV file. As the IP Addresses are ordered, it&#8217;s easy to group them together into missing subnets. Note how depending on the country\/language, the date column may be incorrectly formatted in Excel. You can correct the cell formatting, or just ignore it.<\/p>\n<p style=\"text-align: center;\"><a href=\"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-CSV-in-Excel.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-1190\" title=\"AD-Sites-MissingSubnets-CSV-in-Excel\" alt=\"AD-Sites-MissingSubnets-CSV-in-Excel\" src=\"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-CSV-in-Excel.png\" width=\"453\" height=\"800\" srcset=\"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-CSV-in-Excel.png 453w, https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-CSV-in-Excel-170x300.png 170w, https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-CSV-in-Excel-300x530.png 300w\" sizes=\"(max-width: 453px) 100vw, 453px\" \/><\/a><\/p>\n<p>As per the following screen shot, if you open the CSV in Notepad, you&#8217;ll see the actual date format.<\/p>\n<p style=\"text-align: center;\"><a href=\"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-CSV-in-Notepad.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-1191\" title=\"AD-Sites-MissingSubnets-CSV-in-Notepad\" alt=\"AD-Sites-MissingSubnets-CSV-in-Notepad\" src=\"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-CSV-in-Notepad.png\" width=\"433\" height=\"540\" srcset=\"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-CSV-in-Notepad.png 433w, https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-CSV-in-Notepad-241x300.png 241w, https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/AD-Sites-MissingSubnets-CSV-in-Notepad-300x374.png 300w\" sizes=\"(max-width: 433px) 100vw, 433px\" \/><\/a><\/p>\n<p>Here is the <a href=\"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-content\/uploads\/2014\/01\/Find_missing_subnets_in_ActiveDirectory.ps1_.txt\" target=\"_blank\">Find_missing_subnets_in_ActiveDirectory.ps1<\/a> script:<\/p>\n<pre class=\"brush: powershell; auto-links: false; title: ; toolbar: false; notranslate\" title=\"\">\r\n&lt;#\r\n  This script will get all the missing subnets from the NETLOGON.LOG file from each\r\n  Domain Controller in the Domain. It does this by copying all the NETLOGON.LOG files\r\n  locally and then parsing them all to create a CSV output of unique IP Addresses.\r\n  The CSV file is sorted by IP Address to make it easy to group them into subnets.\r\n\r\n  Script Name: Find_missing_subnets_in_ActiveDirectory.ps1\r\n  Release 1.2\r\n  Modified by Jeremy@jhouseconsulting.com 23\/01\/2014\r\n  Written by Jeremy@jhouseconsulting.com 02\/01\/2014\r\n\r\n  Syntax examples:\r\n\r\n  - To execute the script in the current Domain:\r\n      Find_missing_subnets_in_ActiveDirectory.ps1\r\n\r\n  - To execute the script in a trusted Domain:\r\n      Find_missing_subnets_in_ActiveDirectory.ps1 -TrustedDomain mydemosthatrock.com\r\n\r\n  This script was derived from the AD-Find_missing_subnets_in_ActiveDirectory.ps1\r\n  script written by Francois-Xavier CAT.\r\n   - Report the AD Missing Subnets from the NETLOGON.log\r\n     http:\/\/www.lazywinadmin.com\/2013\/10\/powershell-report-ad-missing-subnets.html\r\n\r\n  Changes:\r\n  - Stripped down the code to remove the e-mail functionality. This is a nice to\r\n    have feature and can be added back in for a future release. I felt that it was\r\n    more important to focus on ensuring the core functionality of the script was\r\n    working correctly and efficiently.\r\n\r\n  Improvements:\r\n  - Reordered the Netlogon.log collection to make it more efficient.\r\n  - Implemented a fix to deal with the changes to the fields in the Netlogon.log\r\n    file from Windows 2012 and above:\r\n    - http:\/\/www.jhouseconsulting.com\/2013\/12\/13\/a-change-to-the-fields-in-the-netlogon-log-file-from-windows-2012-and-above-1029\r\n  - Tidied up the way it writes the CSV file.\r\n  - Changed the write-verbose and write-warning messages to write-host to vary the\r\n    message colors and improve screen output.\r\n  - Added a &quot;replay&quot; feature so that you have the ability to re-create the CSV\r\n    from collected log files.\r\n#&gt;\r\n#-------------------------------------------------------------\r\nparam(&#x5B;String]$TrustedDomain)\r\n#-------------------------------------------------------------\r\n\r\n# Set this to the last number of lines to read from each NETLOGON.log file.\r\n# This allows the report to contain the most recent and relevant errors.\r\n&#x5B;Int]$LogsLines = &quot;200&quot;\r\n\r\n# Set this to $True to remove txt and log files from the output folder.\r\n$Cleanup = $True\r\n\r\n# Set this to $True if you have not removed the log files and want to replay\r\n# them to create a CSV.\r\n$ReplayLogFiles = $False\r\n\r\n#-------------------------------------------------------------\r\n\r\n# PATH Information\r\n$ScriptPath = (Split-Path -Path ((Get-Variable -Name MyInvocation).Value).MyCommand.Path)\r\n$ScriptPathOutput = $ScriptPath + &quot;\\Output&quot;\r\n\r\n# Date and Time Information\r\n$DateFormat = Get-Date -Format &quot;yyyyMMdd_HHmmss&quot;\r\n\r\n$OutputFile = &quot;$scriptPathOutput\\$DateFormat-AD-Sites-MissingSubnets.csv&quot;\r\n\r\n$CombineAndProcess = $False\r\n\r\nIF ($ReplayLogFiles -eq $False)\r\n{\r\n  IF (-not(Test-Path -Path $ScriptPathOutput))\r\n  {\r\n    Write-Host -ForegroundColor green &quot;Creating the Output Folder: $ScriptPathOutput&quot;\r\n    New-Item -Path $ScriptPathOutput -ItemType Directory | Out-Null\r\n  }\r\n\r\n  if (&#x5B;String]::IsNullOrEmpty($TrustedDomain)) {\r\n    # Get the Current Domain Information\r\n    $domain = &#x5B;System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain()\r\n  } else {\r\n    $context = new-object System.DirectoryServices.ActiveDirectory.DirectoryContext(&quot;domain&quot;,$TrustedDomain)\r\n    Try {\r\n      $domain = &#x5B;System.DirectoryServices.ActiveDirectory.Domain]::GetDomain($context)\r\n    }\r\n    Catch &#x5B;exception] {\r\n      write-host -ForegroundColor red $_.Exception.Message\r\n      Exit\r\n    }\r\n  }\r\n\r\n  Write-Host -ForegroundColor green &quot;Domain: $domain&quot;\r\n\r\n  # Get the names of all the Domain Contollers in $domain\r\n  Write-Host -ForegroundColor green &quot;Getting all Domain Controllers from $domain ...&quot;\r\n  $DomainControllers = $domain | ForEach-Object -Process { $_.DomainControllers } | Select-Object -Property Name\r\n\r\n  # Gathering the NETLOGON.LOG for each Domain Controller\r\n  Write-Host -ForegroundColor green &quot;Processing each Domain controller...&quot;\r\n  FOREACH ($dc in $DomainControllers)\r\n  {\r\n    $DCName = $($dc.Name)\r\n\r\n    # Get the Current Domain Controller in the Loop\r\n    Write-Host -ForegroundColor green &quot;Gathering the log from $DCName...&quot;\r\n\r\n    IF (Test-Connection -Cn $DCName -BufferSize 16 -Count 1 -ea 0 -quiet) {\r\n\r\n      # NETLOGON.LOG path for the current Domain Controller\r\n      $path = &quot;\\\\$DCName\\admin`$\\debug\\netlogon.log&quot;\r\n\r\n      # Testing the $path\r\n      IF ((Test-Path -Path $path) -and ((Get-Item -Path $path).Length -ne $null))\r\n      {\r\n        # Copy the NETLOGON.log locally for the current DC\r\n        Write-Host -ForegroundColor green &quot;- Copying the $path file...&quot;\r\n        $TotalTime = measure-command {Copy-Item -Path $path -Destination $ScriptPathOutput\\$($dc.Name)-$DateFormat-netlogon.log}\r\n        $TotalSeconds = $TotalTime.TotalSeconds\r\n        Write-Host -ForegroundColor green &quot;- Copy completed in $TotalSeconds seconds.&quot;\r\n\r\n        IF ((Get-Content -Path $path | Measure-Object -Line).lines -gt 0)\r\n        {\r\n          # Export the $LogsLines last lines of the NETLOGON.log and send it to a file\r\n          ((Get-Content -Path $ScriptPathOutput\\$DCName-$DateFormat-netlogon.log -ErrorAction Continue)&#x5B;-$LogsLines .. -1]) |\r\n            Foreach-Object {$_ -replace &quot;\\&#x5B;\\d{1,5}\\] &quot;, &quot;&quot;} |\r\n            Out-File -FilePath &quot;$ScriptPathOutput\\$DCName.txt&quot; -ErrorAction 'Continue' -ErrorVariable ErrorOutFileNetLogon\r\n          Write-Host -ForegroundColor green &quot;- Exported the last $LogsLines lines to $ScriptPathOutput\\$DCName.txt.&quot;\r\n        }#IF\r\n        ELSE {Write-Host -ForegroundColor green &quot;- File Empty.&quot;}\r\n\r\n      } ELSE {Write-Host -ForegroundColor red &quot;- $DCName is not reachable via the $path path.&quot;}\r\n\r\n    } ELSE {Write-Host -ForegroundColor red &quot;- $DCName is not reachable or offline.&quot;}\r\n\r\n    $CombineAndProcess = $True\r\n\r\n  }#FOREACH\r\n\r\n} ELSE {\r\n\r\n  Write-Host -ForegroundColor green &quot;Replaying the log files...&quot;\r\n  IF (Test-Path -Path $ScriptPathOutput)\r\n  {\r\n    IF ((Get-ChildItem $scriptpathoutput\\*.log | Measure-Object).Count -gt 0)\r\n    {\r\n      $LogFiles = Get-ChildItem $scriptpathoutput\\*.log\r\n\r\n      ForEach ($LogFile in $LogFiles)\r\n      {\r\n        $DCName = $LogFile.Name -Replace(&quot;-\\d{7,8}_\\d{6}-netlogon.log&quot;)\r\n        Write-Host -ForegroundColor green &quot;Processing the log from $DCName...&quot;\r\n        IF ((Get-Content -Path &quot;$ScriptPathOutput\\$($LogFile.Name)&quot; | Measure-Object -Line).lines -gt 0)\r\n        {\r\n          # Export the $LogsLines last lines of the NETLOGON.log and send it to a file\r\n          ((Get-Content -Path &quot;$ScriptPathOutput\\$($LogFile.Name)&quot; -ErrorAction Continue)&#x5B;-$LogsLines .. -1]) |\r\n                    Foreach-Object {$_ -replace &quot;\\&#x5B;\\d{1,5}\\] &quot;, &quot;&quot;} |\r\n                    Out-File -FilePath &quot;$ScriptPathOutput\\$DCName.txt&quot; -ErrorAction 'Continue' -ErrorVariable ErrorOutFileNetLogon\r\n          Write-Host -ForegroundColor green &quot;- Exported the last $LogsLines lines to $ScriptPathOutput\\$DCName.txt.&quot;\r\n        } ELSE {Write-Host -ForegroundColor green &quot;- File Empty.&quot;}\r\n        $CombineAndProcess = $True\r\n      }#ForEach\r\n    } ELSE {Write-Host -ForegroundColor red &quot;There are no log files to process.&quot;}\r\n  } ELSE {Write-Host -ForegroundColor red &quot;The $ScriptpathOutput folder is missing.&quot;}\r\n}#IF\r\n\r\nIF ($CombineAndProcess)\r\n{\r\n\r\n  # Combine all the TXT file in one\r\n  $FilesToCombine = Get-Content -Path &quot;$ScriptPathOutput\\*.txt&quot; -Exclude &quot;*All_Export.txt&quot; -ErrorAction SilentlyContinue |\r\n    Foreach-Object {$_ -replace &quot;\\&#x5B;\\d{1,5}\\] &quot;, &quot;&quot;}\r\n\r\n  if ($FilesToCombine)\r\n  {\r\n    $FilesToCombine | Out-File -FilePath $ScriptPathOutput\\$dateformat-All_Export.txt\r\n\r\n    # Convert the TXT file to a CSV format\r\n    Write-Host -ForegroundColor green &quot;Importing exported data to a CSV format...&quot;\r\n    $importString = Import-Csv -Path $scriptpathOutput\\$dateformat-All_Export.txt -Delimiter ' ' -Header Date,Time,Domain,Error,Name,IPAddress\r\n\r\n    # Get Only the entries for the Missing Subnets\r\n    $MissingSubnets = $importString | Where-Object {$_.Error -like &quot;*NO_CLIENT_SITE*&quot;}\r\n    Write-Host -ForegroundColor green &quot;Total of NO_CLIENT_SITE errors found within the last $LogsLines lines across all log files: $($MissingSubnets.count)&quot;\r\n    # Get the other errors from the log\r\n    $OtherErrors = Get-Content $scriptpathOutput\\$dateformat-All_Export.txt | Where-Object {$_ -notlike &quot;*NO_CLIENT_SITE*&quot;} | Sort-Object -Unique\r\n    Write-Host -ForegroundColor green &quot;Total of other Error(s) found within the last $LogsLines lines across all log files: $($OtherErrors.count)&quot;\r\n\r\n    # Export to a CSV File\r\n    $UniqueIPAddresses = $importString | Select-Object -Property Date, Name, IPAddress, Domain, Error |\r\n    Sort-Object -Property IPAddress -Unique\r\n    $UniqueIPAddresses | Export-Csv -notype -path &quot;$OutputFile&quot;\r\n    # Remove the quotes\r\n    (get-content &quot;$OutputFile&quot;) |% {$_ -replace '&quot;',&quot;&quot;} | out-file &quot;$OutputFile&quot; -Fo -En ascii\r\n    Write-Host -ForegroundColor green &quot;$($UniqueIPAddresses.count) unique IP Addresses exported to $OutputFile.&quot;\r\n\r\n  }#IF File to Combine\r\n  ELSE {Write-Host -ForegroundColor red &quot;No .txt files to process.&quot;}\r\n\r\n  IF ($Cleanup)\r\n  {\r\n    Write-Host -ForegroundColor green &quot;Removing the .txt and .log files...&quot;\r\n    Remove-item -Path $ScriptpathOutput\\*.txt -force\r\n    Remove-Item -Path $ScriptPathOutput\\*.log -force\r\n  }\r\n\r\n}\r\n\r\nWrite-Host -ForegroundColor green &quot;Script Completed.&quot;\r\n<\/pre>\n<p>Enjoy!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This PowerShell script will collect all Netlogon.log files from the Domain Controllers, export the last x lines and combine it into one file of unique IP Addresses\u00a0in CSV format. This easily and simply allows you to then identify any missing subnets that need to be added and associated to an Active Directory Site. Yes, there &#8230; <a title=\"Script to Find Missing Subnets in Active Directory\" class=\"read-more\" href=\"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/2014\/01\/21\/script-to-find-missing-subnets-in-active-directory-1127\" aria-label=\"Read more about Script to Find Missing Subnets in Active Directory\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[202,5],"tags":[428,284,314,208,311,277,310,312,313],"class_list":["post-1127","post","type-post","status-publish","format-standard","hentry","category-active-directory","category-scripting","tag-active-directory","tag-ad","tag-dc","tag-domain-controller","tag-missing-subnets","tag-netlogon-log","tag-no_client_site","tag-sites-and-services","tag-subnets"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-json\/wp\/v2\/posts\/1127","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-json\/wp\/v2\/comments?post=1127"}],"version-history":[{"count":15,"href":"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-json\/wp\/v2\/posts\/1127\/revisions"}],"predecessor-version":[{"id":1218,"href":"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-json\/wp\/v2\/posts\/1127\/revisions\/1218"}],"wp:attachment":[{"href":"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-json\/wp\/v2\/media?parent=1127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-json\/wp\/v2\/categories?post=1127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jhouseconsulting.com\/jhouseconsulting\/wp-json\/wp\/v2\/tags?post=1127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}