PowerShell Active Directory import script failing with PS 3.0 or above












0















I don't know much about PowerShell but have inherited a script from someone who is no longer available for assistance. This script imports AD Group Info and memberships related to Users and Computers. It works fine when run on a machine with PS 2.0 but it crashes if executed on PS 3.0 or newer.



I have not been able to figure out what needs to be modified but it seems the errors start occurring in the "Computer" membership import step and there are hundreds of errors that all say:



Command failed while processing computers: , Exception of type 'System.OutOfMemoryException' was thrown



Then at some point it looks like the script just stops and it never even gets to the 3rd step / function.



Any advice?



[Reflection.Assembly]::LoadWithPartialName("System.DirectoryServices") | Out-Null

$DBServer = "DBSERVER"
$DBName = "DBNAME"
$TableUsers = "[$DBName].[dbo].[AD_GroupToClient]"
$TableComps = "[$DBName].[dbo].[AD_GroupToDevice]"
$TableGroups = "[$DBName].[dbo].[AD_Group_Info]"
$sqldateformat = "yyyy/MM/dd HH:mm:ss:fff"

[system.Data.SqlClient.SqlConnection]$global:SqlConnection = $null

function Get-ScriptPath { $Invocation = (Get-Variable MyInvocation -Scope 1).Value; Split-Path $Invocation.MyCommand.Path }
$ScriptPath = Get-ScriptPath
$Logfile = "$ScriptPathOutLog.log"

function Write-Logfile {
param($logtext)
[string](Get-Date -format $sqldateformat) + "`t$logtext" | Out-File $Logfile -Encoding ascii -Append
}

function Open-Database {
$global:SqlConnection = New-Object system.Data.SqlClient.SqlConnection
try {
$global:SqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True"
$global:SqlConnection.Open() | Out-Null
Write-Logfile "OK`tDatabase opened"
} catch {
Write-Host "Error Opening SQL Database`t$($_.Exception.Message)"
Write-Logfile "Error`tDatabase open failed, $($_.exception.message)"
exit
}
}

function Close-Database {
$global:SqlConnection.Close()
Write-Logfile "OK`tDatabase closed"
}

function Esc-Quote {
param($str)
if ($str) { $str.Replace("'","''") }
}

function Run-DBCommand {
param($SqlCommands, [switch]$getnumrows)
if ($SqlCommands.Count -ge 1) {
$SqlCommandText = [string]::Join(";", $SqlCommands)
try {
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$returnvalue = $SqlCmd.ExecuteNonQuery()
if ($getnumrows) { return $returnvalue }
} catch {
Write-Logfile "Error`tSQL Command failed, $($_.exception.message)"
}
}
}

function Run-GroupMemberExport {
param($exportmode)
switch ($exportmode) {
"users" {
$dom = [ADSI]"LDAP://OU=Clients123,DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=user)(objectCategory=person)(samaccountname=*))"
$table = $TableUsers
$namecolumn = "AD_Group_Member_Name"
$attribs = @("samaccountname")
}
"computers" {
$dom = [ADSI]"LDAP://DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=computer)(samaccountname=*))"
$table = $TableComps
$namecolumn = "AD_Group_Member_Device"
$attribs = @("samaccountname", "whencreated")
}
}
$starttime = (Get-Date).ToUniversalTime().ToString($sqldateformat)
$srch = New-Object DirectoryServices.DirectorySearcher($dom, $query, $attribs)
$srch.PageSize = 1000
$srch.Sort = New-Object DirectoryServices.SortOption("sAMAccountName", [DirectoryServices.SortDirection]::Ascending)
$results = $srch.FindAll()

$count = 0
$numaccounts = $results.Count
foreach ($res in $results) {
try {
$objAccount = $res.GetDirectoryEntry()
$samaccountname = $objAccount.properties["samaccountname"][0]
$whencreated = ""
if ($exportmode -eq "computers") { $whencreated = Get-Date ([datetime]$objAccount.properties["whencreated"][0]) -Format $sqldateformat }
$count++
Write-Progress "Querying accounts" $samaccountname -PercentComplete ($count * 100.0 / $numaccounts)
$objAccount.psbase.RefreshCache("tokenGroups")
$SIDs = $objAccount.psbase.Properties.Item("tokenGroups")
$groups = @()
ForEach ($Value In $SIDs) {
$SID = New-Object System.Security.Principal.SecurityIdentifier $Value, 0
try {
$Group = $SID.Translate([System.Security.Principal.NTAccount]).Value
} catch {
$Group = $SID.Translate([System.Security.Principal.SecurityIdentifier]).Value
}
if ($groups -notcontains $Group -and $Group.Split("")[1] -ne $samaccountname) { $groups += $Group }
}
Run-DBCommand @("DELETE FROM $table WHERE [$namecolumn] = '$(Esc-Quote $samaccountname)'")
$sqlcommands = @()
$currenttime = (Get-Date).ToUniversalTime().ToString($sqldateformat)
if ($groups) {
$groups | sort | foreach {
if ($exportmode -eq "users") {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $_)', '$currenttime')"
} else {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update], [Record_Created]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $_)', '$currenttime', '$whencreated')"
}
if ($sqlcommands.count -ge 50) { Run-DBCommand $sqlcommands; $sqlcommands = @() }
}
} else {
if ($exportmode -eq "users") {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update]) VALUES ('$(Esc-Quote $samaccountname)', 'ERROR: Unable to retrieve groups', '$currenttime')"
} else {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update], [Record_Created]) VALUES ('$(Esc-Quote $samaccountname)', 'ERROR: Unable to retrieve groups', '$currenttime', '$whencreated')"
}
}
Run-DBCommand $sqlcommands
} catch {
Write-Logfile "Error`tCommand failed while processing $exportmode`: $($objAccount.name), $($_.exception.message)"
}
}
Write-Progress " " " " -Completed
if ($count -eq $numaccounts) {
$numdeleted = Run-DBCommand @("DELETE FROM $table WHERE [Last_Update] < '$starttime' OR [Last_Update] IS NULL") -getnumrows
Write-Logfile "OK`tUpdates for $exportmode completed, $numdeleted old records deleted."
}
}

function Run-GroupDescriptionExport {
$dom = [ADSI]"LDAP://DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=group)(samaccountname=*))"
$table = $TableGroups
$attribs = @("samaccountname", "displayname", "description", "whencreated", "managedby", "grouptype","distinguishedname","whenchanged")
$srch = New-Object DirectoryServices.DirectorySearcher($dom, $query, $attribs)
$srch.PageSize = 1000
$srch.Sort = New-Object DirectoryServices.SortOption("sAMAccountName", [DirectoryServices.SortDirection]::Ascending)
$results = $srch.FindAll()
$count = 0
$numgroups = $results.Count
$sqlcommands = @()
$starttime = [datetime]::Now.ToUniversalTime().ToString($sqldateformat)
foreach ($res in $results) {
$count++
$samaccountname = $res.properties["samaccountname"][0]
Write-Progress "Querying accounts, $count/$numgroups" $samaccountname -PercentComplete ($count * 100.0 / $numgroups)
$displayName = ""; if ($res.properties.contains("displayname")) { $displayName = $res.properties["displayname"][0] }
$description = ""; if ($res.properties.contains("description")) { $description = $res.properties["description"][0] }
$managedby = ""; if ($res.properties.contains("managedby")) { $managedby = $res.properties["managedby"][0] }
$grouptype = ""; if ($res.properties.contains("grouptype")) { $grouptype = $res.properties["grouptype"][0] }
$distinguishedname = ""; if ($res.properties.contains("distinguishedname")) { $distinguishedname = $res.properties["distinguishedname"][0] }
$whencreated = ""; if ($res.properties.contains("whencreated")) { $whencreated = ([datetime]$res.properties["whencreated"][0]).ToString($sqldateformat) }
$whenchanged = ""; if ($res.properties.contains("whenchanged")) { $whenchanged = ([datetime]$res.properties["whenchanged"][0]).ToString($sqldateformat) }
$lastupdated = [datetime]::Now.ToUniversalTime().ToString($sqldateformat)
$sqlcommand = "DELETE FROM $table WHERE [AD_Group_Name] = '$(Esc-Quote $samaccountname)'; "
$sqlcommand += "INSERT INTO $table ([AD_Group_Name], [AD_Group_DisplayName], [AD_Group_Description], [Last_Update], [Managed_By],[Distinguished_Name],[Group_Category],[Created_On], AD_Last_Modified]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $displayName)', '$(Esc-Quote $description)', '$lastupdated', '$(Esc-Quote $managedby)', '$(Esc-Quote $distinguishedname)', '$grouptype', '$whencreated','$whenchanged')"

$sqlcommands += $sqlcommand


if ($sqlcommands.count -ge 100) { Run-DBCommand $sqlcommands; $sqlcommands = @()
}
}
Run-DBCommand $sqlcommands
if ($numgroups -eq $count) {
Run-DBCommand @("DELETE FROM $table WHERE [Last_Update] <= '$starttime'")
}
Write-Progress " " " " -Completed
}

