Requirement

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

Considerations

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

• Do we need all computers from AD or just enabled? Yeah! We need only enabled computers. If Admin disables / enables a computer or remove / adds a computer in AD the SQL express table should update accordingly.
• Do we need only client operating systems? Yeah! We need this is for Client OS 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 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 doesn’t match AD value.[Removed from AD but exists in SQL table]
• If new computer found insert into 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()