fwTSAewD

1# ---------------------------------------------
2# ----------------- CREATED BY-----------------
3# ------------- DANIEL FRIIS PARISI -----------
4# ----------------OCTOBER 2018-----------------
5
6Add-Type -AssemblyName System.web
7 
8#---SQL VARIABLES---#
9$SQL_SERVER = “DFP-PC”
10$DB_USER = “DFP”
11$DB_PWD = “Velkommen1”
12$DB_NAME = “DFPDB”
13$connectionString = “Server=$SQL_SERVER;uid=$DB_USER; pwd=$DB_PWD;Database=$DB_NAME;Integrated Security=False;”
14
15
16#---CONNECT TO SQL SERVER---#
17$connection = New-Object System.Data.SqlClient.SqlConnection
18$connection.ConnectionString = $connectionString
19$connection.Open()
20
21
22#---QUERY ALL USER DATA---#
23try{
24$query = “SELECT * FROM Table_User_Accounts”
25$command = $connection.CreateCommand()
26$command.CommandText = $query
27$result = $command.ExecuteReader()
28
29
30#---PUT ALL DATA FROM SQL QUERY INTO NEW TABLE---#
31$table = new-object “System.Data.DataTable”
32$table.Load($result)
33
34#---SHOW RESULTS---#
35$table | Out-default 
36
37}catch{}
38
39# ------------- GET SQL DATA END -------------
40
41
42
43# ------------- FROM TABLE ---> ACTIVE DIRECTORY BEGINNING -------------
44
45
46ForEach($row in $table.rows)  
47
48{
49#---TABLE DATA TO VARIABLE---#
50    try{
51    $Username =  $row[“Username”]
52    $First_name = $row[“First_name”]
53    $Last_name = $row[“Last_name”]
54    $Department = $row[“Department”]
55    $Termination_date = $row[“Termination_date”]
56    $Hard_delete_date = $row[“Hard_delete_date”]
57    $MaxDateTreshhold = 3
58    $CheckDateTreshhold = (get-date).adddays(-$MaxDateTreshhold)
59
60    }catch{}
61
62    try{
63     #---CHECKS IF USER EXISTS---#
64    $CheckUser = Get-ADUser -LDAPFilter "(sAMAccountName=$Username)"
65    }catch{}
66
67    #---ONLY CREATE USER IF IT DOESN'T EXIST IN AD AND DOESN'T HAVE A HARD DELETION DATE IN SQL-DB---#
68    If ($CheckUser -eq $Null -and  ([string]::IsNullOrEmpty($Hard_delete_date)))
69    {
70
71    #---CREATE RANDOMIZED PASSWORD AND UPLOAD TO SQL-DB---#
72
73    try{
74    $Password = "Velkommen_" + [system.web.security.membership]::GeneratePassword(5,0)
75    $query = "UPDATE Table_User_Accounts SET Password = '$Password' WHERE username = '$Username'"
76    $command = $connection.CreateCommand()
77    $command.CommandText = $query
78    $result = $command.ExecuteReader()
79
80    #---CREATE AD USER WITH PROPERTIES---#
81    New-ADUser `
82        -Name "$Username"`
83        -GivenName "$First_name"`
84        -Surname "$Last_name"`
85        -Department "$Department"`
86        -Enabled $true `
87        -AccountPassword (ConvertTo-SecureString "$Password" -AsPlainText -force)`
88        -Path "OU=$Department,OU=Users,OU=Parisi.dk,DC=Parisi,DC=dk" 
89       
90    
91    }catch{}
92
93
94      #---CREATE AD USER WITH PROPERTIES---#
95
96
97      try{
98      #---KILL SQL CONNECTION---#
99        $connection.Close()
100        }catch{}
101        
102
103    }
104
105
106    #---IF LAST_MODIFIED WAS LESS THAN 3 DAYS AGO, UPDATE AD WITH SQL PROPERTIES ---#                   
107    Elseif($Last_Modified -ge $CheckDateTreshhold )
108    {
109
110        #---DISABLE USER IN AD IF TERMINATION DAY IS PAST TODAY ---#  
111        if (-not ([string]::IsNullOrEmpty($Termination_date)))
112
113            {
114    
115                If($Termination_date -gt (get-date))
116                {
117                $User_Disabled_boolean = 1
118                }
119                else
120                {
121                $User_Disabled_boolean = 0
122                }     
123
124            }
125
126        Else
127
128            {
129            $User_Disabled_boolean = 1
130            }
131
132
133            
134    #---CHANGE PROPERTIES FOR THE USER---#
135
136    try{
137      Set-ADUser `
138        -Identity "$Username" `
139        -GivenName "$First_name" `
140        -Surname "$Last_name" `
141        -Department "$Department" `
142        -Enabled  $User_Disabled_boolean 
143      
144  #---MOVE TO CORRECT ORGANIZATIONAL UNIT---#
145      Get-ADUser -Identity $Username | %{move-ADObject $_.DistinguishedName -targetpath "OU=$Department,OU=Users,OU=Parisi.dk,DC=parisi,DC=dk"}
146    
147           
148  #---CHECK HARD DELETE DATE, DELETE USER IF PAST DATE---#
149           if (-not ([string]::IsNullOrEmpty($Hard_delete_date)) -and ($Hard_delete_date -lt (get-date)))
150                  
151             {      
152              Remove-ADUser -Identity $Username -Confirm:$false                  
153             }
154
155
156       }catch{}
157 }             
158 }