Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Powershell comma string to SQL table

Posted on 2014-04-06
25
Medium Priority
?
280 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
  • 17
  • 7
24 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
New Tabletop Appliances Blow Competitors Away!

WatchGuard’s new T15, T35 and T55 tabletop UTMs provide the highest-performing security inspection in their class, allowing users at small offices, home offices and distributed enterprises to experience blazing-fast Internet speeds without sacrificing enterprise-grade security.

 
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

Ready for your healthcare security check-up?

In the past few years, healthcare organizations have become a prime target for advanced attacks. Does your organization have what it needs to defend itself? Schedule your healthcare security check-up today and download our free Healthcare Security Resource Kit today!

Question has a verified solution.

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

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
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 anti-spam), the admin…
Screencast - Getting to Know the Pipeline

876 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