AD mit MS SQL auslesen und verwenden

[DaPete 1]

Guten Tag,

ich bin neu und komme direkt mit einem Problem um die ecke :)

Zuerst kurz zu mir.

Ich bin ein 24 Jähriger Familienvater sowie gelernter Fachinformatiker Anwendungsentwicklung aus dem grünen Herzen deutschlands. Mein Ausbildungsbetrieb hat mich damals auf Oracle SQL spezialisiert.

Natürlich bin ich jetzt in einem Unternehmen gelandet, welches keine Oracle Datenbanken zur verfügung stellt.

Somit habe ich gerade ein wenig mit MS SQL zu kämpfen und komme nun zu meinem Problem.

Im Intranet gibt es eine Mitarbeiterliste mit Namen, email und Telefonnummern.

Bis jetzt wurde es vom Sysadmin immer händisch gepflegt (Doppelter-Dreifacher aufwand).

Mein Vorschlag war, das einmal am Tag ein Dienst startet, der die Tabelle löscht, auf die das Intranet zugreift und mit Daten aus dem AD neu befüllt (Monatliche sind mehrere zu und Abgänge im Betrieb).

 

Nun habe ich schon folgendes hinbekommen:

 

SELECT * FROM OPENQUERY
(ADSI,
'SELECT department,facsimileTelephoneNumber, mail,mobile,telephonenumber,givenname,sn
FROM ''LDAP://srv....'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by sn  ')

 

Hier habe ich meines Wissens nach nur ein reines select aller Daten im AD.

 

Meine Fragen sind nun folgende:

 

1. Kann man nach Domönenbenutzer noch irgendwie Filtern oder gibt es andere Möglichkeiten zu filten?

    Denn einfach WHERE objectCategory = ''Person'' AND objectClass = ''user'' and telephonenumber is not null .... -> geht irgendwie nicht (Fehler 7321)

2. Wie mache ich da ein Create table mit, hat das schon mal wer gemacht?

 

Falls etwas unklar ist einfach sagen.

Ansonsten danke schonmal für eure Hilfe :)

[MrCocktail 192]

Hi,

 

da ich mich mit SQL Servern immer nur am Rand beschäftige, würde ich wahrscheinlich den Weg des geringeren Widerstandes gehen und mir die Daten per Powershell in eine CSV Datei schreiben und die CSV Datei per SQL einlesen ...

 

Ist wahrscheinlich schneller, ob es eine reine Lösung für SQL gibt und wie diese aussehen könnte, entzieht sich hier meiner Kenntniss.

 

Gruss

J

[DaPete 1]

Ja gut, das habe ich auch schon gemacht, ist meiner Meinung nach aber nicht zielführend :)

[Sunny61 806]

So etwas ähnliches ist hier im Einsatz, allerdings mit einer Access-Datenbank als Front- und Backend. Daten aus dem AD zweimal täglich auslesen und in eine Tabelle wegschreiben. Die schönen Berichte werden zu PDFs, die sind im Intranet verlinkt bzw. werden embedded geöffnet. Sieht gut aus und tut was es soll. ;)

"Select givenName, sn, name, initials, samAccountName, description, otherTelephone, physicalDeliveryOfficename, telephoneNumber, mail, streetAddress, postOfficeBox, l,st, postalCode, homePhone, pager, mobile, facsimileTelephoneNumber, ipPhone, info, title, department, company, manager, directReports, distinguishedName FROM 'LDAP://OU=meineOU,DC=DomainName,DC=LOCAL' WHERE objectClass='user' and objectCategory='person' ORDER BY name ASC")

Damit bekomme ich alle Benutzerobjekte der Category Person.

BTW: Ich importiere zuerst in eine reine Importtabelle, von dort dann nach gewissen Kriterien in die endgültige Tabelle. Wäre bei dir vermutlich auch zielführender. Schon im Hinblick auf das Feld Telefonnummer.

 

