Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Powershell comma string to SQL table

Posted on 2014-04-06
25
Medium Priority
?
278 Views
Last Modified: 2014-04-11
The code below returns a comma delimited list, I now need to push this list into a Table in SQL what syntax do I need to Add.



Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=su,DC=sas" | ForEach-Object{

    $dn = $_.DistinguishedName.Split(',')

    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        ParentContainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }

}

Open in new window

0
Comment
Question by:Leo Torres
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 7
25 Comments
 
LVL 8

Author Comment

by:Leo Torres
ID: 39981245
MS SQL 2008.

As far as the commas.. You are probably right I am seeing it with commas when I display it on to the screen.

If that is an issue I need to address then let me know. My goal is to put this into a table.

Thank you
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39981347
No issue, just a formal notice.

The following code looks complex, but it reveals the full power of the .NET Data Adapter, and hence needs a lot of definitions.
I suppose most of it is obvious, but if not, don't hesitate to ask.
# Preparation for MSSQL inserts:
$constr = 'Server=mssqlserve01r\instance02; Database=AD; Integrated Security=true'
$select = 'select * from ADComputers'

$con = New-Object Data.SqlClient.SqlConnection ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ($select, $constr)
$dt  = New-Object Data.DataTable

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into ADComputers( Name, Parentcontainer)
                values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'  , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'ParentContainer' ) | out-null
$da.InsertCommand = $cmd
$dt  = New-Object Data.DataTable

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=su,DC=sas" | ForEach-Object{
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        ParentContainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'  ] = $_.Name
  $row['Parent'] = $_.ParentContainer
  $dt.Rows.Add($row)
}

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39981780
Something is not right

Error
Column 'Name' does not belong to table .
At line:27 char:3
+   $row['Name'] = $_.Name
+   ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], ArgumentException
    + FullyQualifiedErrorId : System.ArgumentException
 
Column 'Parent' does not belong to table .
At line:28 char:3
+   $row['Parent'] = $_.Parentcontainer
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], ArgumentException
    + FullyQualifiedErrorId : System.ArgumentException

Open in new window


Your Code Line 28 has Parent not Parentcontainer coukd this be an issue?
cls
# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB0X; Database=ReportServerTempDB; Integrated Security=true'
$select = 'select * from ADComputers'

$con = New-Object Data.SqlClient.SqlConnection ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ($select, $constr)
$dt  = New-Object Data.DataTable

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into ADComputers( Name, Parentcontainer)
                values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'  , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $cmd
$dt  = New-Object Data.DataTable

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        Parentcontainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['Parent'] = $_.Parentcontainer
  $dt.Rows.Add($row)
}
                                            

Open in new window


Table Creation
Use [ReportServerTempDB]

Create Table ADComputers(
EntryID Int,
CreationTime DateTime default current_Timestamp,
Parentcontainer varchar(2000),
Name varchar(2000)
)

Open in new window

0
Introducing the WatchGuard 420 Access Point

WatchGuard's newest access point includes an 802.11ac Wave 2 chipset, providing the fastest speeds for VoIP, video and music streaming, and large data file transfers. Additionally, enjoy the benefits of strong security as the 3rd radio delivers dedicated WIPS protection!

 
LVL 8

Author Comment

by:Leo Torres
ID: 39981788
I did change it to Parentcontainer but it still failed by the way.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39981798
There were two bugs in that script. This one should work now:
cls
# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB0X; Database=ReportServerTempDB; Integrated Security=true'
$select = 'select * from ADComputers where 1=0'

$con = New-Object Data.SqlClient.SqlConnection ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ($select, $constr)
$dt  = New-Object Data.DataTable

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into ADComputers( Name, Parentcontainer)
                values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'   , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $cmd
$da.Fill($dt)

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        Parentcontainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['ParentContainer'] = $_.Parentcontainer
  $dt.Rows.Add($row)
}

Open in new window

Note that we are initialising the DataTable with a SELECT not returning any rows - it is just for getting the necessary table definition for inserting rows.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39981816
Ok still not sure I understand the select. There are no error the query runs and returns 0 but no rows were inserted into table

Why you need to know table schema insert specifies the to columns?
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39981837
We are working with DataTables here, and they need to have a structure (column definition).

I've missed to perform a commit of the changes (inserts). Add the following line to the end of the script to perform a single commit, or after line 29 to commit each row:
$dt.AcceptChanges()

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39981848
Still no insert, no error. Tried with line inside and out side loop


cls
# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB0X; Database=ReportServerTempDB; Integrated Security=true'
$select = 'select * from ADComputers where 1=0'

$con = New-Object Data.SqlClient.SqlConnection ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ($select, $constr)
$dt  = New-Object Data.DataTable

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into ADComputers( Name, Parentcontainer)
                values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'   , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $cmd
$da.Fill($dt)

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        Parentcontainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['ParentContainer'] = $_.Parentcontainer
  $dt.Rows.Add($row)
 # $dt.AcceptChanges()

}

$dt.AcceptChanges()   

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39981862
Ran this statement from server to verify my rights

Insert into ADComputers (Name, Parentcontainer)
Select 'Lee', 'Torres'

statement inserted sucessfully
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39981878
I know for sure the original select is collecting data as I ran the core select and data was return

sample
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39982601
I added some print screens

cls
# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB03; Database=ReportServerTempDB; Integrated Security=true'
$select = 'Select * from dbo.ADComputers where 1=0'


