query group membership

Indyrb
Indyrb used Ask the Experts™
on
I am in need of a powershell or vbscript that does the following

query exchange\active directory for specified security
distrubtion groups.
list the members of each specified group
export into a pretty report
email report\attachment to specified user.
Schedule script to run monthly.


There are about 30 - 40 groups I need to know the membership of.
I already have the group names \ emails
I dont want to list all groups in ADS as that would be way to many, just the ones that
I have listed.
I dont want 30-40 scripts for each group, I want 1 script that queries the specified groups and exports membership to report, then email. I want to schedule this script to run monthly and send to specified user, so they can evaluate group membership.

I appreciate any help and assistance with creating the script.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, as a good start, try the VBScript here:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_27042055.html#a35829520

it reads the groups from the array at the top, instead of a text file, but that can be changed if you want.

Regards,

Rob.

Author

Commented:
How would you go about reading from a text file? What if the groups are in different OUs inside Active Directory?

Author

Commented:
Okay, I just entered in the groups into the script and away it goes.
I want to create a scheduled task that automates this on the first day of each month.
I remmed out the wscript.excho "Done"
created a task a pointed to the vbs. used the elevated permissions
But it wont work,
It will work if i use it interactively but not via scheduled task.
also I tried creating a batch script and used cscript nameofvbsfile.vbs and that failed too.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
One more thing in addition to the above...

If there is a user name Adam Jones and also a user account named Adam Jones (mobile)
in the ABC group

It only list Adam jones,  not both user accounts. how can we fix this?
Most Valuable Expert 2012
Top Expert 2014
Commented:
Hi, sorry for my delay.  Try this code to read from a file called groups.txt

strInputFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "Groups.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1
Set objGroups = objFSO.OpenTextFile(strInputFile, ForReading, False)
arrScanGroups = Split(objGroups.ReadAll, vbCrLf)

strSMTPRelay = "xxxx"
strFrom = "GroupMembershipChecks@blablabla.com"
strTo = "bla@bla.com"

strOutputFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "AdminGroupReport.csv"

Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
Const ADS_UF_ACCOUNTDISABLE = 2
For Each strGroupName In arrScanGroups
	If strGroupName <> "" Then
		strGroupADsPath = Get_LDAP_User_Properties("group", "name", strGroupName, "adsPath")
		If InStr(strGroupADsPath, "LDAP://") > 0 Then
			Set objGroup = GetObject(strGroupADsPath)
			For Each objMember In objGroup.Members
				If LCase(objMember.Class) = "user" Then
					Set objUser = GetObject(objMember.adsPath)
					On Error Resume Next
					intUAC = objUser.Get("userAccountControl")
					If Err.Number = 0 Then
						On Error Goto 0
						If intUAC And ADS_UF_ACCOUNTDISABLE Then
							strStatus = "Account Disabled"
						Else
							strStatus = ""
						End If
						objOutputFile.WriteLine """" & strGroupName & """,""" & Mid(objUser.Name, 4) & """,""" & strStatus & """"
					Else
						objOutputFile.WriteLine """" & strGroupName & """,""" & Mid(objUser.Name, 4) & """,""" & Err.Number & ": " & Err.Description & """"
						Err.Clear
						On Error Goto 0
					End If
					Set objUser = Nothing
				End If
			Next
			Set objGroup = Nothing
		Else
			'WScript.Echo "There was an error returning the adsPath of " & strGroupName
		End If
	End If
Next
objOutputFile.Close

strTextBody = "Attached is the report listing members in key admin groups." & vbCRLF & vbCRLF & vbCRLF
strTextBody = strTextBody & "These groups include: " & vbCRLF & vbCRLF
For Each strGroupName In arrScanGroups
	strTextBody = strTextBody & strGroupName & vbCRLF
Next
Set objMessage = CreateObject("CDO.Message")
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSMTPRelay
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
objMessage.Configuration.Fields.Update

objMessage.Subject = "Admin User Report for " & Now()
objMessage.From = strFrom
objMessage.To = strTo
objMessage.TextBody = strTextBody
objMessage.AddAttachment strOutputFile

objMessage.Send

'WScript.Echo "Done"
 
