Solved

How to return multiple values for publicDelegates and publicDelegatesBL

Posted on 2011-02-27
5
1,803 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now