Windows PowerShell

Query AD and Update SQL Express Table Using PowerShell

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *