Powershell Get-QADUser结果到SQL表 [英] Powershell Get-QADUser results to SQL table

问题描述

我正在用一个字符串查询Active Directory，该字符串循环遍历系统中的每个域控制器并返回一组结果.该脚本可与export-csv一起很好地工作，但是由于我们希望将所有数据保留在自定义信息字段中(它包含回车符)，因此我希望将其直接导出到SQL表中.

I'm querying Active Directory with a string which loops through each domain controller in our system and returns a set of results. The script works great with export-csv but because we wish to retain all data in the custom info field (it contains carriage-returns) I'd like to export this directly into an SQL table.

Powershell报告的错误为:

The error reported by Powershell reads:

使用"0"参数调用"ExecuteNonQuery"的异常:插入错误:列名或提供的值数与表定义不匹配."

Exception calling "ExecuteNonQuery" with "0" argument(s): "Insert Error: Column name or >number of supplied values does not match table definition."

这是一个非常冗长的响应，我创建并命名了每个表的列，以使其与get-object的输出完全匹配.

Which is a pretty verbose response, I've created and named the columns of each table to exactly match the output of the get-object.

这是管道的输出:

SamAccountName     : testuser
DisplayName        : Test User (COMPANY)
info               : Test Entry 1234567890

                 Test for output.
                 Entering


                 Multiple lines.
whenCreated        : 09/11/2004 09:08:42
whenChanged        : 19/07/2012 09:25:21
AccountExpires     :
pwdLastSet         : 13/06/2012 07:43:43
LastLogonTimestamp : 18/07/2012 15:38:35
userAccountControl : 512
Name               : Test User
LastLogon          :
DC                 : DCNAME1

这是代码:

##--AD data output to SQL script, you need the Quest plugin!

$SamAccountName = Read-Host "Enter the username to query for last logon"

##--Query domain for all domain controllers and funnel into a forEach loop

Get-QADComputer -ComputerRole DomainController | Foreach-Object{
$dc = $_.Name

##--Query each domain controller for the user object and retrieve the LastLogon timestamp

$user = Get-QADUser -Service $dc  -SamAccountName $SamAccountName -IncludedProperties info,pwdLastSet,AccountExpires,userAccountControl | Select-Object SamAccountName,displayName,info,whenCreated,whenChanged,accountExpires,pwdLastSet,lastLogonTimestamp,userAccountControl,name,LastLogon,@{n='DC';e={$dc}} |

out-host

##--Open database connection

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

##--AAGH! How to grab the results of the Select-Object above?

$cmd = $conn.CreateCommand()
$cmd.CommandText ="INSERT extract VALUES ('$user')"
$cmd.ExecuteNonQuery()

##--Don't forget to close it!

$conn.Close()

现在，我正在弄乱一些显而易见的东西，任何帮助都将不胜感激.

Now I'm messing something up which is probably plainly obvious, any help much appreciated.

推荐答案

在这里，我使用的是SqlServerCmdletSnapin，它是Invoke-sqlcmd.该脚本使用MSSQL数据库EMPLOYEE和表EMPLOYEE_DOMAIN.希望能帮助到你.对我来说很好..

here I'm using SqlServerCmdletSnapin and it's Invoke-sqlcmd. This script is using MSSQL database EMPLOYEE and table EMPLOYEE_DOMAIN. Hope it helps. Works fine for me..

Add-PSSnapin Quest.ActiveRoles.ADManagement
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

$db_server = "10.3.18.55"
$db = "EMPLOYEE"
$table = "EMPLOYEE_DOMAIN"
$username = "import"
$pwd = "Okinawa84561"

# First, clear existing table
$sql_query_del = "DELETE FROM $table"
Invoke-Sqlcmd -ServerInstance $db_server -Database $db -Username $username -Password $pwd -Query $sql_query_del

# Get users with employeeID only and write their accountname and employeeID to DB
Get-QADUser -IncludeAllProperties | ? {$_.employeeID} | select sAMAccountName, employeeID | foreach {
    $an = $_.sAMAccountName
    $eid = $_.employeeID
    Write-Host " sAMAccountName : $an       employeeID : $eid"

    $sql_query = "INSERT INTO $table (employeeID, domainName) VALUES ('$eid', '$an')"
    Invoke-Sqlcmd -ServerInstance $db_server -Database $db -Username $username -Password $pwd -Query $sql_query

}

这篇关于Powershell Get-QADUser结果到SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！