none
Query User account status (Enabled or Disabled) from a list of users from text file

    Question

  • I have list of users in a text file (samaccountname) as input,

    Need a script to pull the Displayname, Samaccountname and Account Status (enabled or Disabled) to csv or txt file.

    Tuesday, June 26, 2012 8:15 AM

Answers

  • Hi.

    Returning to initial task, it can be solved in *.bat file.

    @echo off
    for /f %%i in (input.txt) do dsquery user -samid %%i >> temp.tmp
    type temp.tmp|dsget user -samid -display -disabled > outfile.txt
    del temp.tmp
    

    input.txt - samid's one per line, in outfile.txt will be generated desired text data. Faster and simplier way in my opinion.

    Thursday, June 28, 2012 7:27 AM

All replies

  • Let's have a look at your script so that we can help you with it.
    Tuesday, June 26, 2012 9:06 AM
  • I do not have any script, i looking for a script from you guys.. can you help me in this..

    Tuesday, June 26, 2012 9:16 AM
  • What language is the script to be in?  VBS or Powershell?

    Grant Ward, a.k.a. Bigteddy

    Tuesday, June 26, 2012 9:49 AM
  • Am looking for VBS script...
    Tuesday, June 26, 2012 10:01 AM
  • Unfortunately for you this is not a free code dispensing service. Respondents here will gladly help you but there needs to be some effort from your side instead of you just holding out your hand.
    Tuesday, June 26, 2012 10:17 AM
  • Ok.. let me try to find and have a help from technet team...

    Currently i found a script, but it is point to local domain (india.microsoft.com) when i ran the script.

    I want the script to point the remote domain (US.microsoft.com).

    Set objRootDSE = GetObject("LDAP://RootDSE") strDNSDomain = objRootDSE.Get("defaultNamingContext")

    Could you help me to modify this 2 lines to point the remote domain (US.microsoft.com)?


    Tuesday, June 26, 2012 3:33 PM
  • Hi,

    Try replacing those two lines with


    strDNSDomain = "DC=us,DC=microsoft,DC=com"

    Bill

    Tuesday, June 26, 2012 3:41 PM
    Moderator
  • I can see this is going nowhere fast.  Can't it be Powershell, we could have been done already!?

    Grant Ward, a.k.a. Bigteddy

    Tuesday, June 26, 2012 6:44 PM
  • Thank for reply AbqBill..

    Bigteddy.. Thanks but currently we are looking for VB Script..

    Can you help in the below script..

    I am using the below script, it is throwing the error below:

    Error: Script.vbs (33,5) Provider, One or more errors occurred during processing of command.

    Script:

    ***************

    on error resume next
     
    Const ADS_SCOPE_SUBTREE = 2
     
    strExcelPath = "c:\Temp\Book1.xls"
     
    strDNSDomain = "DC=us,DC=microsoft,DC=com"
     
    Set objExcel = CreateObject("Excel.Application")
    If (Err.Number <> 0) Then
        On Error GoTo 0
        Wscript.Echo "Excel application not found."
        Wscript.Quit
    End If
    On Error GoTo 0
     
    objExcel.WorkBooks.Open strExcelPath
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
     
    intRow = 2
    Do While objSheet.Cells(intRow, 1).Value <> ""
        strFirstName = objSheet.Cells(intRow, 1).Value
        strLastName  = objSheet.Cells(intRow, 2).Value
        strAccount   = strFirstName & " " & strLastName
        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") = 1000
        objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
        objCommand.CommandText = "SELECT AdsPath,samAccountName,CN FROM 'LDAP://" & strDNSDomain & "' WHERE objectCategory='user' AND cn='" & strAccount & "'"  
        Set objRecordSet = objCommand.Execute
        if objRecordSet.EOF then
            objSheet.Cells(intRow, 3).Value = "Name not found!"
        else
            objRecordSet.MoveFirst
            Do Until objRecordSet.EOF
                objSheet.Cells(intRow, 3).Value = objRecordSet.Fields("samAccountName").Value
                Set objUser = GetObject(objRecordSet.Fields("AdsPath").Value)
                if objUser.AccountDisabled then
                    objSheet.Cells(intRow, 4).Value = "Disabled"
                else
                    objSheet.Cells(intRow, 4).Value = "Enabled"
                end if
                objRecordSet.MoveNext
            Loop
        end if'
        intRow = intRow + 1
    Loop
     
     
    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit
     
    wscript.echo "Done."

    **************************

    Wednesday, June 27, 2012 2:23 PM
  • Can you help in the below script..

    I am using the below script, it is throwing the error below:

    Error: Script.vbs (33,5) Provider, One or more errors occurred during processing of command.

    Any chance of you highlighting line 35 to save us the trouble of counting lines?
    Wednesday, June 27, 2012 8:24 PM
  •  objCommand.CommandText = "SELECT AdsPath,samAccountName,CN FROM 'LDAP://" & strDNSDomain & "' WHERE objectCategory='user' AND cn='" & strAccount & "'"  

    Bad line.  Something about his string is wrong.  The exact code works.

    I suspect the use of US.microsoft.com is a problem as it is not a legitimate AD domain.

    May also be a unicode file.


    ¯\_(ツ)_/¯

    Thursday, June 28, 2012 12:24 AM
  • Oberwald,

    This is line 35:

            objSheet.Cells(intRow, 3).Value = "Name not found!"

    jrv,

    us.microsoft.com domain name i gave as example for some reason..

    For the bad line, can you suggest me for the right one pls...

    Thursday, June 28, 2012 2:42 AM
  • Oberwald,

    This is line 35:

            objSheet.Cells(intRow, 3).Value = "Name not found!"

    jrv,

    us.microsoft.com domain name i gave as example for some reason..

    For the bad line, can you suggest me for the right one pls...

    That is not line 35 and it can in no way create that error.

    Your error message says (as you posted above):

    Error: Script.vbs (33,5) Provider, One or more errors occurred during processing of command.

    (33,5) is line 33 character 5 not line 35.

    This is line 33:

    Set objRecordSet = objCommand.Execute

    And as I posted you sql statement has a syntax issue.  You need to output it to the console and inspect it very closely.

    When I run that code it works.  Something you are doing and NOT posting is causing the issue.

    Can you run this with no error:

    strDomain-"dc=us,dc=microsoft,dc=com"
    Set ldap = GetObject("LDAP:" & strDomain)

    Put just those to lines in a file and run it.  If it produces an error then you have a bad domain string.


    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, June 28, 2012 2:59 AM
    Thursday, June 28, 2012 2:58 AM
  • Hi.

    Returning to initial task, it can be solved in *.bat file.

    @echo off
    for /f %%i in (input.txt) do dsquery user -samid %%i >> temp.tmp
    type temp.tmp|dsget user -samid -display -disabled > outfile.txt
    del temp.tmp
    

    input.txt - samid's one per line, in outfile.txt will be generated desired text data. Faster and simplier way in my opinion.

    Thursday, June 28, 2012 7:27 AM
  • Hi,

    Sorry for the late reply

    I tried the script in same domain, it executed without error.

    But the output in Excel the samaccount name is mentioned as not found:

    Please help....

    SCRIPT:

    *******************

    on error resume next
     
    Const ADS_SCOPE_SUBTREE = 2
     
    strExcelPath = "C:\temp\Account_status\Book1.xlsx"
     
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("defaultNamingContext")
     
    Set objExcel = CreateObject("Excel.Application")
    If (Err.Number <> 0) Then
        On Error GoTo 0
        Wscript.Echo "Excel application not found."
        Wscript.Quit
    End If
    On Error GoTo 0
     
    objExcel.WorkBooks.Open strExcelPath
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
     
    intRow = 2
    Do While objSheet.Cells(intRow, 1).Value <> ""
        strFirstName = objSheet.Cells(intRow, 1).Value
        strLastName  = objSheet.Cells(intRow, 2).Value
        strAccount   = strFirstName & " " & strLastName
        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") = 1000
        objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
        objCommand.CommandText = "SELECT AdsPath,samAccountName,CN FROM 'LDAP://" & strDNSDomain & "' WHERE objectCategory='user' AND cn='" & strAccount & "'"  
        Set objRecordSet = objCommand.Execute
        if objRecordSet.EOF then
            objSheet.Cells(intRow, 3).Value = "Name not found!"
        else
            objRecordSet.MoveFirst
            Do Until objRecordSet.EOF
                objSheet.Cells(intRow, 3).Value = objRecordSet.Fields("samAccountName").Value
                Set objUser = GetObject(objRecordSet.Fields("AdsPath").Value)
                if objUser.AccountDisabled then
                    objSheet.Cells(intRow, 4).Value = "Disabled"
                else
                    objSheet.Cells(intRow, 4).Value = "Enabled"
                end if
                objRecordSet.MoveNext
            Loop
        end if'
        intRow = intRow + 1
    Loop
     
     
    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit
     
    wscript.echo "Done."

    ***************************

    Tuesday, July 03, 2012 11:12 PM
  • It is clear that none of the names in the spreadsheet match any account name in AD.

    Try this version and see what it tells you. Pay close attention to the console output.

    Const strExcelPath = "C:\temp\Account_status\Book1.xlsx"
    ' Get sheet
    Set objExcel = CreateObject("Excel.Application")
    objExcel.WorkBooks.Open strExcelPath
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
    'Get domain
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("defaultNamingContext")
    'Get connection/command
    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") = 1000
    objCommand.Properties("Searchscope") = 2 'ADS_SCOPE_SUBTREE
    'skip header
    intRow = 2 
    ' walk through spreadsheet and get users.
    Do While objSheet.Cells(intRow, 1).Value <> ""
        strFirstName = objSheet.Cells(intRow, 1).Value
        strLastName  = objSheet.Cells(intRow, 2).Value
        strAccount   = strFirstName & " " & strLastName
        objCommand.CommandText = "SELECT AdsPath,samAccountName,CN FROM 'LDAP://" & strDNSDomain & "' WHERE objectCategory='user' AND cn='" & strAccount & "'"  
        Set objRecordSet = objCommand.Execute
        if objRecordSet.EOF then
            objSheet.Cells(intRow, 3).Value = "Account not found:" & strAccount
            WScript.Echo "Name not found:" & strAccount
        Else
            Do Until objRecordSet.EOF
                WScript.Echo "SamAccountName:" & objRecordSet.Fields("samAccountName").Value
                objSheet.Cells(intRow, 3).Value = objRecordSet.Fields("samAccountName").Value
                Set objUser = GetObject(objRecordSet.Fields("AdsPath").Value)
                if objUser.AccountDisabled then
                    objSheet.Cells(intRow, 4).Value = "Disabled"
                Else
                    objSheet.Cells(intRow, 4).Value = "Enabled"
                end if
                objRecordSet.MoveNext
            Loop
        end if'
        intRow = intRow + 1
    Loop
     
     
    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit
     
    wscript.echo "Done."


    ¯\_(ツ)_/¯

    Wednesday, July 04, 2012 12:09 AM