Active Directory & Groups

[faza]

Hi,

 

Can someone tell me how i could find out all the groups that all my users are assigned to. I would like it in one big report say an excel spreadsheet.

 

Faza

[srochford]

Short answer is that it's not easy :-)

 

The slightly longer answer is the script below. This will query your AD and list every user together with their groups in a spreadsheet.

 

Depending on what you're trying to do this might be OK (eg if an auditor says "do you have a list of the security groups for each person" then you can say yes and the auditor will be happy!)

 

If you're trying to find out who's in "group_entitled_to_read_really_confidential_files" then this may not work for you - you need to do it the other way round (enumerate groups and then get members of the groups).

 

It also doesn't help if you have nested groups (eg let's say you have a group called "pupils" made up of "pupils_year7", "pupils_year8" etc and the pupils are actually in the individual year groups. This will only give you the actual group they're a member of, not the parent groups

 

Finally, it might be more useful to put this in a database (although that's kind of what AD is ...). If you did, I would have a table having 2 columns - user and group and "user" could repeat - eg:

aa234 ese-phd09-dl

aa234 ic-pgr-survey-dl

aa234 CCML Users

 

You can then query this to get a list of members of any group or do a cross tab to get user down the side and group across the top - a much quicker way to check who's in which group.

 

'get an Excel workbook ready
Set oExcel= CreateObject("Excel.Application")
oExcel.Application.Visible = True
oExcel.Application.Workbooks.Add
'add some headings - up to "group10"
oExcel.Application.activesheet.cells(1,1)="User"
for i=1 to 10
 oExcel.Application.activesheet.cells(1,i+1)="Group" & i
next
'connect to active directory; first find the domain name
Set oRootDSE = GetObject("LDAP://RootDSE")
sDNSDomain = oRootDSE.Get("defaultNamingContext")
'now set up ADO
Set ocommand = CreateObject("ADODB.Command")
Set oConn = CreateObject("ADODB.Connection")
oConn.Provider = "ADsDSOObject"
oConn.Open
ocommand.ActiveConnection = oConn
'want to look for all "person" objects in the domain
sCMD = ";(&(objectCategory=person)(objectClass=user));distinguishedName,samaccountname;subTree"
ocommand.CommandText=sCMD

'return results in blocks of 100
ocommand.Properties("Page Size") = 100
'how long to wait for results; if not returned in this time then script will give up
ocommand.Properties("Timeout") = 60
'don't store data locally; if query run again then will re-visit server
ocommand.Properties("Cache Results") = False
oCommand.properties("sort on")="samaccountname"
'execute the query against AD and get a recordset
Set oRS = ocommand.Execute
'check - did we get any results
'set row and column for Excel sheet
iRow=2
if not oRS.eof then
 do while not ors.eof
   iCol=1
   'get the username and show it
   sUser=ors("samaccountname")
   wscript.echo sUser
   oExcel.Application.activesheet.cells(iRow,iCol)=sUser
   iCol=iCol+1
   'bind to the user
   set oUser=getobject("LDAP://" & ors("distinguishedname"))
   'kludge - could have no members if user only in primary group (domain users)
   on error resume next
   arrMemberof=oUser.getex("memberof")
   if err.number=0 then
     'we've got a result - are there any members
     if isempty(arrMemberof) then
       wscript.echo "None"
       oExcel.Application.activesheet.cells(iRow,iCol)="None"
       'got something; if the result is a string there's just one member so show it
     elseif typename(arrMemberOf)="String" then
       sGroup=Clean(arrMemberof)
       wscript.echo sGroup
       oExcel.Application.activesheet.cells(iRow,iCol)=sGroup
     else
       'get here because result is an array - show each member in turn
       for each sGroup in arrMemberof
         sGroup=Clean(sGroup)
         wscript.echo sGroup
         oExcel.Application.activesheet.cells(iRow,iCol)=sGroup
         iCol=iCol+1
       next
     end if
   else
     'error handling; this user is not specifically in any group (just primary group)
     wscript.echo "None"
     oExcel.Application.activesheet.cells(iRow,iCol)="None"
     err.clear
   end if
   'move to next user
   iRow=iRow+1
   ors.movenext
 loop
end if

function Clean(sText)
 'groups are returned in form cn=XXX,cn=YYY etc - just want the first chunk
 arrParts=split(sText,",")
 'arrParts(0) will be cn=XXX; now lose the first 3 chars
 sText=mid(arrparts(0),4)
 Clean=sText
end function

[Rick2134]

This software from wisesoft could help.

 

Download Bulk AD Users. Think there is a tool in there to export the groups.

Great for major changes if you have no management tools!!!

 

http://www.wisesoft.co.uk/software/bulkadusers/default.aspx

 

Cheers, Rich