Powershell: Sample Script

#'========================================
#'Connect to the DB and get a list of all emails with a specific domain name
#'========================================

$conn = New-Object System.Data.SqlClient.SqlConnection

$conn.ConnectionString = "Data Source=<SQL Server DB instance>;Initial Catalog=<DB Name> Integrated Security=SSPI"

 

try

{
                $conn.Open();
                $query="select email from table where email like '%.%@domain.com' and isexistingclient = 'Y'"
                $command = $conn.CreateCommand()
                $command.CommandText = $query
                $result = $command.ExecuteReader()

                $table = New-Object "System.Data.DataTable"

                $table.Load($result)

                $format = @{Expression={$_.email}; Label="Email"; width=50}
                #$table | format-table $format
}

finally

{
                if ($conn -and ($conn.state -eq 'Open'))
                {
                                $conn.Close()
                }
}

#'========================================

#'Check AD if user exists
#'======================================== 

$table | FOREACH-OBJECT{

                $emailtosearch = $_.email
                $User = Get-ADUser -properties * -LDAPFilter "(mail=$emailtosearch)"
                If (($User -eq $null ) -or ($User | Select-Object -Property enabled) -like "*False*")
                {
                                #'===========================
                                #'insert into the database if user is disabled or it does not exist in AD
                                #'===========================
                                try{
                                                $conn.Open();
                                                $email = $emailtosearch -replace "'", "''"
                                                $query="insert into DBName..Table1(EmailAddress) values('" + $email + "')"
                                                $command = $conn.CreateCommand()

                                                $command.CommandText = $query

                                                $command.ExecuteNonQuery()

                                }

                                finally
                                {
                                                if ($conn -and ($conn.state -eq 'Open'))

                                                {

                                                $conn.close()

                                                }
                                }
                }
                #"$emailtosearch  $status"
}