Export liste users AD via SQL sur Excel

Version imprimable

Voir 40 message(s) de cette discussion en une page

Bonjour,

Minuit passé, je n'arrive toujours pas, je poste toutes les manœuvres que j'ai tentées les plus ou moins farfelues les unes des autres . . .

Faut dire que je ne suis pas très très doué en SQL . . .

J'ai essayé de plancher sur un script Visual basic pour savoir une partie des commandes SQL, et en voyant ce script ça m'a donné quelques idées qui n'ont pas marché . . .

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
    * ' ------ SCRIPT d'export d'utilisateurs depuis une OU ------
    * ' ------ Le domaine AD est a jouter en fixe dans le ------
    * ' ------ String StrDomainDN pour des raisons d'utilisations courantes ------
    *
    * dim fso, MyFile, reptemp, filetext
    *
    *
    * Stroucible=inputbox("renseigner le nom de l'ou cible : ")
    *
    * ' Attention à modifier le nom LDAP du domaine
    * strDomainDN ="ou=" & stroucible & ",dc=VotreNomdeDomaine,dc=SonExtention"
    *
    * ' Attention le répertoire c:\temp doit exister
    * reptemp="c:\temp\"
    *
    * Filetext=Inputbox("fichier temporaire de l'OU cible : ")
    * Set fso = CreateObject("Scripting.FileSystemObject")
    *
    * ' création d'un fichier txt pour la première partie du script, soit le nom des utilisateurs
    * set MyFile = fso.CreateTextFile(reptemp + filetext + ".txt")
    *
    * ' Ici un filtre sur les utilisateurs et je récupére leur Distinguishedname
    * strBase = "<LDAP://" & strDomainDN & ">;"
    * strFilter = "(&(objectclass=user)(objectcategory=person));"
    * strAttrs = "distinguishedname;"
    * strScope = "subtree"
    *
    * set objConn = CreateObject("ADODB.Connection")
    * objConn.Provider = "ADsDSOObject"
    * objConn.Open "Active Directory Provider"
    *
    * ' Ici lancement de la requêtes et écriture dans le fichier txt dans le c:\temp
    * set objRS = objConn.Execute(strBase & strFilter & strAttrs & strScope)
    * objRS.MoveFirst
    * while Not objRS.EOF
    * MyFile.WriteLine (objRS.Fields(0).Value)
    * objRS.MoveNext
    * wend
    * MyFile.close
    *
    *
    * ' Maintenant avec le fichier txt je récupère les informations utilisateurs par utilisateurs
    * on error resume next
    * Dim objConnection, objRecords, objExcel, strQuery, i, objSpread, intRow
    *
    * 'Attention le fichier C:\sources.xls doit exister
    * strSheet = "c:\Source.xls"
    *
    * Set objExcel = CreateObject("Excel.Application")
    * Set objSpread = objExcel.Workbooks.Open(strSheet)
    * Set objFSO = CreateObject("Scripting.FileSystemObject")
    * Set UserListe = objFSO.OpenTextFile(reptemp + Filetext + ".txt")
    *
    * 'Renseigner le numéro de la première ligne Excel ou vous souhaité écrire les inforamations
    * i = 2
    *
    * ' liste des attributs à récupérer
    * Do Until UserListe.AtEndofStream
    * UserLDAP = UserListe.Readline
    * Set objUser = GetObject("LDAP://" & UserLDAP & "")
    * CNStr = left(UserLDAP, Instr (UserLDAP, ",") -1)
    * OuStr = Right(UserLDAP, len(UserLDAP) - Instr (UserLDAP, ","))
    * objExcel.ActiveSheet.Range("A" & i).Value = CNStr
    * objExcel.ActiveSheet.Range("B" & i).Value = OuStr
    * objExcel.ActiveSheet.Range("C" & i).Value = objUser.givenName
    * objExcel.ActiveSheet.Range("D" & i).Value = objUser.initials
    * objExcel.ActiveSheet.Range("E" & i).Value = objUser.sn
    * objExcel.ActiveSheet.Range("F" & i).Value = objUser.displayName
    * objExcel.ActiveSheet.Range("G" & i).Value = objUser.userPrincipalName
    * objExcel.ActiveSheet.Range("H" & i).Value = objUser.SamaccountName
    * objExcel.ActiveSheet.Range("I" & i).Value = objUser.mail
    * objExcel.ActiveSheet.Range("J" & i).Value = objUser.physicalDeliveryOfficeName
    * objExcel.ActiveSheet.Range("K" & i).Value = objUser.telephoneNumber
    * objExcel.ActiveSheet.Range("L" & i).Value = objUser.Description
    *
    * i = i + 1
    * loop
    *
    *
    *
    * 'Sauvegarde du fichier Excel
    * objExcel.ActiveWorkbook.SaveAs(reptemp + Filetext + ".xls")
    * objExcel.ActiveWorkbook.Close
    * objExcel.Workbooks.Close
    *
    * msgbox "fin de récupération des utilisateurs. Le fichiers excel est dans " + reptemp + Filetext + ".xls"
    * objExcel.Quit

