다음을 통해 공유

Query AD and Update SQL Express Table Using PowerShell

  • 아티클

Query AD and Update SQL Express Table Using PowerShell

Requirement

Query AD [Active Directory] computers and insert into SQL Express.

Considerations

The requirement is just a one liner. We need to consider a lot before we deliver this to production.

  • Do we need all computers from AD or just enabled? Yep! We need only enabled computers. If Admin disables/enables a computer or removes/adds a computer in AD, the SQL Express table should update accordingly.
  • Do we need only client operating systems? Yep! We need this is for reporting.

Summary

To summarize the requirement, customer needs a solution which should do the below tasks:

  • Insert if new computer found in AD.
  • Drop if a computer is removed.
  • Update properties if any change identified.

Solution

  • Query AD with Filter ON [Enabled -eq $true and OperatingSystem -eq 'Windows 7 Enterprise'].
  • Insert If exists.
  • Drop Row if SQL Express rows don't match AD value. [Removed from AD but exists in SQL table]
  • If new computer found, insert into a table.

PowerShell Code

#Establish Connection to SQL Express
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=Localhost\SQLExpress; Initial Catalog=Employee; Integrated Security=SSPI")
$conn.Open()

#Query AD and Update SQL Table
Get-ADComputer -Filter {(Enabled -eq $true) -and (OperatingSystem -eq 'Windows 7 Enterprise')} -Properties * | %{

$Name = $_.Name 
$OS = $_.OperatingSystem 
$LastLogonDate  = $_.LastLogonDate 
$PasswordLastSet = $_.PasswordLastSet
$Description = $_.Description 
$DN = $_.DistinguishedName
$Enabled = $_.Enabled

$cmd = $conn.CreateCommand()
$query = @"
IF EXISTS (Select * from ComputerInformation Where ComputerName = '$Name')
Delete from ComputerInformation where ComputerName <> '$Name'
ELSE
INSERT INTO ComputerInformation VALUES ('$Name' , '$OS' , '$LastLogonDate' , '$PasswordLastSet' , '$Description' , '$DN' , '$Enabled')
"@
$cmd.CommandText = $query
$cmd.ExecuteNonQuery()
}

#Close the connection
$conn.Close()