Write-Host '1'

$con = New-Object Data.SqlClient.SqlConnection ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ($select, $constr)
$dt  = New-Object Data.DataTable

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into dbo.ADComputers( Name, Parentcontainer)
                values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'   , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $cmd
$da.Fill($dt)

 Write-Host '2'


Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
   Write-Host '3'
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        Parentcontainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['ParentContainer'] = $_.Parentcontainer
  $dt.Rows.Add($row)
 # $dt.AcceptChanges()
}

$dt.AcceptChanges()   

Open in new window


Output
1
0
2
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3

PS AD:\OU=Enterprise Servers,DC=us,DC=saas> 

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39982604
Its going thru the loop but no insert.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39983842
Hmm. Shouldn't use ADO.NET if I've got no clue how to do it ;-/
cls
Set-StrictMode -Version Latest

# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB03; Database=ReportServerTempDB; Integrated Security=true'

$con = New-Object Data.SqlClient.SqlConnection  ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ('select * from ADComputers where 1=0', $constr)
$dt  = New-Object Data.DataTable('ADComputers')

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into dbo.ADComputers( Name,  Parentcontainer)
                    values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'   , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $insert
$da.Fill($dt)

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | 
ForEach-Object {
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = $dn[0] -replace 'cn='
        Parentcontainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['ParentContainer'] = $_.Parentcontainer
  $dt.Rows.Add($row)
}
$da.Update($dt)
$con.Close()

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39984824
Something wring with array

Index was outside the bounds of the array.
At line:23 char:5
+     New-Object PSObject -Property @{
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], IndexOutOfRangeException
    + FullyQualifiedErrorId : System.IndexOutOfRangeException
 
Index was outside the bounds of the array.

Open in new window

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39985380
That's strange, and something outside of the code change I provided. Looks like the LDAP result isn't as expected.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39993338
Any new ideas
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39993472
Ran this code

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
     $_.DistinguishedName.Split(',')
     $dn = $_.DistinguishedName.Split(',')

} |Export-Csv C:\File.csv



Got this (this is only a small Portion)
#TYPE System.String
"Length"
"14"
"7"
"6"
"10"
"5"
"10"
"21"
"5"
"7"
"14"
"7"
"6"
"10"
"5"
"10"
"21"
"5"
"7"
"14"
"7"
"6"
"10"
"5"
"10"
"21"
"5"
"7"
"14"
"7"
"6"
"10"
"5"

Open in new window

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39993500
That tells nothing but that there are strings returned.

The error message of http:#a39984824 only can result from a row not having a distinguished name, or only one component in it. Again, the issue is in your original code, so that error should also occur with your code as in the question. $dn[0] is the "culprit" throwing that error.
What we can do is modifying line 22 of http:#a39983842 to make sure we always have an array:
    $dn = @($_.DistinguishedName.Split(','))

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39993533
The issue is with this Line

 ParentContainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU='

I was not getting this error before

Index was outside the bounds of the array.
At line:34 char:4
+    New-Object PSObject -Property @{
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], IndexOutOfRangeException
    + FullyQualifiedErrorId : System.IndexOutOfRangeException
 
Index was outside the bounds of the array.

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39993543
If I comment out the Line above I get all the values in [0] but I don't get parent tree results which its what I need.


FYI i did  make that change you posted above and it still errored

Code
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39993553
In my opinon I belive it may have something to do with the variables lengths of each Server.

you may have server 3 folders deep or a server 8 Folders deep.


I ran this and it returned values but it errored out when more than 9  
$dn[1] + 
                             ','+ $dn[2] +
                             ','+ $dn[3] +
                             ','+ $dn[4] +
                             ','+ $dn[5] +
                             ','+ $dn[6] +
                             ','+ $dn[7] +
                             ','+ $dn[8] 

Open in new window




code2
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 39993564
I insist on that being an error from the very start (i.e the code you provided). The index of this expression is too big (by one). $dn.Length is e.g. 3 if three elements are in that array, but the index starts with 0, so allowable indexes are 0..2 and not 0..3.

It "worked" because PS ignored that bug. My Set-StrictMode statements involves index boundary checks, and hence you now get an error.

You can remove the Set-StrictMode line, or correct the index usage:
ParentContainer = $dn[($dn.length-1)..1] -notlike 'dc=*' -join ',' -replace 'OU='

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39993596
Working code thanks
cls
Set-StrictMode -Version Latest

# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB03; Database=ReportServerTempDB; Integrated Security=true'

$con = New-Object Data.SqlClient.SqlConnection  ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ('select * from ADComputers where 1=0', $constr)
$dt  = New-Object Data.DataTable('ADComputers')

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into dbo.ADComputers( Name,  Parentcontainer)
                    values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'   , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $insert
$da.Fill($dt)

Get-ADComputer -LDAPFilter "(DistinguishedName=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
     
      $dn = @($_.DistinguishedName.Split(','))
    
   New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        ParentContainer = $dn[($dn.length-1)..1] -notlike 'dc=*' -join ',' -replace 'OU='

    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['ParentContainer'] = $_.Parentcontainer
  $dt.Rows.Add($row)
}
$da.Update($dt)
$con.Close()

Open in new window

0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 39993597
Thank you for your fast and accurate help..
0

Featured Post

Vim Reference Guide

Vim is a powerful text editor favored by many sysadmins and developers - here are some commands that you'll want to keep in your back pocket!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question