Solved

Powershell comma string to SQL table

Posted on 2014-04-06
25
248 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
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 68

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
 
LVL 8

Author Comment

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

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 68

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 68

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 68

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 68

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 68

Accepted Solution

by:
Qlemo earned 500 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now