############################################################################################################# ## # Recherche Active Directory et Collecte d'informations sur les Users ## ############################################################################################################# ### Set-ExecutionPolicy RemoteSigned cls clear # Déclaration des variables $Nom_Serveur = "sqlinfra.croix-rouge.prv\sqlinfra" $Base = "testPS" $Table = "TableADWrite" ###################################################################################### ## # Changement des Assembly ## ###################################################################################### [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null ###################################################################################### ## # Fonction Recherche si erreur il y a ## ###################################################################################### function SQL_Error { Trap { $err = $_.Exception while ( $err.InnerException ) { $erreur = $err.InnerException " | " + $erreur.Message >> $logscript }; continue } $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Nom_Serveur $d = $s.Databases[$Base] $d.CheckTables('Fast') } ###################################################################################### ## # 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 ## ###################################################################################### "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;" # On prépare l'accès à la Table SQL $cmd = New-object system.data.sqlclient.SqlCommand $cmd.connection = $conn # Requête SQL pour vider la table si elle existe, si pas, on la créée $Requete = " IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'$Table') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) TRUNCATE TABLE $Table; CREATE TABLE $Table ( DisplayName char(20), UserPrincipalName char(20), GivenName char(20), SingleName char(20), MiddleName char(20), Mobile char(20), HomePhone char(20), IpPhone char(20), TelephoneNumber char(20), physicalDeliveryOfficeName char(20), Mail char(20), ExtensionAttribute1 char(20), ExtensionAttribute2 char(20), ExtensionAttribute3 char(20), ExtensionAttribute4 char(20), ExtensionAttribute5 char(20), ExtensionAttribute6 char(20), ExtensionAttribute7 char(20), ExtensionAttribute8 char(20), ExtensionAttribute9 char(20), ExtensionAttribute10 char(20), ExtensionAttribute11 char(20), ExtensionAttribute12 char(20), ExtensionAttribute13 char(20), ExtensionAttribute14 char(20), ExtensionAttribute15 char(20), AccountExpires char(20), LastLogon date, LastLogoff date, EmployeeID char(13), EmployeeType char(50), UserAccountControl char(20) )" # On envoie la requête à SQL-Srv $sqlCommand = New-Object System.Data.SqlClient.sqlCommand $Requete,$Conn # 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 } $sqlCommand.ExecuteNonQuery() | Out-Null SQL_Error # 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 $Requete = "INSERT INTO $table 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 );" $sqlCommand = New-Object System.Data.SqlClient.sqlCommand $Requete,$Conn $sqlCommand.ExecuteNonQuery() | Out-Null SQL_Error } } } # On libére la connexion sur le serveur SQL. $Conn.Close()