Es gibt sog. Multivalue Felder, da können 3 Einträge drin sein, die heißt es heraus filtern. Beispiel das Feld Mitarbeiter. Jeder Manager hat x Mitarbeiter, die mußt Du einzeln raus lesen. Einen Hinweis dazu findet sich auch bei Nils auf der Seite:

http://www.faq-o-matic.net/2003/04/17/auslesen-der-domaenen-daten-ueber-adsi/

If Not IsNull(rsMA.Fields("directReports")) Then 'DirectReports sind die Mitarbeiter der Manager
            varText = Nz(rsMA.Fields("directReports"), "")
            'Manager und die dazugehörenden MA in der tblManagerMA löschen.
                db.Execute ("Delete tblManagerMA.fldManagerSID, tblManagerMA.fldMitarbeiter FROM tblManagerMA WHERE " & _
                        "(((tblManagerMA.fldManagerSID)='" & strSID & "'));")
            Dim i As Integer
            For i = LBound(varText) To UBound(varText)
                varText(i) = Mid(varText(i), InStr(1, varText(i), "=", vbTextCompare) + 1, InStr(1, varText(i), ",", vbTextCompare) - 4)
                'Manager und alle Mitarbeiter in die Tabelle tblManagerMA schreiben.
                rsManMA.AddNew
                rsManMA!fldManagerSID = strSID
                rsManMA!fldMitarbeiter = varText(i)
                rsManMA.Update
            Next
        End If

Evtl. helfen dir auch diese Threads weiter:

http://www.ms-office-forum.net/forum/showthread.php?t=212594

https://groups.google.com/forum/?hl=de#!topic/microsoft.public.sqlserver.programming/2N6rflGA6lY

 

MERGE hilft beim einfügen/aktualisieren von Tabellen übrigens, dann geht das in einem Rutsch.

[Daniel -MSFT- 129]

Warum machst Du das mit SQL? Du könntest per PowerShell alle notwendigen Daten auslesen und direkt die HTML-Datei erzeugen. Get-ADUser wäre der Anfang.

 

Ansonsten schau mal hier http://msdn.microsoft.com/en-us/library/aa746468.aspx und hier http://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/ rein.

[Cybquest 36]

Und wie wäre es, den Schritt mit der SQL-Tabelle zu vergessen und die Intranetseite einfach direkt aus dem AD lesen zu lassen?

Per ASP bzw. PHP z.B.

zu einer Deiner eigentlichen Fragen:

Statt Tabelle löschen könntest Du sie ja auch leeren (DELETE * FROM...) und dann befüllen (INSERT INTO MyTable SELECT ...)

[DaPete 1]

Ob ich nun leere oder lösche wäre ja erstmal wurscht, sofern ich generell ein Schema entwickel.

 

Direkt aus dem ad via PHP/ASP war auch schon eine überlegung, jedoch bin ich des HTML derzeit nicht mächtig und hab mich bis jetzt auch immer etwas schwer damit getan.

[NorbertFe 2.034]

Naja so schwer ist das auch nicht, sowas einfach fertig zu finden. ;)

http://4sysops.com/archives/creating-a-company-directory-web-page-generated-from-active-directory/

und mit ein wenig Budget bspw:

http://www.dovestones.com/active-directory-phone-book/

 

Bye

Norbert

[LiveeviL-Odw 2]

Hi,

Da gibt es eine fertige prozedure von Microsoft.

Die zeigt dir an ob der User noch in der AD vorhanden ist.

sp_validatelogins

http://technet.microsoft.com/de-de/library/ms181728.aspx

Andern falls kann man über denn Datenprovider die AD abfragen:

findest du unter linked servers -> providers > ADsDSOObjects.

http://support.microsoft.com/kb/187529/de

 

Ich hoffe das hilft ;-)

 

mfg

Alex

bearbeitet 28. April 2014 von LiveeviL-Odw

[DaPete 1]

Halli Hallo,

danke schonmal für eure Vorschläge.

War gerade mal ein bissl am rumbasteln und ausprobieren um die befehle langsam mal zu können.

