AD Maintenance by Daniel Parisi

# ---------------------------------------------
# ----------------- CREATED BY-----------------
# ------------- DANIEL FRIIS PARISI -----------
# ----------------OCTOBER 2018-----------------

Add-Type -AssemblyName System.web

#---SQL VARIABLES---#
$SQL_SERVER = “DFP-PC”
$DB_USER = “DFP”
$DB_PWD = “Velkommen1”
$DB_NAME = “DFPDB”
$connectionString = “Server=$SQL_SERVER;uid=$DB_USER; pwd=$DB_PWD;Database=$DB_NAME;Integrated Security=False;”


#---CONNECT TO SQL SERVER---#
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()


#---QUERY ALL USER DATA---#
try{
$query = “SELECT * FROM Table_User_Accounts”
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()


#---PUT ALL DATA FROM SQL QUERY INTO NEW TABLE---#
$table = new-object “System.Data.DataTable”
$table.Load($result)

#---SHOW RESULTS---#
$table | Out-default

}catch{}

# ------------- GET SQL DATA END -------------



# ------------- FROM TABLE ---> ACTIVE DIRECTORY BEGINNING -------------


ForEach($row in $table.rows)

{
#---TABLE DATA TO VARIABLE---#
    try{
    $Username =  $row[“Username”]
    $First_name = $row[“First_name”]
    $Last_name = $row[“Last_name”]
    $Department = $row[“Department”]
    $Termination_date = $row[“Termination_date”]
    $Hard_delete_date = $row[“Hard_delete_date”]
    $MaxDateTreshhold = 3
    $CheckDateTreshhold = (get-date).adddays(-$MaxDateTreshhold)

    }catch{}

    try{
     #---CHECKS IF USER EXISTS---#
    $CheckUser = Get-ADUser -LDAPFilter "(sAMAccountName=$Username)"
    }catch{}

    #---ONLY CREATE USER IF IT DOESN'T EXIST IN AD AND DOESN'T HAVE A HARD DELETION DATE IN SQL-DB---#
    If ($CheckUser -eq $Null -and  ([string]::IsNullOrEmpty($Hard_delete_date)))
    {

    #---CREATE RANDOMIZED PASSWORD AND UPLOAD TO SQL-DB---#

    try{
    $Password = "Velkommen_" + [system.web.security.membership]::GeneratePassword(5,0)
    $query = "UPDATE Table_User_Accounts SET Password = '$Password' WHERE username = '$Username'"
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $result = $command.ExecuteReader()

    #---CREATE AD USER WITH PROPERTIES---#
    New-ADUser `
        -Name "$Username"`
        -GivenName "$First_name"`
        -Surname "$Last_name"`
        -Department "$Department"`
        -Enabled $true `
        -AccountPassword (ConvertTo-SecureString "$Password" -AsPlainText -force)`
        -Path "OU=$Department,OU=Users,OU=Parisi.dk,DC=Parisi,DC=dk"


    }catch{}


      #---CREATE AD USER WITH PROPERTIES---#


      try{
      #---KILL SQL CONNECTION---#
        $connection.Close()
        }catch{}


    }


    #---IF LAST_MODIFIED WAS LESS THAN 3 DAYS AGO, UPDATE AD WITH SQL PROPERTIES ---#
    Elseif($Last_Modified -ge $CheckDateTreshhold )
    {

        #---DISABLE USER IN AD IF TERMINATION DAY IS PAST TODAY ---#
        if (-not ([string]::IsNullOrEmpty($Termination_date)))

            {

                If($Termination_date -gt (get-date))
                {
                $User_Disabled_boolean = 1
                }
                else
                {
                $User_Disabled_boolean = 0
                }

            }

        Else

            {
            $User_Disabled_boolean = 1
            }



    #---CHANGE PROPERTIES FOR THE USER---#

    try{
      Set-ADUser `
        -Identity "$Username" `
        -GivenName "$First_name" `
        -Surname "$Last_name" `
        -Department "$Department" `
        -Enabled  $User_Disabled_boolean

  #---MOVE TO CORRECT ORGANIZATIONAL UNIT---#
      Get-ADUser -Identity $Username | %{move-ADObject $_.DistinguishedName -targetpath "OU=$Department,OU=Users,OU=Parisi.dk,DC=parisi,DC=dk"}


  #---CHECK HARD DELETE DATE, DELETE USER IF PAST DATE---#
           if (-not ([string]::IsNullOrEmpty($Hard_delete_date)) -and ($Hard_delete_date -lt (get-date)))

             {
              Remove-ADUser -Identity $Username -Confirm:$false
             }


       }catch{}
 }
 }