Open-Database
Run-GroupMemberExport "users"
Run-GroupMemberExport "computers"
Run-GroupDescriptionExport
Close-Database









share|improve this question




















  • 2





    That's a pretty big leap - assuming 3.0 is at fault. If you run the script, on the same machine having trouble, but with -version 2 parameters, do you still have an OutOfMemoryException occur? If so, it's not the versioning.

    – gravitymixes
    Nov 26 '18 at 15:55











  • I would start with commenting out some of the Try/Catch statements to check with line actually fails.

    – iRon
    Nov 26 '18 at 16:53






  • 1





    Thanks @gravitymixes. I tried -version 2 in the command statement on the same server where the script had issues before and so far it completed without any errors. I am going to run it again few more times to double check.

    – JetRocket11
    Nov 26 '18 at 20:40











  • Subsequent tests failed so -version 2 is not a solution on it's own. What I found though is the script executes 3 functions - only the computer one throws errors - but it works fine if the 'users' function is disabled in the run. Going to try finally { $objAccount.Dispose() suggestion

    – JetRocket11
    Nov 27 '18 at 16:22
















0















I don't know much about PowerShell but have inherited a script from someone who is no longer available for assistance. This script imports AD Group Info and memberships related to Users and Computers. It works fine when run on a machine with PS 2.0 but it crashes if executed on PS 3.0 or newer.



I have not been able to figure out what needs to be modified but it seems the errors start occurring in the "Computer" membership import step and there are hundreds of errors that all say:



Command failed while processing computers: , Exception of type 'System.OutOfMemoryException' was thrown



Then at some point it looks like the script just stops and it never even gets to the 3rd step / function.



Any advice?



[Reflection.Assembly]::LoadWithPartialName("System.DirectoryServices") | Out-Null

$DBServer = "DBSERVER"
$DBName = "DBNAME"
$TableUsers = "[$DBName].[dbo].[AD_GroupToClient]"
$TableComps = "[$DBName].[dbo].[AD_GroupToDevice]"
$TableGroups = "[$DBName].[dbo].[AD_Group_Info]"
$sqldateformat = "yyyy/MM/dd HH:mm:ss:fff"

[system.Data.SqlClient.SqlConnection]$global:SqlConnection = $null

function Get-ScriptPath { $Invocation = (Get-Variable MyInvocation -Scope 1).Value; Split-Path $Invocation.MyCommand.Path }
$ScriptPath = Get-ScriptPath
$Logfile = "$ScriptPathOutLog.log"

function Write-Logfile {
param($logtext)
[string](Get-Date -format $sqldateformat) + "`t$logtext" | Out-File $Logfile -Encoding ascii -Append
}

function Open-Database {
$global:SqlConnection = New-Object system.Data.SqlClient.SqlConnection
try {
$global:SqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True"
$global:SqlConnection.Open() | Out-Null
Write-Logfile "OK`tDatabase opened"
} catch {
Write-Host "Error Opening SQL Database`t$($_.Exception.Message)"
Write-Logfile "Error`tDatabase open failed, $($_.exception.message)"
exit
}
}

function Close-Database {
$global:SqlConnection.Close()
Write-Logfile "OK`tDatabase closed"
}

function Esc-Quote {
param($str)
if ($str) { $str.Replace("'","''") }
}

function Run-DBCommand {
param($SqlCommands, [switch]$getnumrows)
if ($SqlCommands.Count -ge 1) {
$SqlCommandText = [string]::Join(";", $SqlCommands)
try {
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$returnvalue = $SqlCmd.ExecuteNonQuery()
if ($getnumrows) { return $returnvalue }
} catch {
Write-Logfile "Error`tSQL Command failed, $($_.exception.message)"
}
}
}

function Run-GroupMemberExport {
param($exportmode)
switch ($exportmode) {
"users" {
$dom = [ADSI]"LDAP://OU=Clients123,DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=user)(objectCategory=person)(samaccountname=*))"
$table = $TableUsers
$namecolumn = "AD_Group_Member_Name"
$attribs = @("samaccountname")
}
"computers" {
$dom = [ADSI]"LDAP://DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=computer)(samaccountname=*))"
$table = $TableComps
$namecolumn = "AD_Group_Member_Device"
$attribs = @("samaccountname", "whencreated")
}
}
$starttime = (Get-Date).ToUniversalTime().ToString($sqldateformat)
$srch = New-Object DirectoryServices.DirectorySearcher($dom, $query, $attribs)
$srch.PageSize = 1000
$srch.Sort = New-Object DirectoryServices.SortOption("sAMAccountName", [DirectoryServices.SortDirection]::Ascending)
$results = $srch.FindAll()

$count = 0
$numaccounts = $results.Count
foreach ($res in $results) {
try {
$objAccount = $res.GetDirectoryEntry()
$samaccountname = $objAccount.properties["samaccountname"][0]
$whencreated = ""
if ($exportmode -eq "computers") { $whencreated = Get-Date ([datetime]$objAccount.properties["whencreated"][0]) -Format $sqldateformat }
$count++
Write-Progress "Querying accounts" $samaccountname -PercentComplete ($count * 100.0 / $numaccounts)
$objAccount.psbase.RefreshCache("tokenGroups")
$SIDs = $objAccount.psbase.Properties.Item("tokenGroups")
$groups = @()
ForEach ($Value In $SIDs) {
$SID = New-Object System.Security.Principal.SecurityIdentifier $Value, 0
try {
$Group = $SID.Translate([System.Security.Principal.NTAccount]).Value
} catch {
$Group = $SID.Translate([System.Security.Principal.SecurityIdentifier]).Value
}
if ($groups -notcontains $Group -and $Group.Split("")[1] -ne $samaccountname) { $groups += $Group }
}
Run-DBCommand @("DELETE FROM $table WHERE [$namecolumn] = '$(Esc-Quote $samaccountname)'")
$sqlcommands = @()
$currenttime = (Get-Date).ToUniversalTime().ToString($sqldateformat)
if ($groups) {
$groups | sort | foreach {
if ($exportmode -eq "users") {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $_)', '$currenttime')"
} else {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update], [Record_Created]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $_)', '$currenttime', '$whencreated')"
}
if ($sqlcommands.count -ge 50) { Run-DBCommand $sqlcommands; $sqlcommands = @() }
}
} else {
if ($exportmode -eq "users") {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update]) VALUES ('$(Esc-Quote $samaccountname)', 'ERROR: Unable to retrieve groups', '$currenttime')"
} else {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update], [Record_Created]) VALUES ('$(Esc-Quote $samaccountname)', 'ERROR: Unable to retrieve groups', '$currenttime', '$whencreated')"
}
}
Run-DBCommand $sqlcommands
} catch {
Write-Logfile "Error`tCommand failed while processing $exportmode`: $($objAccount.name), $($_.exception.message)"
}
}
Write-Progress " " " " -Completed
if ($count -eq $numaccounts) {
$numdeleted = Run-DBCommand @("DELETE FROM $table WHERE [Last_Update] < '$starttime' OR [Last_Update] IS NULL") -getnumrows
Write-Logfile "OK`tUpdates for $exportmode completed, $numdeleted old records deleted."
}
}

function Run-GroupDescriptionExport {
$dom = [ADSI]"LDAP://DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=group)(samaccountname=*))"
$table = $TableGroups
$attribs = @("samaccountname", "displayname", "description", "whencreated", "managedby", "grouptype","distinguishedname","whenchanged")
$srch = New-Object DirectoryServices.DirectorySearcher($dom, $query, $attribs)
$srch.PageSize = 1000
$srch.Sort = New-Object DirectoryServices.SortOption("sAMAccountName", [DirectoryServices.SortDirection]::Ascending)
$results = $srch.FindAll()
$count = 0
$numgroups = $results.Count
$sqlcommands = @()
$starttime = [datetime]::Now.ToUniversalTime().ToString($sqldateformat)
foreach ($res in $results) {
$count++
$samaccountname = $res.properties["samaccountname"][0]
Write-Progress "Querying accounts, $count/$numgroups" $samaccountname -PercentComplete ($count * 100.0 / $numgroups)
$displayName = ""; if ($res.properties.contains("displayname")) { $displayName = $res.properties["displayname"][0] }
$description = ""; if ($res.properties.contains("description")) { $description = $res.properties["description"][0] }
$managedby = ""; if ($res.properties.contains("managedby")) { $managedby = $res.properties["managedby"][0] }
$grouptype = ""; if ($res.properties.contains("grouptype")) { $grouptype = $res.properties["grouptype"][0] }
$distinguishedname = ""; if ($res.properties.contains("distinguishedname")) { $distinguishedname = $res.properties["distinguishedname"][0] }
$whencreated = ""; if ($res.properties.contains("whencreated")) { $whencreated = ([datetime]$res.properties["whencreated"][0]).ToString($sqldateformat) }
$whenchanged = ""; if ($res.properties.contains("whenchanged")) { $whenchanged = ([datetime]$res.properties["whenchanged"][0]).ToString($sqldateformat) }
$lastupdated = [datetime]::Now.ToUniversalTime().ToString($sqldateformat)
$sqlcommand = "DELETE FROM $table WHERE [AD_Group_Name] = '$(Esc-Quote $samaccountname)'; "
$sqlcommand += "INSERT INTO $table ([AD_Group_Name], [AD_Group_DisplayName], [AD_Group_Description], [Last_Update], [Managed_By],[Distinguished_Name],[Group_Category],[Created_On], AD_Last_Modified]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $displayName)', '$(Esc-Quote $description)', '$lastupdated', '$(Esc-Quote $managedby)', '$(Esc-Quote $distinguishedname)', '$grouptype', '$whencreated','$whenchanged')"

$sqlcommands += $sqlcommand


if ($sqlcommands.count -ge 100) { Run-DBCommand $sqlcommands; $sqlcommands = @()
}
}
Run-DBCommand $sqlcommands
if ($numgroups -eq $count) {
Run-DBCommand @("DELETE FROM $table WHERE [Last_Update] <= '$starttime'")
}
Write-Progress " " " " -Completed
}

Open-Database
Run-GroupMemberExport "users"
Run-GroupMemberExport "computers"
Run-GroupDescriptionExport
Close-Database









share|improve this question




















  • 2





    That's a pretty big leap - assuming 3.0 is at fault. If you run the script, on the same machine having trouble, but with -version 2 parameters, do you still have an OutOfMemoryException occur? If so, it's not the versioning.

    – gravitymixes
    Nov 26 '18 at 15:55











  • I would start with commenting out some of the Try/Catch statements to check with line actually fails.

    – iRon
    Nov 26 '18 at 16:53






  • 1





    Thanks @gravitymixes. I tried -version 2 in the command statement on the same server where the script had issues before and so far it completed without any errors. I am going to run it again few more times to double check.

    – JetRocket11
    Nov 26 '18 at 20:40











  • Subsequent tests failed so -version 2 is not a solution on it's own. What I found though is the script executes 3 functions - only the computer one throws errors - but it works fine if the 'users' function is disabled in the run. Going to try finally { $objAccount.Dispose() suggestion

    – JetRocket11
    Nov 27 '18 at 16:22














0












0








0








I don't know much about PowerShell but have inherited a script from someone who is no longer available for assistance. This script imports AD Group Info and memberships related to Users and Computers. It works fine when run on a machine with PS 2.0 but it crashes if executed on PS 3.0 or newer.



I have not been able to figure out what needs to be modified but it seems the errors start occurring in the "Computer" membership import step and there are hundreds of errors that all say:



Command failed while processing computers: , Exception of type 'System.OutOfMemoryException' was thrown



Then at some point it looks like the script just stops and it never even gets to the 3rd step / function.



Any advice?



[Reflection.Assembly]::LoadWithPartialName("System.DirectoryServices") | Out-Null

$DBServer = "DBSERVER"
$DBName = "DBNAME"
$TableUsers = "[$DBName].[dbo].[AD_GroupToClient]"
$TableComps = "[$DBName].[dbo].[AD_GroupToDevice]"
$TableGroups = "[$DBName].[dbo].[AD_Group_Info]"
$sqldateformat = "yyyy/MM/dd HH:mm:ss:fff"

[system.Data.SqlClient.SqlConnection]$global:SqlConnection = $null

function Get-ScriptPath { $Invocation = (Get-Variable MyInvocation -Scope 1).Value; Split-Path $Invocation.MyCommand.Path }
$ScriptPath = Get-ScriptPath
$Logfile = "$ScriptPathOutLog.log"

function Write-Logfile {
param($logtext)
[string](Get-Date -format $sqldateformat) + "`t$logtext" | Out-File $Logfile -Encoding ascii -Append
}

function Open-Database {
$global:SqlConnection = New-Object system.Data.SqlClient.SqlConnection
try {
$global:SqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True"
$global:SqlConnection.Open() | Out-Null
Write-Logfile "OK`tDatabase opened"
} catch {
Write-Host "Error Opening SQL Database`t$($_.Exception.Message)"
Write-Logfile "Error`tDatabase open failed, $($_.exception.message)"
exit
}
}

function Close-Database {
$global:SqlConnection.Close()
Write-Logfile "OK`tDatabase closed"
}

function Esc-Quote {
param($str)
if ($str) { $str.Replace("'","''") }
}

function Run-DBCommand {
param($SqlCommands, [switch]$getnumrows)
if ($SqlCommands.Count -ge 1) {
$SqlCommandText = [string]::Join(";", $SqlCommands)
try {
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$returnvalue = $SqlCmd.ExecuteNonQuery()
if ($getnumrows) { return $returnvalue }
} catch {
Write-Logfile "Error`tSQL Command failed, $($_.exception.message)"
}
}
}

