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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
#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() |