Habe es auch mal hinbekommen eine Table aus dem AD zu generieren, mit den Feldern die ich möchte.

Jedoch gibt es noch folgendes verhalten.

Mein "Script" geht so vor, das es erst die Tabelle löscht, dann neu erstellt mit den Daten aus dem AD.

Danach werden alle unnützen Zeilen gelöscht (so die Theorie).

Tabelle löschen und neu anlegen ist kein Problem.

Möchte ich jedoch die Zeilen löschen, zeigt er mir in der Where-clause an, "Ungültiger spaltenname".

 

Hier mal das Script:

begin
drop table ad_test_cr
end

begin
Select * into ad_test_cr

FROM OPENQUERY
(ADSI,
'SELECT department,mail,mobile,telephonenumber,givenname,sn,manager,homephone,pager,ipphone,streetaddress,l,st,postalcode,co,physicalDeliveryOfficeName,wwwhomepage,extensionAttribute1,extensionAttribute2,extensionAttribute3,lastlogon,pwdlastset,title,whencreated
FROM ''LDAP://srv02........lokal'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by sn  ')
end

begin
DELETE FROM [master].[dbo].[ad_test_cr]
      WHERE sn is null or (telephonenumber is null and mobile is null)
end

Begin und end habe ich zu testzwecken mal eingefügt.

Nehme ich das delete raus, funktioniert zwar die whereclause beim filtern, jedoch wird dann ja die tabelle nicht mit frischen Daten befüllt.

Kann mir jemand erklären was der unsinn soll?

bearbeitet 29. April 2014 von DaPete

[Sunny61 806]

Wird denn die Tabelle gefüllt? Du mußt IMHO beim Select INTO exakt zuweisen.

 

http://technet.microsoft.com/de-de/library/ms174335.aspx

 

Auch würde ich nicht die Tabelle komplett löschen, sondern nur den Inhalt leeren.

DELETE * from ad_test_cr

[Pathomorph 1]

@Sunny: Wenn die Tabelle nicht existiert, wird diese automatisch erzeugt. Dann funktioniert auch "Select * into ad_test_cr...". 

Ich denke, deshalb löscht er die Tabelle vorher...

 

@DaPete: Wenn du die Statements in Transaktionen kapselst, existieren die Tabellen und die Daten gar nicht. Daher die Fehlermeldung...

    Wenn du nach jedem begin..end Block ein GO einfügst, dann macht er auch, was du willst

 

begin

 *blablupp*

end

GO

[DaPete 1]

Cool das geht schonmal *freu*

 

Dann steht mir jetzt nur noch eine Frage im weg.

Das Feld Manager hat ja mehrere Informationen, das würde ich nur auf den Nachnamen des Vorgesetzten reduzieren.

 

Gedacht waren 2 Variablen für die Start und Endposition.

while .....

Startposition =  PATINDEX('%=%',manager)

laenge =  PATINDEX('%\%',manager)

lange = laenge - startposition

Und dann noch ein update auf die Felder jeweils mit update tabelle set manager = substring (manager,startposition,laenge)

end while blub

So im etwa die logik, jedoch akzptiert er meine Variablen nie, ich dachte einfach an - declare startpos as varchar(20) - zum beispiel.

 

Wo mache ich da einen fehler?

[Pathomorph 1]

Variablen beginnen immer mit '@' --> @startposition bzw. @laenge

[DaPete 1]

Ah, was so ein kleines @ alles ausmacht :)

 

In meiner Ausbildung bin ich sowas, wie oben schon geschrieben immer schleifenmäßig durchgegangen.

 

Das sah im etwa so aus.

 

For r in (select * from tabelle) loop

 

//hier dann zugriff auf die die einzelnen Werte die durchgelaufen werden

variable xyz = r.manager

 

end loop

 

wenn ich das richtig gelesen habe, gibt es keine for schleife in MS sql, gibt es sowas ähnliches?

bearbeitet 30. April 2014 von DaPete