Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1432
  • Last Modified:

How to extract Account Status {Disabled} from AD

I found a script on the net to extract given fields from AD and create an Excel spreadsheet.
I need to add the field Account status {Disabled / Enabled} as well.
How can I do this - This is driving me nuts.

Thanks in advance.

Const ADS_SCOPE_SUBTREE = 2
 
Set objExcel = CreateObject("Excel.Application")
 
objExcel.Visible = True
objExcel.Workbooks.Add
 
objExcel.Cells(1, 1).Value = "User name"
objExcel.Cells(1, 2).Value = "Home Directory"
objExcel.Cells(1, 3).Value = "Department"
objExcel.Cells(1, 4).Value = "Distinguished Name"
 
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
 
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
objCommand.CommandText = _
    "SELECT CN, HOMEDIRECTORY, DEPARTMENT, distinguishedname FROM " _
        & "'LDAP://dc=brookfieldproperties,dc=com' WHERE " _
            & "objectCategory='user'"  
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 2
 
Do Until objRecordSet.EOF
    objExcel.Cells(x, 1).Value = _
        objRecordSet.Fields("CN").Value
    objExcel.Cells(x, 2).Value = _
        objRecordSet.Fields("homedirectory").Value
    objExcel.Cells(x, 3).Value = _
        objRecordSet.Fields("department").Value
    objExcel.Cells(x, 4).Value = _
        objRecordSet.Fields("distinguishedname").Value
 
    x = x + 1
    objRecordSet.MoveNext
Loop
 
Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
 
Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
 
Set objRange = objExcel.Range("C1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
 
Set objRange = objExcel.Range("D1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
 
Set objRange = objExcel.Range("A1").SpecialCells(11)
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")

Open in new window

0
MAJAEJ
Asked:
MAJAEJ
1 Solution
 
exx1976Commented:
Try this.
Const ADS_SCOPE_SUBTREE = 2
 
Set objExcel = CreateObject("Excel.Application")
 
objExcel.Visible = True
objExcel.Workbooks.Add
 
objExcel.Cells(1, 1).Value = "User name"
objExcel.Cells(1, 2).Value = "Home Directory"
objExcel.Cells(1, 3).Value = "Department"
objExcel.Cells(1, 4).Value = "Distinguished Name"
objExcel.Cells(1,5).Value = "Account Status"
 
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
 
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
objCommand.CommandText = _
    "SELECT CN, HOMEDIRECTORY, DEPARTMENT, distinguishedname FROM " _
        & "'LDAP://dc=brookfieldproperties,dc=com' WHERE " _
            & "objectCategory='user'"  
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 2
 
Do Until objRecordSet.EOF
    objExcel.Cells(x, 1).Value = _
        objRecordSet.Fields("CN").Value
    objExcel.Cells(x, 2).Value = _
        objRecordSet.Fields("homedirectory").Value
    objExcel.Cells(x, 3).Value = _
        objRecordSet.Fields("department").Value
    objExcel.Cells(x, 4).Value = _
        objRecordSet.Fields("distinguishedname").Value
    If objRecordSet.Fields("accountDisabled").Value = True Then
    	objExcel.Cells(x,5).Value = "Disabled"
    Else
    	objExcel.Cells(x,5).Value = "Enabled"
    End If
 
    x = x + 1
    objRecordSet.MoveNext
Loop
 
Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
 
Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
 
Set objRange = objExcel.Range("C1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
 
Set objRange = objExcel.Range("D1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
 
Set objRange = objExcel.Range("F1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
 
Set objRange = objExcel.Range("A1").SpecialCells(11)
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")

Open in new window

0
 
MAJAEJAuthor Commented:
Thank you.
0

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now