#############################################################################################################
##
# Recherche Active Directory et Collecte d'informations sur les Users 
##
#############################################################################################################


### Set-ExecutionPolicy RemoteSigned
cls
clear

######################################################################################
##
#  Lecture des erreurs SQL éventuelles
##
######################################################################################

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($Nom_Serveur)
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, $Base)
$d = $srv.ReadErrorLog()
$databaseCreateEventSet = new-object Microsoft.SqlServer.Management.Smo.DatabaseEventSet
$databaseCreateEventSet.CreateTable = $TRUE
$serverCreateEventHandler = new-object Microsoft.SqlServer.Management.Smo.ServerEventHandler(AddressOf MyCreateEventHandler) 
$db.Events.SubscribeToEvents()
$databaseDropEventSet = new-object Microsoft.SqlServer.Management.Smo.DatabaseEventSet
$databaseDropEventSet.DropTable = $TRUE

$serverDropEventHandler = new-object Microsoft.SqlServer.Management.Smo.ServerEventHandler(AddressOf MyDropEventHandler)
$db.Events.SubscribeToEvents()
$db.Events.StartEvents()


######################################################################################
##
#  Passage des paramètres de recherche au Script
##
######################################################################################

# Création du fichier de Log pour Debug
New-Item c:\temp\UsersLogScript.log -type file -force 
$logscript = "c:\temp\UsersLogScript.log" 

# Ouverture et récupération des différentes adresses de l'AD à traiter
$LDAPRechList = get-content "C:\Temp\LDAPRechList.txt" 

######################################################################################
##
#  Préparation de la Base de données SQL
##
######################################################################################
$Nom_Serveur = "sqlinfra.croix-rouge.prv\sqlinfra"
$Base = "testPS"
$TableIN = "TestAD"

"Début de transaction : "+[System.DateTime]::Now  >> $logscript

# Connexion sur Sql Server et à la Base de données
$Conn = new-object system.data.sqlclient.sqlconnection
# Connexion avec l'authentification Windows
$Conn.connectionstring = "Server="+$Nom_Serveur+";database="+$Base+";trusted_connection=yes;"
# Ouvre la connexion
$Conn.Open()
# Test de l'état de la connexion 
    if ($Conn.get_state() -eq 'Open')   
        { 
        [String]("La connexion au serveur $NOM_Serveur réussie !") >> $logscript
        }
    else
        {
        [String]("La connexion au serveur $NOM_Serveur a échouée !") >> $logscript
        } 

# On prépare l'accès à la Table SQL
$cmdIN = New-object system.data.sqlclient.SqlCommand
$cmdIN.connection = $conn
$DataAdapterIN = new-object System.Data.SqlClient.SQLDataAdapter($cmdIN)
$DataSetIN = new-object System.Data.DataSet
# On vide la table si elle existe, si pas, on la crée
$RequeteIN = "CREATE TABLE $tableIN IF NOT EXISTS; TRUNCATE TABLE $TableIN;"
$cmdIN.commandtext = $RequeteIN
$cmdIN.Executenonquery()  | out-null

$DataAdapterIN.Fill($DataSetIN)
$DataSetIN.Tables[0].Rows

# Initialisation de certaines variables
[long]$AccountExpires = $null
[datetime]$LastLogon = $null
[datetime]$LastLogoff = $null
[int]$EmployeeID = $null
[long]$UserAccountControl = $null

######################################################################################
##
#  Recherche à proprement parlée dans l'Active Directory
##
######################################################################################

#Pour chaque adresse LDAP se trouvant dans le fichier LDAPRechList.txt
foreach ($LDAP in $LDAPRechList)  
        {
            # Extraction des données dont on a besoin pour chaque personnes recherchées
            $objDomain = New-Object System.DirectoryServices.DirectoryEntry($LDAP)
            $ldapQuery = "(&(objectCategory=user))"
            $objSearcher = new-object system.directoryservices.directorysearcher -argumentlist $objDomain,$ldapQuery
            $complist = $objSearcher.FindAll()
            foreach ($u in $complist)  
                    {
                        $detailUsers = New-Object DirectoryServices.DirectoryEntry($u.path)
                        foreach ($p in $detailUsers) {
# On enregistre les informations dont on a besoin pour traitement
                                    $DisplayName = $detailUsers.displayName.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $UserPrincipalName = $detailUsers.userPrincipalName.Value  | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $GivenName = $detailUsers.givenName.Value  | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $SingleName = $detailUsers.sn.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $MiddleName = $detailUsers.MiddleName.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $Mobile = $detailUsers.Mobile.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $HomePhone = $detailUsers.HomePhone.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $IpPhone = $detailUsers.IpPhone.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $TelephoneNumber = $detailUsers.TelephoneNumber.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $physicalDeliveryOfficeName = $detailUsers.physicalDeliveryOfficeName.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $Mail = $detailUsers.mail.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute1 = $detailUsers.ExtensionAttribute1.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute2 = $detailUsers.ExtensionAttribute2.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute3 = $detailUsers.ExtensionAttribute3.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute4 = $detailUsers.ExtensionAttribute4.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute5 = $detailUsers.ExtensionAttribute5.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute6 = $detailUsers.ExtensionAttribute6.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute7 = $detailUsers.ExtensionAttribute7.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute8 = $detailUsers.ExtensionAttribute8.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute9 = $detailUsers.ExtensionAttribute9.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute10 = $detailUsers.ExtensionAttribute10.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute11 = $detailUsers.ExtensionAttribute11.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute12 = $detailUsers.ExtensionAttribute12.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute13 = $detailUsers.ExtensionAttribute13.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute14 = $detailUsers.ExtensionAttribute14.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $ExtensionAttribute15 = $detailUsers.ExtensionAttribute15.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $AccountExpires = $detailUsers.accountExpires.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $LastLogon = $detailUsers.lastLogon.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $LastLogoff = $detailUsers.lastLogoff.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $EmployeeID = $detailUsers.EmployeeID.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $EmployeeType = $detailUsers.EmployeeType.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
                                    $UserAccountControl = $detailUsers.userAccountControl.Value | out-File 'c:\Temp\Users.txt' -append -encoding utf8
# Injection des données récoltées dans la Base SQL
                                    $RequeteIN = "INSERT INTO $tableIN 
                                    DisplayName, UserPrincipalName, GivenName, SingleName, MiddleName,
                                    Mobile, HomePhone, IpPhone, TelephoneNumber, physicalDeliveryOfficeName,
                                    Mail, ExtensionAttribute1, ExtensionAttribute2,
                                    ExtensionAttribute3, ExtensionAttribute4, ExtensionAttribute5, ExtensionAttribute6,
                                    ExtensionAttribute7, ExtensionAttribute8, ExtensionAttribute9, ExtensionAttribute10,
                                    ExtensionAttribute11, ExtensionAttribute12, ExtensionAttribute13, ExtensionAttribute14, 
                                    ExtensionAttribute15, AccountExpires, LastLogon, LastLogoff, EmployeeID,
                                    EmployeeType, UserAccountControl
                                    Values (
                                    $DisplayName, $UserPrincipalName, $GivenName, $SingleName, $MiddleName,
                                    $Mobile, $HomePhone, $IpPhone, $TelephoneNumber, $physicalDeliveryOfficeName,
                                    $Mail, $ExtensionAttribute1, $ExtensionAttribute2,
                                    $ExtensionAttribute3, $ExtensionAttribute4, $ExtensionAttribute5, $ExtensionAttribute6,
                                    $ExtensionAttribute7, $ExtensionAttribute8, $ExtensionAttribute9, $ExtensionAttribute10,
                                    $ExtensionAttribute11, $ExtensionAttribute12, $ExtensionAttribute13, $ExtensionAttribute14, 
                                    $ExtensionAttribute15, $AccountExpires, $LastLogon, $LastLogoff, $EmployeeID,
                                    $EmployeeType, $UserAccountControl
                                    );" 

                                    $cmdIN.commandtext = $RequeteIN
                                    $cmdIN.Executenonquery() | out-null
                                    $DataAdapterIN.Fill($DataSetIN)
                                    $DataSetIN.Tables[0].Rows
                                }
                    } 
        }
            
$db.Events.StopEvents()
    foreach ($r in $db.Events)
        {
           "============================================" >> $logscript
            $db.Events >> $logscript
        }

# On libére la connexion sur le serveur SQL.
$Conn.Close()