Voici mes tentatives:

Code:

1
2
3
4
5
6
7
8
SELECT 
     *
FROM 
  OPENQUERY(SQLAD, 
   'SELECT title, displayName, sAMAccountName, 
   givenName, telephoneNumber, facsimileTelephoneNumber, sn 
   FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain,DC=Users'' 
   where objectClass = ''User''')

Qui me paraissait d'une logique simpliste étant donné que SQLAD c'est AD => SQL server . . . elle m'a renvoyé cette banale erreur . . .

Code:

1
2
3
4
5
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT title, displayName, sAMAccountName, 
   givenName, telephoneNumber, facsimileTelephoneNumber, sn 
   FROM 'LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain,DC=Users' 
   where objectClass = 'User'" for execution against OLE DB provider "ADSDSOObject" for linked server "SQLAD".

Cependant lorsque j'exécute la dernière commande sur laquelle nous avons bataillé, elle fonctionne mais . . .

Code:

1
2
3
4
5
6
CREATE VIEW dbo.vw_AD_USER_INFO
AS
 
SELECT * FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''')
 
GO

Elle renvoie donc qu'il existe déjà un objet nommé mais je ne sais pas comment le mettre dans le fichier Excel . . .

Code:

1
2
Msg 2714, Level 16, State 3, Procedure vw_AD_USER_INFO, Line 4
There is already an object named 'vw_AD_USER_INFO' in the database.

J'avoue je suis perdu avec les codes . . . :bug:

Edit:

Les vues sont visibles dans 2 bases de données, si je veux les extraire pour les mettre dans un fichier Excel, je devrais utiliser OPENROWSET?

http://sphotos.ak.fbcdn.net/hphotos-..._3480583_n.jpg

Celle qui m'intéresse est la bdd Master, elle contient toutes les données telles que facsimile, givename . . .

Ce n'est pas hyper clair là...

Si cette requête ne fonctionne pas, ne cherche pas plus loin, il faut que cette requête retourne qqch.

Code:

SELECT * FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''')

Normalement tu devrais récupérer la liste des utlisateurs dans l'AD en retour d'exécution.

Cette commande-ci ne fait que créer une vue, un objet qui te permet de masquer l'utilisation de la requête :
Code:

1 2 3 4 5 6 CREATE VIEW dbo.vw_AD_USER_INFO AS SELECT * FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''') GO
Donc si tu as tjrs l'erreur

Code:

Msg 7321, Level 16, State 2, Line 1

Il faut d'abord résoudre ceci. Est-ce que tu as changé l'option de sécu du serveur lié, avec quelle valeur ? As-tu essayé de placer ton utilisateur et mot de pase ?

29/04/2010, 10h51
Feldunost

Cette requête retourne toutes les valeurs que je voulais mais il prend aussi en compte les utilisateurs qui ont été effacés . . . j'arrive à 6800 utilisateurs . . . au lieu de quelques 800 . . .

Code:

SELECT * FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''')

Ca fonctionne mais tout de même . . .

Edit: Même pas il prend tous les utilisateurs du domaine même ceux qui ne sont pas visibles . . . Bon au moins ça fonctionne mais faut que je pense à nettoyer AD . . .

Faut que je trouve pour exporter ça vers Excel . . .

Mais dans ce cqs-là il faut que tu affines ta recherche. Je ne connais ton arbo AD mais si tu as une OU spécifique, tu peux la rajouter pour filtrer.

Code:

LDAP://marshall-ff263d/OU=ServiceInformatique,DC=CfsaAftec,DC=MysqlDomain

ou par rapport à ce que je vois sur ta capture ecran

Code:

LDAP://marshall-ff263d/CN=Users,DC=CfsaAftec,DC=MysqlDomain

Une fois que tu as la bonne requête et donc le résultat que tu recherches, la partie Excel est simple :
Tu n'a qu'à créer un fichier vide, par ex c:\listusers.xls puis à exécuter la requête, du style :
Code:

1 2 3 INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\listusers.xls;', 'SELECT * FROM [Sheet1$]') SELECT * FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''')
Le lien que tu as mentionné montre tout , pas à pas, c'est très simple. La difficulté réside plus dans la localisation des utilisateurs dans ton LDAP.