function Run-GroupMemberExport {
param($exportmode)
switch ($exportmode) {
"users" {
$dom = [ADSI]"LDAP://OU=Clients123,DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=user)(objectCategory=person)(samaccountname=*))"
$table = $TableUsers
$namecolumn = "AD_Group_Member_Name"
$attribs = @("samaccountname")
}
"computers" {
$dom = [ADSI]"LDAP://DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=computer)(samaccountname=*))"
$table = $TableComps
$namecolumn = "AD_Group_Member_Device"
$attribs = @("samaccountname", "whencreated")
}
}
$starttime = (Get-Date).ToUniversalTime().ToString($sqldateformat)
$srch = New-Object DirectoryServices.DirectorySearcher($dom, $query, $attribs)
$srch.PageSize = 1000
$srch.Sort = New-Object DirectoryServices.SortOption("sAMAccountName", [DirectoryServices.SortDirection]::Ascending)
$results = $srch.FindAll()

$count = 0
$numaccounts = $results.Count
foreach ($res in $results) {
try {
$objAccount = $res.GetDirectoryEntry()
$samaccountname = $objAccount.properties["samaccountname"][0]
$whencreated = ""
if ($exportmode -eq "computers") { $whencreated = Get-Date ([datetime]$objAccount.properties["whencreated"][0]) -Format $sqldateformat }
$count++
Write-Progress "Querying accounts" $samaccountname -PercentComplete ($count * 100.0 / $numaccounts)
$objAccount.psbase.RefreshCache("tokenGroups")
$SIDs = $objAccount.psbase.Properties.Item("tokenGroups")
$groups = @()
ForEach ($Value In $SIDs) {
$SID = New-Object System.Security.Principal.SecurityIdentifier $Value, 0
try {
$Group = $SID.Translate([System.Security.Principal.NTAccount]).Value
} catch {
$Group = $SID.Translate([System.Security.Principal.SecurityIdentifier]).Value
}
if ($groups -notcontains $Group -and $Group.Split("")[1] -ne $samaccountname) { $groups += $Group }
}
Run-DBCommand @("DELETE FROM $table WHERE [$namecolumn] = '$(Esc-Quote $samaccountname)'")
$sqlcommands = @()
$currenttime = (Get-Date).ToUniversalTime().ToString($sqldateformat)
if ($groups) {
$groups | sort | foreach {
if ($exportmode -eq "users") {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $_)', '$currenttime')"
} else {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update], [Record_Created]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $_)', '$currenttime', '$whencreated')"
}
if ($sqlcommands.count -ge 50) { Run-DBCommand $sqlcommands; $sqlcommands = @() }
}
} else {
if ($exportmode -eq "users") {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update]) VALUES ('$(Esc-Quote $samaccountname)', 'ERROR: Unable to retrieve groups', '$currenttime')"
} else {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update], [Record_Created]) VALUES ('$(Esc-Quote $samaccountname)', 'ERROR: Unable to retrieve groups', '$currenttime', '$whencreated')"
}
}
Run-DBCommand $sqlcommands
} catch {
Write-Logfile "Error`tCommand failed while processing $exportmode`: $($objAccount.name), $($_.exception.message)"
}
}
Write-Progress " " " " -Completed
if ($count -eq $numaccounts) {
$numdeleted = Run-DBCommand @("DELETE FROM $table WHERE [Last_Update] < '$starttime' OR [Last_Update] IS NULL") -getnumrows
Write-Logfile "OK`tUpdates for $exportmode completed, $numdeleted old records deleted."
}
}

function Run-GroupDescriptionExport {
$dom = [ADSI]"LDAP://DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=group)(samaccountname=*))"
$table = $TableGroups
$attribs = @("samaccountname", "displayname", "description", "whencreated", "managedby", "grouptype","distinguishedname","whenchanged")
$srch = New-Object DirectoryServices.DirectorySearcher($dom, $query, $attribs)
$srch.PageSize = 1000
$srch.Sort = New-Object DirectoryServices.SortOption("sAMAccountName", [DirectoryServices.SortDirection]::Ascending)
$results = $srch.FindAll()
$count = 0
$numgroups = $results.Count
$sqlcommands = @()
$starttime = [datetime]::Now.ToUniversalTime().ToString($sqldateformat)
foreach ($res in $results) {
$count++
$samaccountname = $res.properties["samaccountname"][0]
Write-Progress "Querying accounts, $count/$numgroups" $samaccountname -PercentComplete ($count * 100.0 / $numgroups)
$displayName = ""; if ($res.properties.contains("displayname")) { $displayName = $res.properties["displayname"][0] }
$description = ""; if ($res.properties.contains("description")) { $description = $res.properties["description"][0] }
$managedby = ""; if ($res.properties.contains("managedby")) { $managedby = $res.properties["managedby"][0] }
$grouptype = ""; if ($res.properties.contains("grouptype")) { $grouptype = $res.properties["grouptype"][0] }
$distinguishedname = ""; if ($res.properties.contains("distinguishedname")) { $distinguishedname = $res.properties["distinguishedname"][0] }
$whencreated = ""; if ($res.properties.contains("whencreated")) { $whencreated = ([datetime]$res.properties["whencreated"][0]).ToString($sqldateformat) }
$whenchanged = ""; if ($res.properties.contains("whenchanged")) { $whenchanged = ([datetime]$res.properties["whenchanged"][0]).ToString($sqldateformat) }
$lastupdated = [datetime]::Now.ToUniversalTime().ToString($sqldateformat)
$sqlcommand = "DELETE FROM $table WHERE [AD_Group_Name] = '$(Esc-Quote $samaccountname)'; "
$sqlcommand += "INSERT INTO $table ([AD_Group_Name], [AD_Group_DisplayName], [AD_Group_Description], [Last_Update], [Managed_By],[Distinguished_Name],[Group_Category],[Created_On], AD_Last_Modified]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $displayName)', '$(Esc-Quote $description)', '$lastupdated', '$(Esc-Quote $managedby)', '$(Esc-Quote $distinguishedname)', '$grouptype', '$whencreated','$whenchanged')"

$sqlcommands += $sqlcommand


if ($sqlcommands.count -ge 100) { Run-DBCommand $sqlcommands; $sqlcommands = @()
}
}
Run-DBCommand $sqlcommands
if ($numgroups -eq $count) {
Run-DBCommand @("DELETE FROM $table WHERE [Last_Update] <= '$starttime'")
}
Write-Progress " " " " -Completed
}

Open-Database
Run-GroupMemberExport "users"
Run-GroupMemberExport "computers"
Run-GroupDescriptionExport
Close-Database









share|improve this question
















I don't know much about PowerShell but have inherited a script from someone who is no longer available for assistance. This script imports AD Group Info and memberships related to Users and Computers. It works fine when run on a machine with PS 2.0 but it crashes if executed on PS 3.0 or newer.



I have not been able to figure out what needs to be modified but it seems the errors start occurring in the "Computer" membership import step and there are hundreds of errors that all say:



Command failed while processing computers: , Exception of type 'System.OutOfMemoryException' was thrown



Then at some point it looks like the script just stops and it never even gets to the 3rd step / function.



Any advice?



[Reflection.Assembly]::LoadWithPartialName("System.DirectoryServices") | Out-Null

$DBServer = "DBSERVER"
$DBName = "DBNAME"
$TableUsers = "[$DBName].[dbo].[AD_GroupToClient]"
$TableComps = "[$DBName].[dbo].[AD_GroupToDevice]"
$TableGroups = "[$DBName].[dbo].[AD_Group_Info]"
$sqldateformat = "yyyy/MM/dd HH:mm:ss:fff"

[system.Data.SqlClient.SqlConnection]$global:SqlConnection = $null

function Get-ScriptPath { $Invocation = (Get-Variable MyInvocation -Scope 1).Value; Split-Path $Invocation.MyCommand.Path }
$ScriptPath = Get-ScriptPath
$Logfile = "$ScriptPathOutLog.log"

function Write-Logfile {
param($logtext)
[string](Get-Date -format $sqldateformat) + "`t$logtext" | Out-File $Logfile -Encoding ascii -Append
}

function Open-Database {
$global:SqlConnection = New-Object system.Data.SqlClient.SqlConnection
try {
$global:SqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True"
$global:SqlConnection.Open() | Out-Null
Write-Logfile "OK`tDatabase opened"
} catch {
Write-Host "Error Opening SQL Database`t$($_.Exception.Message)"
Write-Logfile "Error`tDatabase open failed, $($_.exception.message)"
exit
}
}

