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

x
?
Solved

AD Group Members to SQL Table

Posted on 2014-02-27
14
Medium Priority
?
693 Views
Last Modified: 2014-03-08
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
0
Comment
Question by:geohixon
[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
  • 8
  • 5
14 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39892558
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
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 39892564
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
 

Author Comment

by:geohixon
ID: 39892592
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39892634
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
 

Author Comment

by:geohixon
ID: 39892663
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
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39892699
"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
 

Author Comment

by:geohixon
ID: 39892712
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
 

Author Comment

by:geohixon
ID: 39892785
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
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39893027
Try...
     $insert_stmt = "INSERT INTO ADGroupMembers (GroupName, Name) VALUES ($Results.GroupName, $Results.Name)"
0
 

Author Comment

by:geohixon
ID: 39893182
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
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39893209
Try...
    $cmd.ExecuteScalar()
0
 

Author Comment

by:geohixon
ID: 39893246
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
 

Accepted Solution

by:
geohixon earned 0 total points
ID: 39900725
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
 

Author Closing Comment

by:geohixon
ID: 39914440
was able to figure it out by doing some other google searches
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

664 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