[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1952
  • Last Modified:

How to return multiple values for publicDelegates and publicDelegatesBL

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
publicworks
Asked:
publicworks
  • 3
  • 2
1 Solution
 
RobSampsonCommented:
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
 
publicworksAuthor Commented:
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
 
RobSampsonCommented:
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
 
publicworksAuthor Commented:
great job mate, working as intended :)
thanks for your prompt answer and support
0
 
RobSampsonCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now