Solved

How to return multiple values for publicDelegates and publicDelegatesBL

Posted on 2011-02-27
5
1,823 Views
Last Modified: 2012-05-11
Hello experts,

Is anyone able to help me out with my script? Basically, i"m struggling with publicDelegates and
publicDelegatesBL. i cannot export those values into csv file. My script works if user doesn't have or if it has only one Send On Behalf value, doesn't work with multiple values (Send On Behalf). i understand there should be a loop for publicdelegates but I cant figured it out.

I'm not that good with vbscripting, unfortunatelly our environment runs on exchange 2003.

thank you in advance.

'domain LDAP value
strLDAPString = ("LDAP://dc=mydomain.com")

'Declare variables
Dim oSession
Dim oInfoStores
Dim oInfoStore
Dim StorageUsed
Dim NumMessages
Dim strProfileInfo
Dim sMsg

'Input and Output files
Set iFSO = CreateObject("Scripting.FilesyStemObject")
Set oFSO = CreateObject("Scripting.FilesyStemObject")

'Input file 
Set ifile = iFSO.OpenTextFile("userlist.txt")

'Output file
Set ofile = iFSO.CreateTextFile("Mailbox Details.csv", true)
ofile.writeline "Display Name,Mailbox Alias,Email Address,Exchange Server,Mailbox Size (MB),Total Items, Send on Behalf Permissions"

'Set up AD stuff
Const E_ADS_PROPERTY_NOT_FOUND = &h8000500D
Const ADS_SCOPE_SUBTREE = 2
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command") 

'Loop through each user one by one.
Do until ifile.AtEndOfLine
struser = ifile.ReadLine

'Connect to AD and find out where the users mailbox is located.
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 Name, adspath FROM '" & strLDAPString & "' WHERE objectCategory='user' " & "AND sAMAccountName=' " & struser & "'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
strpath = objRecordSet.Fields("ADsPath").Value
set objuser = GetObject(strPath)

'If there is no error,get user the details.
If Err.Number = 0 then
strExchangeServer = (Mid(objuser.msExchHomeServerName,(InStrRev(objuser.msExchHomeServerName,"=")+ 1)))
strExchangeName = objuser.msExchHomeServerName
strMailboxAlias = objuser.mailNickname
strEmailAddress = objuser.mail
strLastName = objuser.lastname
strFirstName = objuser.firstname
strSendOnBehalf = objuser.publicDelegates
else
wscript.echo "Error User Account May Not Exist or Corrupt AD Info For Account"
end if
objConnection.Close 

On Error Resume Next

'Create MAPI Session object.
Set oSession = CreateObject("MAPI.Session")
if Err.Number <> 0 Then
sMsg = "Error creating MAPI.Session."
sMsg = sMsg & "Make sure CDO 1.21 is installed. (Run it on an Exchange server is probably easiest)"
sMsg = sMsg & Err.Number & " " & Err.Description
WScript.Echo sMsg
End If

'Build logon string
strProfileInfo = strExchangeServer & vbLf & strUser

'Log on to mailbox
oSession.Logon , , False, True, , True, strProfileInfo
if Err.Number <> 0 Then
Set oSession = Nothing
End If

'Grab the information stores.
Set oInfoStores = oSession.InfoStores
if Err.Number <> 0 Then

Set oInfoStores = Nothing
Set oSession = Nothing
End If

'Loop through information stores to find the user's mailbox.
For Each oInfoStore In oInfoStores
If InStr(1, oInfoStore.Name, "Mailbox - ", 1) <> 0 Then
'&HE080003 = PR_MESSAGE_SIZE
StorageUsed = oInfoStore.Fields(&HE080003)
if Err.Number <> 0 Then
sMsg = "Error retrieving PR_MESSAGE_SIZE: "
sMsg = sMsg & Err.Number & " " & Err.Description
WScript.Echo sMsg
WScript.Echo "Server: " & obArgs.Item(0)
WScript.Echo "Mailbox: " & obArgs.Item(1)
Set oInfoStore = Nothing
Set oInfoStores = Nothing
Set oSession = Nothing
End If

'&H33020003 = PR_CONTENT_COUNT
NumMessages = oInfoStore.Fields(&H36020003)

if Err.Number <> 0 Then

sMsg = "Error Retrieving PR_CONTENT_COUNT: "
sMsg = sMsg & Err.Number & " " & Err.Description
Set oInfoStore = Nothing
Set oInfoStores = Nothing
Set oSession = Nothing
End If

strInfoStore = oInfoStore.Name
strStorageUsed = StorageUsed /1024
strTotalItems = NumMessages
End If
Next

' Log off.
oSession.Logoff

' Clean up memory.
Set oInfoStore = Nothing
Set oInfoStores = Nothing
Set oSession = Nothing 

'Echo to screen
wscript.echo (strLastName & " " & strFirstName) &","& strMailboxAlias &","& strEmailAddress &","& strExchangeName &","& strStorageUsed &","& strTotalItems &"," & strSendOnBehalf
'Write to CSV file
ofile.writeline (strLastName & " " & strFirstName) &","& strMailboxAlias &","& strEmailAddress &","& strExchangeName &","& strStorageUsed &","& strTotalItems &"," & strSendOnBehalf

loop

Open in new window

0
Comment
Question by:publicworks
  • 3
  • 2
5 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 34994522
Hi, try replacing this line:
strSendOnBehalf = objuser.publicDelegates

with this:
If TypeName(objuser.publicDelegates) = "Empty" Then
      strSendOnBehalf = ""
ElseIf TypeName(objUser.publicDelegates) = "String" Then
      strSendOnBehalf = objuser.publicDelegates
Else
      strSendOnBehalf = Join(objuser.publicDelegates, ",")
End If



The issue is that if there is one, then it's a string value, so a simple assignment works, but if it's multivalued, then it's an array value, so you need to use Join.

Regards,

Rob.
0
 

Author Comment

by:publicworks
ID: 34994597
mate that work great. however, i have another issue with the output.
I couldn't figured it out when i was doing strFullName. In our domain fullname is displayed as:
Smith, John but it exported it as 2 values into csv file, Smith in A2 and John in A3 and so on.
My workaround was to use strLastName and strFirstName and output them in following format:
(strLastName & " " & strFirstName) in one excell cell which was exactly what i needed.

with publicDelegates some of my users have like 10 send on behalf users assigned and the output
is much more complicated,
format is:  CN=user,OU=xxx,OU=xxx,OU=xxx,DC=mydomain,DC=com;
how do i output publicDelegates value into one excell cell? or even better if possible the script would
just take cn name(s)?

appreciate your fast response
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 250 total points
ID: 34994623
OK, that's a bit different, try this.  We now enumerate through each delegate to get just the CN part of the name.  To keep it in one cell, we enclose the value in double quotes.

Replace this:
If TypeName(objuser.publicDelegates) = "Empty" Then
      strSendOnBehalf = ""
ElseIf TypeName(objUser.publicDelegates) = "String" Then
      strSendOnBehalf = objuser.publicDelegates
Else
      strSendOnBehalf = Join(objuser.publicDelegates, ",")
End If

with the code below.

Regards,

Rob.
strSendOnBehalf = ""
If TypeName(objuser.publicDelegates) = "Empty" Then
      strSendOnBehalf = ""
ElseIf TypeName(objUser.publicDelegates) = "String" Then
      strSendOnBehalf = Left(Mid(objUser.publicDelegates, 4), InStr(Mid(objUser.publicDelegates, 4), "OU=") - 1)
Else
	For Each strDelegate In objuser.publicDelegates
		If strSendOnBehalf = "" Then
		  	strSendOnBehalf = Left(Mid(strDelegate, 4), InStr(Mid(strDelegate, 4), "OU=") - 1)
		Else
			strSendOnBehalf = strSendOnBehalf & "," & Left(Mid(strDelegate, 4), InStr(Mid(strDelegate, 4), "OU=") - 1)
		End If
	Next
End If
If strSendOnBehalf <> "" Then strSendOnBehalf = """" & strSendOnBehalf & """"

Open in new window

0
 

Author Comment

by:publicworks
ID: 35002819
great job mate, working as intended :)
thanks for your prompt answer and support
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35002838
Cool. Thanks for the grade.

Regards,

Rob.

P.S. Instead of separating your first name and last name, you could have enclosed them in quotes like I have, so that they stay in one column, but it doesn't matter.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question