Function Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)
      
      ' This is a custom function that connects to the Active Directory, and returns the specific
      ' Active Directory attribute value, of a specific Object.
      ' strObjectType: usually "User" or "Computer"
      ' strSearchField: the field by which to seach the AD by. This acts like an SQL Query's WHERE clause.
      '             It filters the results by the value of strObjectToGet
      ' strObjectToGet: the value by which the results are filtered by, according the strSearchField.
      '             For example, if you are searching based on the user account name, strSearchField
      '             would be "samAccountName", and strObjectToGet would be that speicific account name,
      '             such as "jsmith".  This equates to "WHERE 'samAccountName' = 'jsmith'"
      ' strCommaDelimProps: the field from the object to actually return.  For example, if you wanted
      '             the home folder path, as defined by the AD, for a specific user, this would be
      '             "homeDirectory".  If you want to return the ADsPath so that you can bind to that
      '             user and get your own parameters from them, then use "ADsPath" as a return string,
      '             then bind to the user: Set objUser = GetObject("LDAP://" & strReturnADsPath)
      
      ' Now we're checking if the user account passed may have a domain already specified,
      ' in which case we connect to that domain in AD, instead of the default one.
      If InStr(strObjectToGet, "\") > 0 Then
            arrGroupBits = Split(strObjectToGet, "\")
            strDC = arrGroupBits(0)
            strDNSDomain = strDC & "/" & "DC=" & Replace(Mid(strDC, InStr(strDC, ".") + 1), ".", ",DC=")
            strObjectToGet = arrGroupBits(1)
      Else
      ' Otherwise we just connect to the default domain
            Set objRootDSE = GetObject("LDAP://RootDSE")
            strDNSDomain = objRootDSE.Get("defaultNamingContext")
      End If
 
      strBase = "<LDAP://" & strDNSDomain & ">"
      ' Setup ADO objects.
      Set adoCommand = CreateObject("ADODB.Command")
      Set ADOConnection = CreateObject("ADODB.Connection")
      ADOConnection.Provider = "ADsDSOObject"
      ADOConnection.Open "Active Directory Provider"
      adoCommand.ActiveConnection = ADOConnection
 
 
      ' Filter on user objects.
      'strFilter = "(&(objectCategory=person)(objectClass=user))"
      strFilter = "(&(objectClass=" & strObjectType & ")(" & strSearchField & "=" & strObjectToGet & "))"
 
      ' Comma delimited list of attribute values to retrieve.
      strAttributes = strCommaDelimProps
      arrProperties = Split(strCommaDelimProps, ",")
 
      ' Construct the LDAP syntax query.
      strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
      adoCommand.CommandText = strQuery

      ' Define the maximum records to return
      adoCommand.Properties("Page Size") = 100
      adoCommand.Properties("Timeout") = 30
      adoCommand.Properties("Cache Results") = False
 
      ' Run the query.
      Set adoRecordset = adoCommand.Execute
      ' Enumerate the resulting recordset.
      strReturnVal = ""
      Do Until adoRecordset.EOF
          ' Retrieve values and display.
          For intCount = LBound(arrProperties) To UBound(arrProperties)
                If strReturnVal = "" Then
                      strReturnVal = adoRecordset.Fields(intCount).Value
                Else
                      strReturnVal = strReturnVal & vbCrLf & adoRecordset.Fields(intCount).Value
                End If
          Next
          ' Move to the next record in the recordset.
          adoRecordset.MoveNext
      Loop
 
      ' Clean up.
      adoRecordset.Close
      ADOConnection.Close
      Get_LDAP_User_Properties = strReturnVal
 
End Function

Open in new window


In answer to your question about the Adam Jones (mobile) user, are they are "user" account class?  Or a contact?  The script will only query "user" type accounts, based on this line:
                        If LCase(objMember.Class) = "user" Then

When you run it as a scheduled task, does it generate the report file, and just not email?  Try deleting the report file, then run it, and see if it is recreated.

Regards,

Rob.

Author

Commented:
You are right the (mobile) is a contact.
How can I add both contanct and users to the group membership report?
Most Valuable Expert 2012
Top Expert 2014
Commented:
Change this:
                        If LCase(objMember.Class) = "user" Then

to this
                        If LCase(objMember.Class) = "user" Or LCase(objMember.Class) = "contact" Then



Regards,

Rob.

Author

Commented:
Thanks so much... Still having issues running this as a scheduled task. Even put in my credentials.
If i run it interactly it works just fine, if I run via the taskscheduler it doesnt work
Most Valuable Expert 2012
Top Expert 2014

Commented:
Is the admingroupreport.csv created, or not at all?  Delete it, then run the scheduled task, and see.  It will definately need to run as an account that has access to the AD, and you should run it with highest privileges as well.

If you can see that it's creating the report file, does it write any data to it?  If it's not creating the report file, try setting the report file to a location where everyone has full rights, and you may as well put the script there to execute it from as well.

Also, as the scheduled task, run
cscript C:\Scripts\YourScript.vbs

Rob.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial