AD in Excel Importieren mit Userdaten

Kabadeluxe · 14. Februar 2013

Hallo Jungs

Ich habe ein Problem auf der Arbeit.

Ich muss eine AD auslesen, und in Excel speichern.

Ich muss auch bei den Usern die Registrierkarten "Allgemein", *Adresse" und und und Auslesen.

Des weiteren muss ich ebenfalls noch "Security Groups" die Gruppen auslesen inklusive Benutzern die in den Gruppen sind.

Kann mir da jemand weiterhelfen?

Danke schon mal für die Antworten.

Gruss Thomas

Timsk · 14. Februar 2013

Hi,

schau Dir Mal http://www.faq-o-matic.net/jose/ an, eventuell kannst du hiermit deine Anforderungen erfüllen. Wenn das Ergebnis in Excel muss würde ich Dir das auslesen via Powershell empfehlen, eventuell kennt jemand aber auch noch eine andere/fertige Lösung.

Viele Grüße!

bearbeitet 14. Februar 2013 von Timsk

Kabadeluxe · 14. Februar 2013

Also es sollte schon in einer Excelliste sein. Aber Danke Newbie schonmal für deinen Tip

Timsk · 14. Februar 2013

Ich kenne leider keine fertige Lösung die das Ganze nach Excel exportiert. Ich würde Dir hier wie gesagt empfehlen die Informationen mittels Powershell zu ermitteln und in eine csv Datei schreiben zu lassen, je nachdem wie gut du dich bereits mit der Powershell auskennst kannst du damit schnell dein Ziel erreichen.

Wenn du genaue Vorstellungen lieferst wie die Excel Datei auszusehen hat kann man Dich auch eventuell dabei unterstützen.

Viele Grüße!

Kabadeluxe · 14. Februar 2013

Danke schon schon mal Timsk

Ich habe einfach mal ein Script Geschrieben und was soll ich sagen es geht :-)

Ich will den Script euch nicht vorenthalten

Dim ObjWb
Dim ObjExcel
Dim x, zz
Set objRoot = GetObject("LDAP://RootDSE")
strDNC = objRoot.Get("DefaultNamingContext")
Set objDomain = GetObject("LDAP://" & strDNC) ' Bind to the top of the Domain using LDAP using ROotDSE
Call ExcelSetup("Sheet1") ' Sub to make Excel Document
x = 1
Call enummembers(objDomain)
Sub enumMembers(objDomain)
On Error Resume Next
Dim Secondary(20) ' Variable to store the Array of 2ndary email alias's
For Each objMember In objDomain ' go through the collection

If ObjMember.Class = "user" Then ' if not User object, move on.
x = x +1 ' counter used to increment the cells in Excel

    objwb.Cells(x, 1).Value = objMember.Class
    ' I set AD properties to variables so if needed you could do Null checks or add if/then's to this code
    ' this was done so the script could be modified easier.
SamAccountName = ObjMember.samAccountName
Cn = ObjMember.CN
FirstName = objMember.GivenName
LastName = objMember.sn
initials = objMember.initials
Descrip = objMember.description
Office = objMember.physicalDeliveryOfficeName
Telephone = objMember.telephonenumber
EmailAddr = objMember.mail
WebPage = objMember.wwwHomePage
Addr1 = objMember.streetAddress
City = objMember.l
State = objMember.st
ZipCode = objMember.postalCode
Title = ObjMember.Title
Department = objMember.Department
Company = objMember.Company
Manager = ObjMember.Manager
Profile = objMember.profilePath
LoginScript = objMember.scriptpath
HomeDirectory = ObjMember.HomeDirectory
HomeDrive = ObjMember.homeDrive
AdsPath = Objmember.Adspath
LastLogin = objMember.LastLogin

zz = 1 ' Counter for array of 2ndary email addresses
For each email in ObjMember.proxyAddresses
     If Left (email,5) = "SMTP:" Then
Primary = Mid (email,6) ' if SMTP is all caps, then it's the Primary
     ElseIf Left (email,5) = "smtp:" Then
        Secondary(zz) = Mid (email,6) ' load the list of 2ndary SMTP emails into Array.
        zz = zz + 1
     End If
Next
' Write the values to Excel, using the X counter to increment the rows.

