Sign in
 
HomeLibraryLearnDownloadsRepositoryCommunityForumsBlog
Script Center >
> 
> 
Query User account status (Enabled or Disabled) from a list of users from text file

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

  • Tuesday, June 26, 2012 8:15 AM
     
     
    Sign In to Vote
    0

    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.

     

All Replies

  • Tuesday, June 26, 2012 9:06 AM
     
     
    Sign In to Vote
    0
    Let's have a look at your script so that we can help you with it.
     
  • Tuesday, June 26, 2012 9:16 AM
     
     
    Sign In to Vote
    0

    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:49 AM
     
     
    Sign In to Vote
    0
    What language is the script to be in?  VBS or Powershell?

    Grant Ward, a.k.a. Bigteddy

     
  • Tuesday, June 26, 2012 10:01 AM
     
     
    Sign In to Vote
    0
    Am looking for VBS script...
     
  • Tuesday, June 26, 2012 10:17 AM
     
     
    Sign In to Vote
    0
    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 3:33 PM
     
      Has Code
    Sign In to Vote
    0

    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)?

    • Edited by premkumartech Tuesday, June 26, 2012 3:34 PM error
    •  
     
  • Tuesday, June 26, 2012 3:41 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    Hi,

    Try replacing those two lines with

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

    Bill

     
  • Tuesday, June 26, 2012 6:44 PM
     
     
    Sign In to Vote
    0
    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

     
  • Wednesday, June 27, 2012 2:23 PM
     
     
    Sign In to Vote
    0

    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 8:24 PM
     
     
    Sign In to Vote
    0

    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?
     
  • Thursday, June 28, 2012 12:24 AM
     
     
    Sign In to Vote
    0

     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 2:42 AM
     
     
    Sign In to Vote
    0

    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:58 AM
     
     
    Sign In to Vote
    0

    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.

    ¯\_(ツ)_/¯


     
  • Thursday, June 28, 2012 7:27 AM
     
     Answered Has Code
    Sign In to Vote
    0

    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.

     
  • Tuesday, July 03, 2012 11:12 PM
     
     
    Sign In to Vote
    0

    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."

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

     
  • Wednesday, July 04, 2012 12:09 AM
     
      Has Code
    Sign In to Vote
    0

    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."

    ¯\_(ツ)_/¯