AD Group Members to SQL Table

Here is my powershell statement with associated error.  Can't figure out what I am doing wrong.


PS H:\> Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group |
      Select-Object @{Name="Group Name";Expression={$Group}},name
} | out-host
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=LSI-1; Initial Catalog=databasename; Integrated Security=SSPI")
$conn.Open()
$insert_stmt = "INSERT INTO ADGroupMembers('$Results')"
$cmd = $conn.CreateCommand()
$cmd.CommandText = $insert_stmt
$cmd.ExecuteNonQuery()
$conn.Close()


Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near ')'."
At line:11 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

Any assistance would be greatly appreciated.  Also I am planning on running this script once a day and would probably like to drop and re-add the table with the new data.  Insight into this would also be beneficial
geohixonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul MacDonaldDirector, Information SystemsCommented:
It may be expecting a VALUES statement:
     INSERT INTO ADGroupMembers VALUES ('$Results')

http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx
0
Justin YeungSenior Systems EngineerCommented:
I am not sure about the SQL part but | out-host after } of each object will only result in the last searched result

you should include | out-host within the } after ,name
0
geohixonAuthor Commented:
Getting closer but now get this (see screenshot).
Also the table names in the table are exactly the same in the output  "Group Name" and "Name" set to nvarchar(50)

screenshot of results
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
Paul MacDonaldDirector, Information SystemsCommented:
Okay, so the error is self-explanatory - "the number of supplied values does not match the table definition" so you either need to tell it what column the values go into or you need to supply values for all the columns.
0
geohixonAuthor Commented:
This is what I am trying to figure out.  what should be there.  I tried this

Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group |
      Select-Object @{Name="GroupName";Expression={$Group}},name
} | out-host
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=LSI-1; Initial Catalog=database; Integrated Security=SSPI")
$conn.Open()
$insert_stmt = "INSERT INTO ADGroupMembers VALUES($Results.GroupName,$Results.Name)"
$cmd = $conn.CreateCommand()
$cmd.CommandText = $insert_stmt
$cmd.ExecuteNonQuery()
$conn.Close()

Exception calling "ExecuteNonQuery" with "0" argument(s): "The name ".GroupName" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not
permitted."
At line:11 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException
0
Paul MacDonaldDirector, Information SystemsCommented:
"This is what I am trying to figure out.  what should be there."
What's your table's structure?  Generally the command takes the form of:
    INSERT INTO table (columnname1, columnname2, columnname3) VALUES (value1, value2, value3)
0
geohixonAuthor Commented:
results provides two columns, "GroupName" and "name" as seen in the screenshot above, the table contains the exact two column names.  Essentially I need the results from
Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group |
      Select-Object @{Name="GroupName";Expression={$Group}},name | out-host
}
 entered into a SQL Table called ADGroupMembers that has two columns as well (GroupName, Name)
0
geohixonAuthor Commented:
maybe my approach is all wrong to start with and there is a better way.  I need to take groups from a text file, query AD and find all the users that are in those said groups then take the results and insert the group name and the member into a sql table.  Is there another approach I should be taking for this?  Thanks again as I am new to powershell (as you can tell) and trying to piece together stuff from google to get what I need.
0
Paul MacDonaldDirector, Information SystemsCommented:
Try...
     $insert_stmt = "INSERT INTO ADGroupMembers (GroupName, Name) VALUES ($Results.GroupName, $Results.Name)"
0
geohixonAuthor Commented:
Get this error.


INSERT INTO ADGroupMembers (GroupName, Name) VALUES(.groupname, .name)
-1
Exception calling "ExecuteNonQuery" with "0" argument(s): "The name ".groupname" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not
permitted."
At line:16 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException
0
Paul MacDonaldDirector, Information SystemsCommented:
Try...
    $cmd.ExecuteScalar()
0
geohixonAuthor Commented:
Still no go...

INSERT INTO ADGroupMembers (GroupName, Name) VALUES(.groupname, .name)
-1
Exception calling "ExecuteScalar" with "0" argument(s): "The name ".groupname" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not
permitted."
At line:16 char:1
+ $cmd.ExecuteScalar()
+ ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException
 
when I try this I see all the user info under 1 row.  

Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group  
     # Select-Object @{Name="GroupName";Expression={$Group}},name, samaccountname | Out-Host
}
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=LSI-1; Initial Catalog=db; Integrated Security=SSPI")
$conn.Open()
$insert_stmt = "INSERT INTO ADGroupMembers VALUES('"+ $group +"','"+ $Results +"')"
write $insert_stmt
$cmd = $conn.CreateCommand()
#delete existing contents of this table (enter SysTrack DB Name below)
$cmd.CommandText = "truncate table " + $db + ".ADGroupMembers"
$cmd.ExecuteNonQuery()
#import new/current data
$cmd.CommandText = $insert_stmt
$cmd.ExecuteNonQuery()
$conn.Close()

INSERT INTO ADGroupMembers VALUES('ACL_Builds','CN=Linux Build,OU=Engineering,OU= Hills,DC=domain,DC=org CN=BUILD1,OU=Engineering Computers,OU=Engineering,OU=HQ,DC=domain,DC=org CN=Support Group,OU=Su
pport Users,OU=Support,OU=HQ,DC=domain,DC=org CN=Domain Admins,CN=Users,DC=domain,DC=org CN=Engineering Group,OU=Engineering Users,OU=Engineering,OU=HQ,DC=domain,DC=org')
-1
Exception calling "ExecuteNonQuery" with "0" argument(s): "String or binary data would be truncated.
The statement has been terminated."
At line:16 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException
0
geohixonAuthor Commented:
Figured it out.

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=sqlserver; Initial Catalog=databasename; Integrated Security=SSPI")
$conn.Open()

$cmd = $conn.CreateCommand()
#delete existing contents of this table (enter sqlDB Name below)
$cmd.CommandText = "truncate table " + $dbname+ ".ADGroupMembers"
$cmd.ExecuteNonQuery()
#import new/current data

$content = Get-Content c:\temp\groups.txt
foreach ($Group in $content)
{
     
     $Results = Get-ADGroupMember $Group  
           Select-Object @{Name="GroupName";Expression={$Group}},name

     foreach ($name in $Results)
     {
          $insert_stmt = "INSERT INTO ADGroupMembers VALUES('"+ $group +"', '"+ $name.name +"')"
          write $insert_stmt
          $cmd.CommandText = $insert_stmt
          $cmd.ExecuteNonQuery()
          }
          }
          $conn.Close()
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geohixonAuthor Commented:
was able to figure it out by doing some other google searches
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.