29/04/2010, 13h15
Feldunost

En gros, on a énormément de possibilités avec SQL, vu que l'on peut retenir les informations qu'on veut, tout prendre ou bien faire les listings différents, la seule limite c'est donc le lexique SQL qui est disponible?

Si je veux insérer des données d'utilisateurs en reverse (XLS => SQL => AD) est ce possible cette méthode? Si oui, alors on peut vraiment tout faire, et c'est excellent :ccool:

Le vues ne servent donc pas à grand chose dans l'export de données vers Excel? Autant suprimmer . . . mais les données que l'on retire par la précédente commande qui fonctionne, elles sont stockées ou??

Bonsoir,

Alors quelques manipulations ont été faites:
Code:

1 2 3 INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Documents and Settings\Administrateur\Bureau\AD.xlsx;', 'SELECT * FROM [Sheet1$]') SELECT * FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''')
A retourné cette erreur suivante:
Code:

1 2 3 4 Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Tandis que le code suivant:

Code:

SELECT * FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''')

Retourne bien la liste des utilisateurs que l'on veut . . .

Je pense que il y a une confusion au niveau du OPENROWSET?

30/04/2010, 18h31
kagemaru

est-ce que tu as fait le test avec un fichier à la racine de C: et dans un fichier xls "classique" ?

Code:

C:\fichier.xls

Je pense que ca vient du fait que j'ai la version 2007 plus! de la suite bureautique, et le fait de mettre Excel 8.0 ne va pas avec cette version?

J'ai changé comme tu m'as dit ça ne change rien:

Code:

1
2
3
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ADTest.xls;',
'SELECT * FROM [Sheet1$]')
SELECT * FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''')

Code:

1
2
3
4
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Cette fois j'ai changé le fichier en sauvegardant en .xls donc Excel 8.0 n'est pas le problème.
Code:

1 2 Msg 7357, Level 16, State 2, Line 1 Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
Les droits ont bien été paramétrés et les colonnes . . . je ne vois pas quoi mettre à part les colonnes de AD qui sont: sn, facsimile etc . . .

01/05/2010, 19h26
kagemaru

Si ton excel est localisé en français, il peut s'agir de Feuil1$ et non de Sheet1$, .... mes 2 eurocents. En tout cas ta requête AD fonctionne donc il faut continuer à regarder côté import dans excel ....

En effet il s'agissait bien de Feuil1:

Code:

1
2
3
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ADTest.xls;',
'SELECT * FROM [Feuil1$]')
SELECT * FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''')

Mais il y a une erreur un peu bizarre . . .

Code:

1
2
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

04/05/2010, 11h01
kagemaru

essaie de réduire le nb de colonnes dans un 1er temps pour voir s'il ne s'agit d'une col vide par exemple. Ne mets que la colonne SN par ex.
04/05/2010, 22h30
Feldunost

Ok, en fait la situation n'est pas si excellente que cela . . .

Il faut impérativement que la colonne que l'on souhaite exporter n'aie pas la valeur "NULL" . . . sinon cela retourne l'erreur . . .

Et donc par conséquent je ne peux que exporter la colonne sAMAccountName.

J'aimerais savoir 2 choses:
Est-il possible de contourner cette erreur dûe à une seule ligne de NULL ?
Est-il possible de permettre un import ? :ccool:

Eh bien ....... il y a une fonction qui te permet de remplacer les valeurs NULL :
ISNULL

Code:

1
2
3
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ADTest.xls;',
'SELECT * FROM [Feuil1$]')
SELECT isnull(title,'No title'),isnull(displayName,'No displayName'),isnull(sAMAccountName,'No sAMAccountName'),isnull(givenName,'No givenName'),isnull(telephoneNumber,'No telephoneNumber'),isnull(telephoneNumber,'no telephoneNumber'),isnull(facsimileTelephoneNumber,'no facsimileTelephoneNumber'),isnull(sn, 'no sn') FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''')

Il y avait une colonne en double, mais même en effaçant la double colonne, ça ne fonctionne pas . . .

Code:

1
2
3
4
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ADTest.xls;',
'SELECT * FROM [Feuil1$]')
SELECT isnull(title,'No title'),isnull(displayName,'No displayName'),isnull(sAMAccountName,'No sAMAccountName'),isnull(givenName,'No givenName'),isnull(telephoneNumber,'No telephoneNumber'),isnull(facsimileTelephoneNumber,'No facsimileTelephoneNumber'),isnull(sn, 'No sn') 
FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''')

Code:

1
2
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

La fonction ne semble pas fonctionner . . . :calim2:

ok, ce n'est pas gagné avec la méthode excel. Dans ce cas-là, faisons efficace (j'aurais du y songer bien plutôt quqnd je vois le nb de posts sur ce fil de discussion... ;-)

1 - Dans ta base de test, tu créés finalement une vue :
Code:

1 2 3 create view ad_view as SELECT isnull(title,'No title') as 'Title',isnull(displayName,'No displayName') as 'DisplayName',isnull(sAMAccountName,'No sAMAccountName') as 'sAMAccountName',isnull(givenName,'No givenName') as 'givenName',isnull(telephoneNumber,'No telephoneNumber') as 'TelNum',isnull(facsimileTelephoneNumber,'No facsimileTelephoneNumber') as 'Fax',isnull(sn, 'No sn') as 'SN' FROM OpenQuery(SQLAD, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://marshall-ff263d/DC=CfsaAftec,DC=MysqlDomain'' where objectClass = ''User''')
2 - tu lances une fenêtre de commande en ligne (CMD) et tu extraies les donnès au format CSV :

Code:

C:\> bcp mabase..ad_view out c:\listeusers2.xls -c -T -S CfsaAftec

Tu remplaces mabase par le nom de la base dans laquelle la vue a été créée.

Tu peux alors ouvrir le fichier avec Excel.

Pour la vue, cela fonctionne impeccablement cependant la commande à exécuter via cmd ne marche pas comme on voudrait . . .

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
C:\>bcp master ad_view out c:\ADTest.xls -c -T -S CfsaAftec
 
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]

Je ne comprends pas trop le fait de mettre CfsaAftec, mais même en remplaçant par le vrai nom du serveur qui est MARSHALL-FF263D, ça ne fonctionne pas plus que ça . . .

06/05/2010, 22h51
kagemaru

Il faut aussi regarder la doc parfois ou même sur ce fourm, il y a des exemples d'utilisation de cette commande
http://msdn.microsoft.com/fr-fr/library/ms162802.aspx

Comme tu peux voir, il y a 2 points entre mabase et ad_view. Le format est normalement base.schema.table. J'ai présumé qu'il s'agissait du schéma par défaut (dbo), donc on utilise souvent cette notation avec pointpoint.

Code:

bcp mabase..ad_view out c:\listeusers2.xls -c -T -S MARSHALL-FF263D

Pour le nom du serveur, en effet tu as raison, j'ai confondu, donc tu devrais avoir au final

Code:

bcp mabase..ad_view out c:\listeusers2.xls -c -T -S MARSHALL-FF263D

En fait je n'avais pas vu le dbo.ad_view

Il y a bien dbo.ad_view dans la System Databases => master => views => dbo.ad_view

Voilà ce que cela me retourne:
Code:

1 2 3 4 5 6 7 8 9 10 11 C:\>bcp master..ad_view out c:\listeusers2.xls -c -T -S MARSHALL-FF263D SQLState = 08001, NativeError = 2 Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a con nection to SQL Server [2]. SQLState = 08001, NativeError = 2 Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. SQLState = S1T00, NativeError = 0 Error = [Microsoft][SQL Native Client]Login timeout expired
EDIT:

Eureka . . .
Code:

1 2 3 4 5 6 7 8 C:\>bcp master..ad_view out c:\listeusers2.xls -c -T -S MARSHALL-FF263D\SQLEXPRE SS Starting copy... 5 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1594 Average : (3.14 rows per sec.)
De plus cette fois-ci la fonction isnull que je ne connaissais pas, ben, elle marche parfaitement, j'ai compris beaucoup de choses en tâtonnant avec toi même si c'est lent ça me permet de voir beaucoup de trucs qui me serviront plus tard :ccool:

Je suis en train d'essayer de faire la même chose mais en inverse, je t'en dis plus dès demain soir comment je m'en sortirai => :aie:

07/05/2010, 15h57
Feldunost

Bonjour,

Voilà le topo: J'arrive à faire des requêtes sympathiques me permettant d'avoir la liste des utilisateurs et des informations personnelles ce qui est très utile.

Cependant j'ai cherché en vain 1h avec très peu de tests, personne n'explique en gros même si google est mon ami comment faire ce chemin inverse.

J'ai lu que une personne n'arrivait pas à importer correctement un utilisateur à cause des politiques de sécurité de AD et de ce fait que les comptes importés étaient désactivés.

En gros c'est pire de trouver une solution d'importation en sql mais d'après les options de la commande d'export que tu m'as donnée, il est possible de faire de même en import: donc pour cette partie, il n'y a pas de souci, le problème réside donc du fait que ayant la vue SQL, comment importer cette vue dans AD . . .

C'est plutôt un sujet épineux . . . 8O

Voir 40 message(s) de cette discussion en une page