powershell script - write variable values to a sql server table

Question

_{Friday, June 10, 2011 10:35 PM}

Hi, 
I have this script (ad_test.ps1) :

$strFilter = "(&(objectCategory=person)(objectClass=user))"

#$objDomain = New-Object System.DirectoryServices.DirectoryEntry

$objDomain = New-Object System.DirectoryServices.DirectoryEntry("LDAP://dc=xxxx,dc=com")

$Searcher = New-Object System.DirectoryServices.DirectorySearcher
$Searcher.SearchRoot = $objDomain

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.PageSize = 1000
$objSearcher.Filter = $strFilter
$objSearcher.SearchScope = "Subtree"

$colProplist = "name", "jobTitle", "telephoneNumber"

foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

$colResults = $objSearcher.FindAll()

foreach ($objResult in $colResults)
    {$objItem = $objResult.Properties
         "Name: " + $objItem.name
         "Title: " + $objItem.jobTitle
         "Phone number: " + $objItem.telephoneNumber
         $Write-Host
    }

I would like to output the fields into the sql server table. How do I set the variables in the script to write to sql table instead of the $Write-Host?

Please let me know regarding this.

Thanks,
-NR

All replies (9)

_{Saturday, June 11, 2011 12:35 AM ✅Answered}

I got it working - writes to the sql table:

$strFilter = "(&(objectCategory=person)(objectClass=user))"

#$objDomain = New-Object System.DirectoryServices.DirectoryEntry

$objDomain = New-Object System.DirectoryServices.DirectoryEntry("LDAP://dc=xxxx,dc=com")

$Searcher = New-Object System.DirectoryServices.DirectorySearcher
$Searcher.SearchRoot = $objDomain

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.PageSize = 1000
$objSearcher.Filter = $strFilter
$objSearcher.SearchScope = "Subtree"

$colProplist = "name", "jobTitle", "telephoneNumber"

$sql_conn = New-Object system.Data.SqlClient.SqlConnection
$sql_conn.ConnectionString = "Data Source=srvrname;Integrated Security=true;Initial Catalog=dbname;"
$sql_conn.Open()

foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

$colResults = $objSearcher.FindAll()

foreach ($objResult in $colResults)
    {$objItem = $objResult.Properties
         "Name: " + $objItem.name
         "Title: " + $objItem.jobTitle
         "Phone number: " + $objItem.telephoneNumber
         $Write-Host

       ## Store the INSERT query in a variable
          $insert_query = "[dbname].[dbo].[tablename]
           ([name]
            ,[telephonenumber]
           ,[title]
           )
           VALUES('$objItem.name','$objItem.telephoneNumber','$objItem.jobTitle')"
              ## Add new object to the current database connection and then execute the query
              $execute_query = New-Object System.Data.SqlClient.SqlCommand
               $execute_query.connection = $sql_conn
                $execute_query.commandtext = $insert_query
               $execute_query.executenonquery()

    }

$sql_conn.Close()

_{Saturday, July 2, 2011 12:27 AM ✅Answered}

Hi,
I changed code as below and it works perfectly now:

$Flag = $objResult.Properties.Item("userAccountControl")[0]
If ($Flag -band 2)
{
    $Acct = "Disabled"
}
Else
{
    $Acct = "Enabled"
}

Thanks,
-NR

_{Monday, June 13, 2011 4:53 PM}

Hi,
Thank you - I have correct the script to get Title.

How can I run the script (located on a remote share - this remote server does not have powershell) in SSIS/SQL Agent job on a SQL Server? I tried UNC path to the ps1 script but it does not like it.

Please let me know regarding this.

Thanks,
-NR

_{Tuesday, June 14, 2011 7:44 PM}

Thank you.

One more clarification: some of the AD name attribute contains values with "apostrophe" and the script fails for these entries.

How to ignore/replace the apostrophe? Eg Tim O'Neil

Thanks
-NR

_{Tuesday, June 14, 2011 7:52 PM}

Hi,

In the above code - why do you have two $ sign for the Insert statement?

$Command.CommandText = "INSERT INTO MyTable(Name, Title, Phone) Values('$($Name)', '$($Title)', '$($Phone)')"

-Thanks,
NR

_{Wednesday, June 15, 2011 3:46 PM}

HI,
I used the -Replace code and it works perfectly.

Thank you for helping me out.

Thanks,
-NR.

_{Thursday, June 30, 2011 5:41 PM}

Hi,
I need to include in the above powershell script whether the user account is enabled or disabled and feed that into a column in sql table as "disabled"/"enabled".

How can I get this information in powershell?

Thanks,
-NR.

_{Thursday, June 30, 2011 11:10 PM}

HI,
If tried using this code but I am able to display the account as "disabled" for some users only. I have some users with values 66050;66082;546

$Searcher.PropertiesToLoad.Add("userAccountControl") > $Null
$Value = $Result.Properties.Item("userAccountControl")

if ($Value -eq 514)
 {
$acct = "Disabled”
 }

if ($Value -eq 512)
 {
 $acct = "Enabled”
 }

I get this error message when trying your example:
The '-bor' operator failed: Cannot convert the "System.DirectoryServices.ResultPropertyValueCollection" value of type "System.DirectoryServices.ResultPropertyValueCollection" to type "System.Int32"..

Thanks,
-NR

_{Friday, July 1, 2011 11:43 PM}

Hi,
I tried the above code and the output shows "disabled" for all entries.
If I have users with these values 514,546,66050,66082, I would like to display the col as "disabled" and rest all "enabled"

- Thanks
NR