objwb.Cells(x, 2).Value = SamAccountName
objwb.Cells(x, 3).Value = CN
objwb.Cells(x, 4).Value = FirstName
objwb.Cells(x, 5).Value = LastName
objwb.Cells(x, 6).Value = Initials
objwb.Cells(x, 7).Value = Descrip
objwb.Cells(x, 8).Value = Office
objwb.Cells(x, 9).Value = Telephone
objwb.Cells(x, 10).Value = EmailAddr
objwb.Cells(x, 11).Value = WebPage
objwb.Cells(x, 12).Value = Addr1
objwb.Cells(x, 13).Value = City
objwb.Cells(x, 14).Value = State
objwb.Cells(x, 15).Value = ZipCode
objwb.Cells(x, 16).Value = Title
objwb.Cells(x, 17).Value = Department
objwb.Cells(x, 18).Value = Company
objwb.Cells(x, 19).Value = Manager
objwb.Cells(x, 20).Value = Profile
objwb.Cells(x, 21).Value = LoginScript
objwb.Cells(x, 22).Value = HomeDirectory
objwb.Cells(x, 23).Value = HomeDrive
objwb.Cells(x, 24).Value = Adspath
objwb.Cells(x, 25).Value = LastLogin
objwb.Cells(x,26).Value = Primary

' Write out the Array for the 2ndary email addresses.
For ll = 1 To 20
objwb.Cells(x,26+ll).Value = Secondary(ll)
Next
' Blank out Variables in case the next object doesn't have a value for the property
SamAccountName = "-"
Cn = "-"
FirstName = "-"
LastName = "-"
initials = "-"
Descrip = "-"
Office = "-"
Telephone = "-"
EmailAddr = "-"
WebPage = "-"
Addr1 = "-"
City = "-"
State = "-"
ZipCode = "-"
Title = "-"
Department = "-"
Company = "-"
Manager = "-"
Profile = "-"
LoginScript = "-"
HomeDirectory = "-"
HomeDrive = "-"
Primary = "-"
For ll = 1 To 20
Secondary(ll) = ""
Next
    End If

    ' If the AD enumeration runs into an OU object, call the Sub again to itinerate

    If objMember.Class = "organizationalUnit" or OBjMember.Class = "container" Then
        enumMembers (objMember)
    End If
Next
End Sub
Sub ExcelSetup(shtName) ' This sub creates an Excel worksheet and adds Column heads to the 1st row
Set objExcel = CreateObject("Excel.Application")
Set objwb = objExcel.Workbooks.Add
Set objwb = objExcel.ActiveWorkbook.Worksheets(1)
Objwb.Name = "Active Directory Users" ' name the sheet
objwb.Activate
objExcel.Visible = True
objwb.Cells(1, 2).Value = "SamAccountName"
objwb.Cells(1, 3).Value = "CN"
objwb.Cells(1, 4).Value = "FirstName"
objwb.Cells(1, 5).Value = "LastName"
objwb.Cells(1, 6).Value = "Initials"
objwb.Cells(1, 7).Value = "Descrip"
objwb.Cells(1, 8).Value = "Office"
objwb.Cells(1, 9).Value = "Telephone"
objwb.Cells(1, 10).Value = "Email"
objwb.Cells(1, 11).Value = "WebPage"
objwb.Cells(1, 12).Value = "Addr1"
objwb.Cells(1, 13).Value = "City"
objwb.Cells(1, 14).Value = "State"
objwb.Cells(1, 15).Value = "ZipCode"
objwb.Cells(1, 16).Value = "Title"
objwb.Cells(1, 17).Value = "Department"
objwb.Cells(1, 18).Value = "Company"
objwb.Cells(1, 19).Value = "Manager"
objwb.Cells(1, 20).Value = "Profile"
objwb.Cells(1, 21).Value = "LoginScript"
objwb.Cells(1, 22).Value = "HomeDirectory"
objwb.Cells(1, 23).Value = "HomeDrive"
objwb.Cells(1, 24).Value = "Adspath"
objwb.Cells(1, 25).Value = "LastLogin"
objwb.Cells(1, 26).Value = "Primary SMTP"
End Sub
MsgBox "Done" ' show that script is complete