Sign in
 
HomeLibraryLearnDownloadsRepositoryCommunityForumsBlog
Script Center >
> 
> 
Script to find all users in AD who are not assigned a Department

Answered Script to find all users in AD who are not assigned a Department

  • Friday, November 30, 2012 1:45 PM
     
     
    Sign In to Vote
    0
    I'd like to have a script to find all users in AD who are not assigned to a department and export the list to .xls file.  Thanks for your help.
     

All Replies

  • Friday, November 30, 2012 3:11 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    What have you tried so far, and with what results?

    Bill

     
  • Friday, November 30, 2012 3:38 PM
     
      Has Code
    Sign In to Vote
    0
     
    Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
Set objContainer = GetObject("LDAP://cn=users," & objRootDSE.Get("defaultNamingContext")) 
set objConn = CreateObject("ADODB.Connection")
set objCmd = CreateObject("ADODB.Command")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objCmd.ActiveConnection = objConn
objCmd.Properties("Cache Results") = False
strFilter = "(&(objectclass=user)(objectcategory=user))"
strQuery = "<LDAP://" & strDNSDomain & ">;" & strFilter & ";distinguishedName;subtree"
objCmd.CommandText = strQuery
Set wshFSO=Createobject("Scripting.FileSystemObject")

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
 
strCSVPath = "C:\My_Scripts\users.csv"
 
if wshFSO.FileExists(strCSVPath) then
	wshFSO.deletefile(strCSVPath)
end if
 
Set objLogFile = wshFSO.CreateTextFile(strCSVPath)
objLogFile.Write("User, Department" & chr(13) & chr(10))
 
 
Set objRecordSet = objCmd.Execute
 
Do Until objRecordSet.EOF
 
  strDN = objRecordSet.Fields("distinguishedName")
  Set objComputer = GetObject("LDAP://" & strDN)
 
  	strCN = objcomputer.cn
	strDesc = objComputer.description
	 
	objLogFile.Write(strCN & ", " & strDesc & ", " & strOS & ", " & strSP & chr(13) & chr(10))
objRecordSet.MoveNext
Loop
 
msgbox "Complete." & vbLF & "Ouput has been saved to " & strCSVPath
 
objLogFile.close
objConn.Close
Set objGroup = Nothing
Set objRootDSE = Nothing
Set objCmd = Nothing
Set objConn = Nothing

    I used this a couple months ago to query Computers and their OS.  I've made changes at the top, but I'm not too familiar with AD names (ie - strDesc, strCN).

    Right now, the script is pulling their name and description.  I'm not sure what should be changed to pull the department.

    Thanks.

     
  • Friday, November 30, 2012 3:46 PM
     
      Has Code
    Sign In to Vote
    0

    Nevermind, it was simple enough.  Just changed objComputer.Description to objComputer.deptartment and removed the excess below.

    Thanks for taking the time to read my post and sorry for the waste of time... I do have one more question though.

    Set objLogFile = wshFSO.CreateTextFile(strCSVPath)
objLogFile.Write("User, Department" & chr(13) & chr(10))

    Do you know  how to make these headers when written?  What I'm trying to say is when these are added to my list as categories, they are still sortable... How do I write in the script to make them category headers and not sort with the rest of the info?

    Thanks,

    Josh

     
  • Friday, November 30, 2012 4:08 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1

    Hi,

    A CSV file is just a text file. Using VBScript, if you want the txt file to have a CSV header, you'll have to write it to the output file yourself. As far as sorting: That's up to the tool that does the sorting. I know that Excel can sort in a way that ignores the header row (you tell it the first row is the header, then do your sort).

    Note that the most of the manual work required in VBScript is not needed in PowerShell. The Active Directory cmdlets are particularly useful here:

    PS C:\> get-adcomputer -filter { Name -like '*'} -properties name,department |
  select-object name,department |
  sort-object name |
  export-csv report.csv -notypeinformation

    This is a single command that creates a sorted CSV report of all computers in the current domain that includes their name and department attributes.

    Bill

    • Marked As Answer by bubowski Friday, November 30, 2012 4:39 PM
    •  
     
  • Friday, November 30, 2012 4:18 PM
     
     
    Sign In to Vote
    0
    Well that's pretty cool.  Anyway to choose which container is searched?
     
  • Friday, November 30, 2012 4:38 PM
    Moderator
     
     
    Sign In to Vote
    1

    Yes; use the -searchbase parameter. See the documentation for more details.

    Bill

     
  • Friday, November 30, 2012 4:44 PM
     
     
    Sign In to Vote
    0
    Wow, very helpful.  Thank you Bill.
     
  • Friday, November 30, 2012 6:04 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1

    The filter for all users that do not have a department would be:

    (&(objectCategory=person)(objectClass=user)(!(department=*)))

    -----

    The final clause (of 3) above means where the department attribute does NOT, "!", have any , "*", value.

    Richard Mueller - MVP Directory Services

    • Marked As Answer by bubowski Friday, November 30, 2012 7:52 PM
    •