function Close-Database {
$global:SqlConnection.Close()
Write-Logfile "OK`tDatabase closed"
}

function Esc-Quote {
param($str)
if ($str) { $str.Replace("'","''") }
}

function Run-DBCommand {
param($SqlCommands, [switch]$getnumrows)
if ($SqlCommands.Count -ge 1) {
$SqlCommandText = [string]::Join(";", $SqlCommands)
try {
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$returnvalue = $SqlCmd.ExecuteNonQuery()
if ($getnumrows) { return $returnvalue }
} catch {
Write-Logfile "Error`tSQL Command failed, $($_.exception.message)"
}
}
}

function Run-GroupMemberExport {
param($exportmode)
switch ($exportmode) {
"users" {
$dom = [ADSI]"LDAP://OU=Clients123,DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=user)(objectCategory=person)(samaccountname=*))"
$table = $TableUsers
$namecolumn = "AD_Group_Member_Name"
$attribs = @("samaccountname")
}
"computers" {
$dom = [ADSI]"LDAP://DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=computer)(samaccountname=*))"
$table = $TableComps
$namecolumn = "AD_Group_Member_Device"
$attribs = @("samaccountname", "whencreated")
}
}
$starttime = (Get-Date).ToUniversalTime().ToString($sqldateformat)
$srch = New-Object DirectoryServices.DirectorySearcher($dom, $query, $attribs)
$srch.PageSize = 1000
$srch.Sort = New-Object DirectoryServices.SortOption("sAMAccountName", [DirectoryServices.SortDirection]::Ascending)
$results = $srch.FindAll()

$count = 0
$numaccounts = $results.Count
foreach ($res in $results) {
try {
$objAccount = $res.GetDirectoryEntry()
$samaccountname = $objAccount.properties["samaccountname"][0]
$whencreated = ""
if ($exportmode -eq "computers") { $whencreated = Get-Date ([datetime]$objAccount.properties["whencreated"][0]) -Format $sqldateformat }
$count++
Write-Progress "Querying accounts" $samaccountname -PercentComplete ($count * 100.0 / $numaccounts)
$objAccount.psbase.RefreshCache("tokenGroups")
$SIDs = $objAccount.psbase.Properties.Item("tokenGroups")
$groups = @()
ForEach ($Value In $SIDs) {
$SID = New-Object System.Security.Principal.SecurityIdentifier $Value, 0
try {
$Group = $SID.Translate([System.Security.Principal.NTAccount]).Value
} catch {
$Group = $SID.Translate([System.Security.Principal.SecurityIdentifier]).Value
}
if ($groups -notcontains $Group -and $Group.Split("")[1] -ne $samaccountname) { $groups += $Group }
}
Run-DBCommand @("DELETE FROM $table WHERE [$namecolumn] = '$(Esc-Quote $samaccountname)'")
$sqlcommands = @()
$currenttime = (Get-Date).ToUniversalTime().ToString($sqldateformat)
if ($groups) {
$groups | sort | foreach {
if ($exportmode -eq "users") {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $_)', '$currenttime')"
} else {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update], [Record_Created]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $_)', '$currenttime', '$whencreated')"
}
if ($sqlcommands.count -ge 50) { Run-DBCommand $sqlcommands; $sqlcommands = @() }
}
} else {
if ($exportmode -eq "users") {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update]) VALUES ('$(Esc-Quote $samaccountname)', 'ERROR: Unable to retrieve groups', '$currenttime')"
} else {
$sqlcommands += "INSERT INTO $table ([$namecolumn], [AD_Group_Name], [Last_Update], [Record_Created]) VALUES ('$(Esc-Quote $samaccountname)', 'ERROR: Unable to retrieve groups', '$currenttime', '$whencreated')"
}
}
Run-DBCommand $sqlcommands
} catch {
Write-Logfile "Error`tCommand failed while processing $exportmode`: $($objAccount.name), $($_.exception.message)"
}
}
Write-Progress " " " " -Completed
if ($count -eq $numaccounts) {
$numdeleted = Run-DBCommand @("DELETE FROM $table WHERE [Last_Update] < '$starttime' OR [Last_Update] IS NULL") -getnumrows
Write-Logfile "OK`tUpdates for $exportmode completed, $numdeleted old records deleted."
}
}

function Run-GroupDescriptionExport {
$dom = [ADSI]"LDAP://DC=test1,DC=test2,DC=test3"
$query = "(&(objectClass=group)(samaccountname=*))"
$table = $TableGroups
$attribs = @("samaccountname", "displayname", "description", "whencreated", "managedby", "grouptype","distinguishedname","whenchanged")
$srch = New-Object DirectoryServices.DirectorySearcher($dom, $query, $attribs)
$srch.PageSize = 1000
$srch.Sort = New-Object DirectoryServices.SortOption("sAMAccountName", [DirectoryServices.SortDirection]::Ascending)
$results = $srch.FindAll()
$count = 0
$numgroups = $results.Count
$sqlcommands = @()
$starttime = [datetime]::Now.ToUniversalTime().ToString($sqldateformat)
foreach ($res in $results) {
$count++
$samaccountname = $res.properties["samaccountname"][0]
Write-Progress "Querying accounts, $count/$numgroups" $samaccountname -PercentComplete ($count * 100.0 / $numgroups)
$displayName = ""; if ($res.properties.contains("displayname")) { $displayName = $res.properties["displayname"][0] }
$description = ""; if ($res.properties.contains("description")) { $description = $res.properties["description"][0] }
$managedby = ""; if ($res.properties.contains("managedby")) { $managedby = $res.properties["managedby"][0] }
$grouptype = ""; if ($res.properties.contains("grouptype")) { $grouptype = $res.properties["grouptype"][0] }
$distinguishedname = ""; if ($res.properties.contains("distinguishedname")) { $distinguishedname = $res.properties["distinguishedname"][0] }
$whencreated = ""; if ($res.properties.contains("whencreated")) { $whencreated = ([datetime]$res.properties["whencreated"][0]).ToString($sqldateformat) }
$whenchanged = ""; if ($res.properties.contains("whenchanged")) { $whenchanged = ([datetime]$res.properties["whenchanged"][0]).ToString($sqldateformat) }
$lastupdated = [datetime]::Now.ToUniversalTime().ToString($sqldateformat)
$sqlcommand = "DELETE FROM $table WHERE [AD_Group_Name] = '$(Esc-Quote $samaccountname)'; "
$sqlcommand += "INSERT INTO $table ([AD_Group_Name], [AD_Group_DisplayName], [AD_Group_Description], [Last_Update], [Managed_By],[Distinguished_Name],[Group_Category],[Created_On], AD_Last_Modified]) VALUES ('$(Esc-Quote $samaccountname)', '$(Esc-Quote $displayName)', '$(Esc-Quote $description)', '$lastupdated', '$(Esc-Quote $managedby)', '$(Esc-Quote $distinguishedname)', '$grouptype', '$whencreated','$whenchanged')"

$sqlcommands += $sqlcommand


if ($sqlcommands.count -ge 100) { Run-DBCommand $sqlcommands; $sqlcommands = @()
}
}
Run-DBCommand $sqlcommands
if ($numgroups -eq $count) {
Run-DBCommand @("DELETE FROM $table WHERE [Last_Update] <= '$starttime'")
}
Write-Progress " " " " -Completed
}

Open-Database
Run-GroupMemberExport "users"
Run-GroupMemberExport "computers"
Run-GroupDescriptionExport
Close-Database






powershell active-directory






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 26 '18 at 16:04









mklement0

130k20245281




130k20245281










asked Nov 26 '18 at 15:53









JetRocket11JetRocket11

6910




6910








  • 2





    That's a pretty big leap - assuming 3.0 is at fault. If you run the script, on the same machine having trouble, but with -version 2 parameters, do you still have an OutOfMemoryException occur? If so, it's not the versioning.

    – gravitymixes
    Nov 26 '18 at 15:55











  • I would start with commenting out some of the Try/Catch statements to check with line actually fails.

    – iRon
    Nov 26 '18 at 16:53






  • 1





    Thanks @gravitymixes. I tried -version 2 in the command statement on the same server where the script had issues before and so far it completed without any errors. I am going to run it again few more times to double check.

    – JetRocket11
    Nov 26 '18 at 20:40











  • Subsequent tests failed so -version 2 is not a solution on it's own. What I found though is the script executes 3 functions - only the computer one throws errors - but it works fine if the 'users' function is disabled in the run. Going to try finally { $objAccount.Dispose() suggestion

    – JetRocket11
    Nov 27 '18 at 16:22














  • 2





    That's a pretty big leap - assuming 3.0 is at fault. If you run the script, on the same machine having trouble, but with -version 2 parameters, do you still have an OutOfMemoryException occur? If so, it's not the versioning.

    – gravitymixes
    Nov 26 '18 at 15:55











  • I would start with commenting out some of the Try/Catch statements to check with line actually fails.

    – iRon
    Nov 26 '18 at 16:53






  • 1





    Thanks @gravitymixes. I tried -version 2 in the command statement on the same server where the script had issues before and so far it completed without any errors. I am going to run it again few more times to double check.

    – JetRocket11
    Nov 26 '18 at 20:40











  • Subsequent tests failed so -version 2 is not a solution on it's own. What I found though is the script executes 3 functions - only the computer one throws errors - but it works fine if the 'users' function is disabled in the run. Going to try finally { $objAccount.Dispose() suggestion

    – JetRocket11
    Nov 27 '18 at 16:22








2




2





That's a pretty big leap - assuming 3.0 is at fault. If you run the script, on the same machine having trouble, but with -version 2 parameters, do you still have an OutOfMemoryException occur? If so, it's not the versioning.

– gravitymixes
Nov 26 '18 at 15:55





That's a pretty big leap - assuming 3.0 is at fault. If you run the script, on the same machine having trouble, but with -version 2 parameters, do you still have an OutOfMemoryException occur? If so, it's not the versioning.

– gravitymixes
Nov 26 '18 at 15:55













I would start with commenting out some of the Try/Catch statements to check with line actually fails.

– iRon
Nov 26 '18 at 16:53





I would start with commenting out some of the Try/Catch statements to check with line actually fails.

– iRon
Nov 26 '18 at 16:53




1




1





Thanks @gravitymixes. I tried -version 2 in the command statement on the same server where the script had issues before and so far it completed without any errors. I am going to run it again few more times to double check.

– JetRocket11
Nov 26 '18 at 20:40





Thanks @gravitymixes. I tried -version 2 in the command statement on the same server where the script had issues before and so far it completed without any errors. I am going to run it again few more times to double check.

– JetRocket11
Nov 26 '18 at 20:40













Subsequent tests failed so -version 2 is not a solution on it's own. What I found though is the script executes 3 functions - only the computer one throws errors - but it works fine if the 'users' function is disabled in the run. Going to try finally { $objAccount.Dispose() suggestion

– JetRocket11
Nov 27 '18 at 16:22





Subsequent tests failed so -version 2 is not a solution on it's own. What I found though is the script executes 3 functions - only the computer one throws errors - but it works fine if the 'users' function is disabled in the run. Going to try finally { $objAccount.Dispose() suggestion

– JetRocket11
Nov 27 '18 at 16:22












1 Answer
1






active

oldest

votes


















2














This doesn't have anything to do with the PowerShell version. You're just plain running out of memory. You're pulling in a lot of data, so you need to be more conscious of getting rid of that data when you're done with it.



There are a couple things you can do to clean up memory:



First, the documentation for DirectorySearcher.FindAll() says:




Due to implementation restrictions, the SearchResultCollection class cannot release all of its unmanaged resources when it is garbage collected. To prevent a memory leak, you must call the Dispose method when the SearchResultCollection object is no longer needed.




So whenever you do:



$results = $srch.FindAll()


Make sure you call $results.Dispose() when you're done with it (at the end of the function).



Second, when you loop through the results in your Run-GroupMemberExport function, you're calling $res.GetDirectoryEntry(). Usually you can just let the garbage collector clean up DirectoryEntry objects, but when you're creating so many in a loop like that, the GC doesn't have time to run. This has happened to me when I've run a loop over thousands of accounts.



To solve this, you can call Dispose() on the DirectoryEntry objects yourself. Since you already have a try/catch block there, I would suggest adding a finally block to make sure it happens even if an error is thrown:



try {
...
} catch {
Write-Logfile "Error`tCommand failed while processing $exportmode`: $($objAccount.name), $($_.exception.message)"
} finally {
$objAccount.Dispose()
}


Actually, you could probably just not use GetDirectoryEntry() at all. Just ask the DirectorySearcher to return the other attributes you need. But if you want to still use it, then make sure you call RefreshCache for every attribute you need (you can put them all in one call to RefreshCache). If you access the Properties collection and ask for a value that it does not already have in cache, then it will ask AD for every attribute with a value - that's a lot of unnecessary data.






share|improve this answer





















  • 1





    Thanks. I will review your suggestions to see what improvements we can make in the script but just wanted to note that the script is executed in a corporate environment using an enterprise job scheduler utility. We have dozens of servers all with same memory and CPU. However the script only fails on those servers where PowerShell has been upgraded to newer versions. It runs just fine on any server that still has the 2.0 version. That is why I thought it all had to do with version and something in the code that is not compatible.

    – JetRocket11
    Nov 26 '18 at 20:43











  • Some promising results. I added a single instance of the 'finally' code you suggested which does $objAccount.Dispose() and the script fully completed without errors on the same server where it failed previously. All 3 functions within the script succeeded but took nearly 3 hours.

    – JetRocket11
    Nov 27 '18 at 16:23













  • That sounds about right. If you implement the solution at the end of my answer (to not use GetDirectoryEntry() that should really help the time. But it will likely still take a long time regardless simply because you're going through every account.

    – Gabriel Luci
    Nov 27 '18 at 16:39











  • I ran the job 3 more times and all completed from start to finish. However, when this script runs on the server with PS 3 version it takes twice as long as the same exact script on a server with PS 2 version. The servers have same specs. Any idea why? Also, on one of the runs, even though all steps completed there were 2 error instances in the logs from the "Computer" step which said: Command failed while processing computers: , Cannot index into a null array but it looks like the script ran past those and finished.

    – JetRocket11
    Nov 28 '18 at 21:25













  • Errors in PowerShell are non-terminating by default. So a script will just continue after any error. But you may get unexpected results. Do you know the exact line where the error happened?

    – Gabriel Luci
    Nov 28 '18 at 21:33











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53484771%2fpowershell-active-directory-import-script-failing-with-ps-3-0-or-above%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









2














This doesn't have anything to do with the PowerShell version. You're just plain running out of memory. You're pulling in a lot of data, so you need to be more conscious of getting rid of that data when you're done with it.



There are a couple things you can do to clean up memory:



First, the documentation for DirectorySearcher.FindAll() says:




Due to implementation restrictions, the SearchResultCollection class cannot release all of its unmanaged resources when it is garbage collected. To prevent a memory leak, you must call the Dispose method when the SearchResultCollection object is no longer needed.




So whenever you do:



$results = $srch.FindAll()


Make sure you call $results.Dispose() when you're done with it (at the end of the function).



Second, when you loop through the results in your Run-GroupMemberExport function, you're calling $res.GetDirectoryEntry(). Usually you can just let the garbage collector clean up DirectoryEntry objects, but when you're creating so many in a loop like that, the GC doesn't have time to run. This has happened to me when I've run a loop over thousands of accounts.



To solve this, you can call Dispose() on the DirectoryEntry objects yourself. Since you already have a try/catch block there, I would suggest adding a finally block to make sure it happens even if an error is thrown:



try {
...
} catch {
Write-Logfile "Error`tCommand failed while processing $exportmode`: $($objAccount.name), $($_.exception.message)"
} finally {
$objAccount.Dispose()
}


Actually, you could probably just not use GetDirectoryEntry() at all. Just ask the DirectorySearcher to return the other attributes you need. But if you want to still use it, then make sure you call RefreshCache for every attribute you need (you can put them all in one call to RefreshCache). If you access the Properties collection and ask for a value that it does not already have in cache, then it will ask AD for every attribute with a value - that's a lot of unnecessary data.






share|improve this answer





















  • 1





    Thanks. I will review your suggestions to see what improvements we can make in the script but just wanted to note that the script is executed in a corporate environment using an enterprise job scheduler utility. We have dozens of servers all with same memory and CPU. However the script only fails on those servers where PowerShell has been upgraded to newer versions. It runs just fine on any server that still has the 2.0 version. That is why I thought it all had to do with version and something in the code that is not compatible.

    – JetRocket11
    Nov 26 '18 at 20:43











  • Some promising results. I added a single instance of the 'finally' code you suggested which does $objAccount.Dispose() and the script fully completed without errors on the same server where it failed previously. All 3 functions within the script succeeded but took nearly 3 hours.

    – JetRocket11
    Nov 27 '18 at 16:23













  • That sounds about right. If you implement the solution at the end of my answer (to not use GetDirectoryEntry() that should really help the time. But it will likely still take a long time regardless simply because you're going through every account.

    – Gabriel Luci
    Nov 27 '18 at 16:39











  • I ran the job 3 more times and all completed from start to finish. However, when this script runs on the server with PS 3 version it takes twice as long as the same exact script on a server with PS 2 version. The servers have same specs. Any idea why? Also, on one of the runs, even though all steps completed there were 2 error instances in the logs from the "Computer" step which said: Command failed while processing computers: , Cannot index into a null array but it looks like the script ran past those and finished.

    – JetRocket11
    Nov 28 '18 at 21:25













  • Errors in PowerShell are non-terminating by default. So a script will just continue after any error. But you may get unexpected results. Do you know the exact line where the error happened?

    – Gabriel Luci
    Nov 28 '18 at 21:33
















2














This doesn't have anything to do with the PowerShell version. You're just plain running out of memory. You're pulling in a lot of data, so you need to be more conscious of getting rid of that data when you're done with it.



There are a couple things you can do to clean up memory:



First, the documentation for DirectorySearcher.FindAll() says:




Due to implementation restrictions, the SearchResultCollection class cannot release all of its unmanaged resources when it is garbage collected. To prevent a memory leak, you must call the Dispose method when the SearchResultCollection object is no longer needed.




So whenever you do:



$results = $srch.FindAll()


Make sure you call $results.Dispose() when you're done with it (at the end of the function).



Second, when you loop through the results in your Run-GroupMemberExport function, you're calling $res.GetDirectoryEntry(). Usually you can just let the garbage collector clean up DirectoryEntry objects, but when you're creating so many in a loop like that, the GC doesn't have time to run. This has happened to me when I've run a loop over thousands of accounts.



To solve this, you can call Dispose() on the DirectoryEntry objects yourself. Since you already have a try/catch block there, I would suggest adding a finally block to make sure it happens even if an error is thrown:



try {
...
} catch {
Write-Logfile "Error`tCommand failed while processing $exportmode`: $($objAccount.name), $($_.exception.message)"
} finally {
$objAccount.Dispose()
}


Actually, you could probably just not use GetDirectoryEntry() at all. Just ask the DirectorySearcher to return the other attributes you need. But if you want to still use it, then make sure you call RefreshCache for every attribute you need (you can put them all in one call to RefreshCache). If you access the Properties collection and ask for a value that it does not already have in cache, then it will ask AD for every attribute with a value - that's a lot of unnecessary data.






share|improve this answer





















  • 1





    Thanks. I will review your suggestions to see what improvements we can make in the script but just wanted to note that the script is executed in a corporate environment using an enterprise job scheduler utility. We have dozens of servers all with same memory and CPU. However the script only fails on those servers where PowerShell has been upgraded to newer versions. It runs just fine on any server that still has the 2.0 version. That is why I thought it all had to do with version and something in the code that is not compatible.

    – JetRocket11
    Nov 26 '18 at 20:43











  • Some promising results. I added a single instance of the 'finally' code you suggested which does $objAccount.Dispose() and the script fully completed without errors on the same server where it failed previously. All 3 functions within the script succeeded but took nearly 3 hours.

    – JetRocket11
    Nov 27 '18 at 16:23













  • That sounds about right. If you implement the solution at the end of my answer (to not use GetDirectoryEntry() that should really help the time. But it will likely still take a long time regardless simply because you're going through every account.

    – Gabriel Luci
    Nov 27 '18 at 16:39











  • I ran the job 3 more times and all completed from start to finish. However, when this script runs on the server with PS 3 version it takes twice as long as the same exact script on a server with PS 2 version. The servers have same specs. Any idea why? Also, on one of the runs, even though all steps completed there were 2 error instances in the logs from the "Computer" step which said: Command failed while processing computers: , Cannot index into a null array but it looks like the script ran past those and finished.

    – JetRocket11
    Nov 28 '18 at 21:25













  • Errors in PowerShell are non-terminating by default. So a script will just continue after any error. But you may get unexpected results. Do you know the exact line where the error happened?

    – Gabriel Luci
    Nov 28 '18 at 21:33














2












2








2







This doesn't have anything to do with the PowerShell version. You're just plain running out of memory. You're pulling in a lot of data, so you need to be more conscious of getting rid of that data when you're done with it.



There are a couple things you can do to clean up memory:



First, the documentation for DirectorySearcher.FindAll() says:




Due to implementation restrictions, the SearchResultCollection class cannot release all of its unmanaged resources when it is garbage collected. To prevent a memory leak, you must call the Dispose method when the SearchResultCollection object is no longer needed.




So whenever you do:



$results = $srch.FindAll()


Make sure you call $results.Dispose() when you're done with it (at the end of the function).



Second, when you loop through the results in your Run-GroupMemberExport function, you're calling $res.GetDirectoryEntry(). Usually you can just let the garbage collector clean up DirectoryEntry objects, but when you're creating so many in a loop like that, the GC doesn't have time to run. This has happened to me when I've run a loop over thousands of accounts.



To solve this, you can call Dispose() on the DirectoryEntry objects yourself. Since you already have a try/catch block there, I would suggest adding a finally block to make sure it happens even if an error is thrown:



try {
...
} catch {
Write-Logfile "Error`tCommand failed while processing $exportmode`: $($objAccount.name), $($_.exception.message)"
} finally {
$objAccount.Dispose()
}


Actually, you could probably just not use GetDirectoryEntry() at all. Just ask the DirectorySearcher to return the other attributes you need. But if you want to still use it, then make sure you call RefreshCache for every attribute you need (you can put them all in one call to RefreshCache). If you access the Properties collection and ask for a value that it does not already have in cache, then it will ask AD for every attribute with a value - that's a lot of unnecessary data.






share|improve this answer















This doesn't have anything to do with the PowerShell version. You're just plain running out of memory. You're pulling in a lot of data, so you need to be more conscious of getting rid of that data when you're done with it.



There are a couple things you can do to clean up memory:



First, the documentation for DirectorySearcher.FindAll() says:




Due to implementation restrictions, the SearchResultCollection class cannot release all of its unmanaged resources when it is garbage collected. To prevent a memory leak, you must call the Dispose method when the SearchResultCollection object is no longer needed.




So whenever you do:



$results = $srch.FindAll()


Make sure you call $results.Dispose() when you're done with it (at the end of the function).



Second, when you loop through the results in your Run-GroupMemberExport function, you're calling $res.GetDirectoryEntry(). Usually you can just let the garbage collector clean up DirectoryEntry objects, but when you're creating so many in a loop like that, the GC doesn't have time to run. This has happened to me when I've run a loop over thousands of accounts.



To solve this, you can call Dispose() on the DirectoryEntry objects yourself. Since you already have a try/catch block there, I would suggest adding a finally block to make sure it happens even if an error is thrown:



try {
...
} catch {
Write-Logfile "Error`tCommand failed while processing $exportmode`: $($objAccount.name), $($_.exception.message)"
} finally {
$objAccount.Dispose()
}


Actually, you could probably just not use GetDirectoryEntry() at all. Just ask the DirectorySearcher to return the other attributes you need. But if you want to still use it, then make sure you call RefreshCache for every attribute you need (you can put them all in one call to RefreshCache). If you access the Properties collection and ask for a value that it does not already have in cache, then it will ask AD for every attribute with a value - that's a lot of unnecessary data.







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 26 '18 at 18:33

























answered Nov 26 '18 at 16:38









Gabriel LuciGabriel Luci

11k11525




11k11525








  • 1





    Thanks. I will review your suggestions to see what improvements we can make in the script but just wanted to note that the script is executed in a corporate environment using an enterprise job scheduler utility. We have dozens of servers all with same memory and CPU. However the script only fails on those servers where PowerShell has been upgraded to newer versions. It runs just fine on any server that still has the 2.0 version. That is why I thought it all had to do with version and something in the code that is not compatible.

    – JetRocket11
    Nov 26 '18 at 20:43











  • Some promising results. I added a single instance of the 'finally' code you suggested which does $objAccount.Dispose() and the script fully completed without errors on the same server where it failed previously. All 3 functions within the script succeeded but took nearly 3 hours.

    – JetRocket11
    Nov 27 '18 at 16:23













  • That sounds about right. If you implement the solution at the end of my answer (to not use GetDirectoryEntry() that should really help the time. But it will likely still take a long time regardless simply because you're going through every account.

    – Gabriel Luci
    Nov 27 '18 at 16:39











  • I ran the job 3 more times and all completed from start to finish. However, when this script runs on the server with PS 3 version it takes twice as long as the same exact script on a server with PS 2 version. The servers have same specs. Any idea why? Also, on one of the runs, even though all steps completed there were 2 error instances in the logs from the "Computer" step which said: Command failed while processing computers: , Cannot index into a null array but it looks like the script ran past those and finished.

    – JetRocket11
    Nov 28 '18 at 21:25













  • Errors in PowerShell are non-terminating by default. So a script will just continue after any error. But you may get unexpected results. Do you know the exact line where the error happened?

    – Gabriel Luci
    Nov 28 '18 at 21:33














  • 1





    Thanks. I will review your suggestions to see what improvements we can make in the script but just wanted to note that the script is executed in a corporate environment using an enterprise job scheduler utility. We have dozens of servers all with same memory and CPU. However the script only fails on those servers where PowerShell has been upgraded to newer versions. It runs just fine on any server that still has the 2.0 version. That is why I thought it all had to do with version and something in the code that is not compatible.

    – JetRocket11
    Nov 26 '18 at 20:43











  • Some promising results. I added a single instance of the 'finally' code you suggested which does $objAccount.Dispose() and the script fully completed without errors on the same server where it failed previously. All 3 functions within the script succeeded but took nearly 3 hours.

    – JetRocket11
    Nov 27 '18 at 16:23













  • That sounds about right. If you implement the solution at the end of my answer (to not use GetDirectoryEntry() that should really help the time. But it will likely still take a long time regardless simply because you're going through every account.

    – Gabriel Luci
    Nov 27 '18 at 16:39











  • I ran the job 3 more times and all completed from start to finish. However, when this script runs on the server with PS 3 version it takes twice as long as the same exact script on a server with PS 2 version. The servers have same specs. Any idea why? Also, on one of the runs, even though all steps completed there were 2 error instances in the logs from the "Computer" step which said: Command failed while processing computers: , Cannot index into a null array but it looks like the script ran past those and finished.

    – JetRocket11
    Nov 28 '18 at 21:25













  • Errors in PowerShell are non-terminating by default. So a script will just continue after any error. But you may get unexpected results. Do you know the exact line where the error happened?

    – Gabriel Luci
    Nov 28 '18 at 21:33








1




1





Thanks. I will review your suggestions to see what improvements we can make in the script but just wanted to note that the script is executed in a corporate environment using an enterprise job scheduler utility. We have dozens of servers all with same memory and CPU. However the script only fails on those servers where PowerShell has been upgraded to newer versions. It runs just fine on any server that still has the 2.0 version. That is why I thought it all had to do with version and something in the code that is not compatible.

– JetRocket11
Nov 26 '18 at 20:43





Thanks. I will review your suggestions to see what improvements we can make in the script but just wanted to note that the script is executed in a corporate environment using an enterprise job scheduler utility. We have dozens of servers all with same memory and CPU. However the script only fails on those servers where PowerShell has been upgraded to newer versions. It runs just fine on any server that still has the 2.0 version. That is why I thought it all had to do with version and something in the code that is not compatible.

– JetRocket11
Nov 26 '18 at 20:43













Some promising results. I added a single instance of the 'finally' code you suggested which does $objAccount.Dispose() and the script fully completed without errors on the same server where it failed previously. All 3 functions within the script succeeded but took nearly 3 hours.

– JetRocket11
Nov 27 '18 at 16:23







Some promising results. I added a single instance of the 'finally' code you suggested which does $objAccount.Dispose() and the script fully completed without errors on the same server where it failed previously. All 3 functions within the script succeeded but took nearly 3 hours.

– JetRocket11
Nov 27 '18 at 16:23















That sounds about right. If you implement the solution at the end of my answer (to not use GetDirectoryEntry() that should really help the time. But it will likely still take a long time regardless simply because you're going through every account.

– Gabriel Luci
Nov 27 '18 at 16:39





That sounds about right. If you implement the solution at the end of my answer (to not use GetDirectoryEntry() that should really help the time. But it will likely still take a long time regardless simply because you're going through every account.

– Gabriel Luci
Nov 27 '18 at 16:39













I ran the job 3 more times and all completed from start to finish. However, when this script runs on the server with PS 3 version it takes twice as long as the same exact script on a server with PS 2 version. The servers have same specs. Any idea why? Also, on one of the runs, even though all steps completed there were 2 error instances in the logs from the "Computer" step which said: Command failed while processing computers: , Cannot index into a null array but it looks like the script ran past those and finished.

– JetRocket11
Nov 28 '18 at 21:25







I ran the job 3 more times and all completed from start to finish. However, when this script runs on the server with PS 3 version it takes twice as long as the same exact script on a server with PS 2 version. The servers have same specs. Any idea why? Also, on one of the runs, even though all steps completed there were 2 error instances in the logs from the "Computer" step which said: Command failed while processing computers: , Cannot index into a null array but it looks like the script ran past those and finished.

– JetRocket11
Nov 28 '18 at 21:25















Errors in PowerShell are non-terminating by default. So a script will just continue after any error. But you may get unexpected results. Do you know the exact line where the error happened?

– Gabriel Luci
Nov 28 '18 at 21:33





Errors in PowerShell are non-terminating by default. So a script will just continue after any error. But you may get unexpected results. Do you know the exact line where the error happened?

– Gabriel Luci
Nov 28 '18 at 21:33




















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53484771%2fpowershell-active-directory-import-script-failing-with-ps-3-0-or-above%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

Calculate evaluation metrics using cross_val_predict sklearn

Insert data from modal to MySQL (multiple modal on website)