Extract List of User Mailbox Data using vbScript

If you need to export a list of user mailboxes (because your boss is making you or you simply have nothing better to do), it is quite an easy task if you have Exchange 2007 and up because of PowerShell snapins.

You can just fire up PowerShell and import the Exchange 2010 Module.

Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010

Then:

Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | select-object Database, DisplayName, TotalItemSize, TotalDeletedItemSize | Sort-Object Database

Or you can export to CSV like so:

Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | select-object Database, DisplayName, TotalItemSize, TotalDeletedItemSize | Sort-Object Database | Export-Csv .\Mailboxes.CSV -NoTypeInformation

But what if you're still in survival mode with Exchange 2003?

You can use this script should you need to extract the list of user mailboxes from one or more Exchange 2003 servers. 

This script will cough-out the following Fields.

• ServerName
• StorageGroupName
• StoreName
• MailboxGUID
• MailboxDisplayName
• LegacyDN
• Size
• TotalItems
• AssocContentCount
• DeletedMessageSizeExtended
• StorageLimitInfo
• LastLoggedOnUserAccount
• LastLogOnTime
• LastLogOffTime
• DateDiscoveredAbsentAbsentDaysInDS

This script reads the list of servers to be queried from a file called ServerList.ini.
Be sure to create this file and populate it with the server names before running this script. 

It runs in this order:

1. Read list of server from ServerList.ini
2. Query each servers and extract information
3. Save information to "ExchangeMBX.txt"

'==========================================================================
'
' NAME: ExtractMBXInfo.vbs
'
' AUTHOR: june.castillote@gmail.com
' DATE  : 10/01/2011
'
' COMMENT: This is for extracting the list of mailboxes from specified servers in the "serverlist.ini" file.
' FILES : 1. ExtractMBXInfo.vbs - main script
'          2. ServerList.ini - file containing the list of servers for the query.
' USAGE  : cscript ExtractMBXInfo.vbs
'==========================================================================
Option Explicit

On Error Resume Next

Dim t1, t2, t3, d1, d2, d3

Dim strComputer

'==========================================================================

Dim FileToWrite, FileToRead, fsoWrite, fsoRead

Set fsoWrite = CreateObject("Scripting.FileSystemObject")
Set fsoRead = CreateObject("Scripting.FileSystemObject")

Set FileToWrite = fsoWrite.CreateTextFile("ExchangeMBX.txt")
Set FileToRead = fsoRead.OpenTextFile("ServerList.ini")

Dim objWMIService
Dim colItems
Dim objItem
Dim i

'Header row
FileToWrite.WriteLine "Server" & vbTab & "Storage Group" & vbTab & "Mail Store" & vbTab & "Mailbox GUID" & vbTab & "Display Name" & vbTab & "LegacyDN" & vbTab & "Size" & vbTab & "Item Count" & vbTab & "Associated Content Count" & vbTab & "Deleted Message Size" & vbTab & "Date Absent" & vbTab & "Storage Limit Level" & vbtab & "Last LogOn Account" & vbtab & "Last LogOn Time" & vbTab & "Last LogOff Time"

'Iterate through the list of servers
Do While Not FileToRead.AtEndOfStream
     strComputer = FileToRead.ReadLine()
     WScript.Echo Now & " : Connecting to " & strComputer
     Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & _
            "\ROOT\MicrosoftExchangeV2")
   
    WScript.Echo Now & " : Running Query on " & strComputer
    Set colItems = objWMIService.ExecQuery _
    ("Select * from Exchange_Mailbox")
   
    For Each objItem in colItems
        If objItem.LastLogOnTime <> "" Then
            t1=WMIDateStringToDate(objItem.LastLogonTime)
        Else
            t1 = ""
        End If
       
        If objItem.LastLogOffTime <> "" Then
            t2=WMIDateStringToDate(objItem.LastLogOffTime)
        Else
            t2 = ""
        End If
       
        If objItem.DateDiscoveredAbsentAbsentDaysInDS <> "" Then
            t3=WMIDateStringToDate(objItem.DateDiscoveredAbsentInDS)
        Else
            t3=""
        End If           
       
        FileToWrite.WriteLine objItem.ServerName & vbTab & objItem.StorageGroupName & vbTab & objItem.StoreName  & vbTab & objItem.MailboxGUID & vbTab & objItem.MailboxDisplayName & vbTab & objItem.LegacyDN & vbTab & objItem.Size & vbTab & objItem.TotalItems & vbTab & objItem.AssocContentCount & vbTab & objItem.DeletedMessageSizeExtended & vbTab & t3 & vbTab & objItem.StorageLimitInfo & vbTab & objItem.LastLoggedOnUserAccount & vbTab & t1 & vbTab & t2
    Next
Loop
WScript.Echo Now & " : End - Saved to ExchangeMBX.txt"

'To convert WMI time to Standard time format
Function WMIDateStringToDate(dtmInstallDate)
    WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & _
    Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) _
    & " " & Mid (dtmInstallDate, 9, 2) & ":" & _
    Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, _
    13, 2))
End Function

FileToRead.Close
FileToWrite.Close
Set FileToRead = Nothing
Set FileToWrite = Nothing

Share:

Read More

List All Users LDAP Query with Attributes

If you need to extract a list of users from the Active Directory, with customizable list of attributes, here's one for you.

'==========================================================================
' NAME: ListAllusers.vbs
'
' AUTHOR: june.castillote@gmail.com
' DATE  : 03/18/2011
'
' COMMENT: This is for extracting the list of users with their attribute values.
'        Once you run the script, it will output the result to UserList-Extended.txt in TAB-delimited format.
' USAGE  : ListAllusers.vbs
'==========================================================================
 
On Error Resume Next
Dim FileToWrite, fsoWrite
Set fsoWrite = CreateObject("Scripting.FileSystemObject")
Set FileToWrite = fsoWrite.CreateTextFile("UserList-Extended.txt")

Dim objConnection, objQuery, objRootDSE, strDomain
Dim strFilter, strQuery, objRecordSet, gt
Set objConnection = CreateObject("ADODB.Connection")
Set objQuery = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOOBject"
objConnection.Open "Active Directory Provider"
Set objQuery.ActiveConnection = objConnection
Set objRootDSE = GetObject("LDAP://RootDSE")

'Retrieve the domain
strDomain = objRootDSE.Get("defaultNamingContext")
strBase = ""

'To filter results only to user objects
strFilter = "(&(objectCategory=user))"

'Populate the required attributes.
'You can find the list of available attributes here: http://www.kouti.com/tables/userattributes.htm
strAttributes = "distinguishedName,Name"


'Query String
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objQuery.CommandText = strQuery
objQuery.Properties("Page Size") = 99999
objQuery.Properties("Timeout") = 300
objQuery.Properties("Cache Results") = False

FileToWrite.WriteLine "Name" & vbTab & "DN"
Set objRecordSet = objQuery.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    FileToWrite.WriteLine objRecordSet.Fields("Name") & vbTab &objRecordSet.Fields("distinguishedName")
    Wscript.Echo  objRecordSet.Fields("Name") & vbTab &objRecordSet.Fields("distinguishedName")
    objRecordSet.MoveNext
Loop

' Clean up
objConnection.Close
FileToWrite.Close
Set objConnection = Nothing
Set objQuery = Nothing
Set objRootDSE = Nothing
Set objRecordSet = Nothing
Set fsoWrite = Nothing
Set FileToWrite = Nothing

Share:

Read More

Domino Server Data Collector [DISCONTINUED]

NOTE: This project has been discontinued and there are no longer any plans to update this.

Similar to my Exchange Server Data Collector, this one is designed for Domino Server instead.

I made this with .NET, with Lotus Notes COM component.
If you'd ask why didn't I just make this in LotusScript as an agent, well here are my answers.

• I hate LotusScript
• I hate Domino
• I hate Lotus Notes
• If Domino is acting up, the Agent may not work
• If Domino is acting up, the email report or error notification may not be sent.

Those are why I created this independent from any Domino components (or DDM).

What does this do?

This utility collects the following information:

• Queue information like Pending, Hold, Dead mail status in each of routing mailboxes.
• Disk space information of the server.
• Number of mailbox with statistics for Over quota, Warning and Normal. 

 

There are two programs in this project:

• Dominator.exe - The main executable that collects information from the domino server.
• SecureStrings.exe - Tool to encrypt the Notes.ID password that will be in the configuration file of the main executable.

How does this work?

• Read contents of "config.ini"
• Connect to the specified server
• Login to Domino Server using the ID specified in Notes.INI
• Extract Queue information
• Extract Disk space information
• Extract Mailbox information
• Generate report
• Send HTML report via email if enabled

What do I need to use this?

• A computer with Lotus Notes installed and configured with the user ID that have proper access to the Domino Server.
• .NET Framework 2.0 installed
• The program: http://sdrv.ms/Y3w9SI
• You know how to use the Command Prompt

How do I use this?

• Configure your Lotus Notes client PC. This utility will use the "last used" Notes.ID in the notes.ini file to connect to the Domino Server.
• Download the program package here and save/extract to whichever folder you choose. http://sdrv.ms/Y3w9SI
• Modify the CONFIG.INI to input the correct values.
• Run the SecureStrings.exe program to encrypt the Notes.ID password that you will put in the CONFIG.INI

 

• Run DOMINATOR.exe

What's in the CONFIG.INI?

===========================================
[OPTIONS]
;This is the DOMINO server
DSvrName=DOM01/LabWorks

;This is the HOSTNAME
WSvrName=DOM01

;Indicate here the number of routing mailboxes
NumMailBox=1

;Here you put the encrypted password for the NOTES.ID (use the securestrings.exe tool to encryt)
NotesPassword=FZ+/8l+pS2n2bX3eszbrgA==

[SMTP]
;Specify only ONE SMTP server IP and PORT
SMTPServerIP=127.0.0.1
SMTPServerPort=25

;Populate this item if the SMTP server requires authentication, usually this is not the case.
;Accepted values are only TRUE or FALSE
AuthRequired=False

;If AuthRequired=True, you need to fill in this information for the login credentials.
;The format is DOMAIN;UserName;Password
Creds=

[REPORT]
;The company name to appear in the report
CompanyName=LabWorks

;Sender="Display Name"
Sender="DOM01 Mailer"

;When specifying multiple recipient, seperate with commad eg: someone@hp.com,nobody@hp.com
Recipients=

;This is the Title that will be shown in the HTML report and also the Subject of the email report.
;The program will automatically append the DATE and TIME in this string
ReportTitle=DOM01 Status Report as of

;This item indicates if the program will send the report via email
;EmailReport= True or False
EmailReport=False

;This can be any value. This is needed so that the email report will be less likely to be considered as spam.
XMailer=Dominator by june.castillote@gmail.com

;This will be the prefix of the HTML output file. The date and time will automatically be appended.
ReportFile=LabWorks_

;The value which will be considered normal for the mail queue, anything above the value specified here will be shown as critical.
QueueThreshold=100

;The value which will be considered normal for the disk space free percentage, anything above the value specified here will be shown as critical.
DiskThreshold=15 

Download Link

Dominator 1.0

Share:

Read More

How to Set Custom User Mailbox Limits in Exchange 2003 using vbScript

If you inherited an Exchange Server(s) from another Administrator (who died or otherwise), you might find yourself scratching your head or just cussing because of the issues that might have been left behind for you to deal with :)

However, you must understand that they did not intend to leave you those problems (or maybe they did). One of which is that you may find that there are users who do not have mailbox quotas. Of course its bad enough that they don't have quotas, what more if there are hundreds of them? Some of you would say "why not just create a system policy for mailbox size limits?" --- Valid point, but what if these users must have custom mailbox limits and not follow the standard policy for the mail stores?

If there are just 10 of them, I guess modifying the storage limits manually is fine. But again, if there are hundreds or more of these rouge mailboxes it would be best to do the task via script. 




'==========================================================================
'
' NAME: SetCustomMailboxLimits.vbs
'
' AUTHOR: Tito Castillote , june.castillote@gmail.com, shaking-off-the-cobwebs.blogspot.com
' DATE  : 11/12/2012
'
'
' Usage instructions:
' 1. You must create a text file (MailboxLimits.txt) containing the list of user DN (distinguished names)
'     and the storage limits to apply.
'
'        Format: [Distinguished Name]    [Prohibit Send and Receive]    [Prohibit Send]    [Issue Warning]
'        example: CN=Administrator,CN=Users,DC=LabWorks,DC=local    500000    450000    400000
'
'        - the DN and each values must be seperated with a TAB.
'            This script is designed for TAB-delimited Input file.
'
' 2. This script and the Input file must be in the same location and then run this from the Command Prompt.
'     Step 1. Create a backup of existing limits.
'        cscript //nologo SetCustomMailboxLimits.vbs BACKUP
'     Step 2. Modify the limits as found in MailboxLimits.txt
'        cscript //nologo SetCustomMailboxLimits.vbs MODIFY
'    Step 3. Restore the limits from backup (IF NEEDED)
'        cscript //nologo SetCustomMailboxLimits.vbs RESTORE
'
' COMMENT: If you plan to modify a bulk of users, I'd suggest that you do it in batches and not all at once.
'
'========================================================================

'Declare Constants to avoid "Invalid Procedure Calls or Arguments" errors.
Const ADS_PROPERTY_CLEAR = 1, ADS_PROPERTY_UPDATE = 2, ADS_PROPERTY_APPEND = 3
Const ForReading = 1, ForWriting = 2, ForAppending = 8

If WScript.Arguments.Count < 1 Then
    WScript.Echo "Pleas supply the Action (BACKUP or MODIFY)"
ElseIf WScript.Arguments(0)="BACKUP" Then
    WScript.Echo "=================================================="
    WScript.Echo (Now & ": Begin backup of user mailbox limits listed in MailboxLimits.txt")
    CreateBackup
    WScript.Echo (Now & ": Backup is saved to Backup.txt")
    WScript.Echo "=================================================="
ElseIf WScript.Arguments(0)="MODIFY" Then
    WScript.Echo "=================================================="
    WScript.Echo (Now & ": Begin modification of user mailbox limits listed in MailboxLimits.txt")
    ModifyLimits("MailboxLimits.txt")
    WScript.Echo (Now & ": Complete!")
    WScript.Echo "=================================================="
ElseIf WScript.Arguments(0)="RESTORE" Then
    WScript.Echo "=================================================="
    WScript.Echo (Now & ": Begin restoration of user mailbox limits listed in Backup.txt")
    ModifyLimits("Backup.txt")
    WScript.Echo (Now & ": Complete!")
    WScript.Echo "=================================================="
End If

'-----------------Backup Original Values---------------
Sub CreateBackup()

'Create File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFSO1 = CreateObject("Scripting.FileSystemObject")

Set BackUpName = objFSO1.CreateTextFile("Backup.txt",True)
Set MbxName = objFSO.OpenTextFile("MailboxLimits.txt", ForReading)

Do Until MbxName.AtEndofStream
    'Read the current line
    nUser=MbxName.ReadLine
    nuser=Replace(nUser,"/","\/")
   
    'Split the current line into an array with TAB as delimiter
    userArray=Split(nUser, vbTAB)

On Error Resume Next
    'Get the Object with DN name stored in UserArray(0)
    Set objUser = GetObject ("LDAP://" & UserArray(0))
    If Err.Number=0 Then
        'Check if user mailbox exists
        If objUser.homeMDB<>"" then
            'Get information about the Object (User) and start modifying values.
            objUser.GetInfo
           
            'This is just to display which user is being processed.
            WScript.Echo Now & ": Backup limits for - " & objUser.DisplayName
           
            'Write to file
            BackUpName.WriteLine(objUser.DistinguishedName & vbTab & objUser.mDBOverHardQuotaLimit & vbTab & objUser.mDBOverQuotaLimit & vbTab & objUser.mDBStorageQuota)
        Else
            WScript.Echo Now & ": Not an Exchange user - " & objUser.DisplayName
        End If
    Else
        'Set uName = Split(userarray(0),",")
        WScript.Echo Now & ": NOT FOUND!!! - " & userArray(0) 'Replace(uName(0),"CN=","")
        Err.Clear
    End If   
Loop


BackUpName.Close
MbxName.Close
Set BackUpName = Nothing
Set MbxName = Nothing
Set nUser = Nothing
Set userArray = Nothing
Set objFSO = Nothing
Set objFSO1 = Nothing
End Sub
'-----------------End Backup---------------------------


'-----------------Start Modification-------------------

Sub ModifyLimits(strInputFile)
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Open the Input file
Set MbxName = objFSO.OpenTextFile(strInputFile, ForReading)

'Read the inout file from beginning to end
Do Until MbxName.AtEndofStream

    'Read the current line
    nUser=MbxName.ReadLine
    nuser=Replace(nUser,"/","\/")
   
    'Split the current line into an array with TAB as delimiter
    userArray=Split(nUser, vbTAB)

On Error Resume Next
    'Get the Object with DN name stored in UserArray(0)
    Set objUser = GetObject ("LDAP://" & UserArray(0))

    If Err.Number=0 Then
        If objUser.homeMDB<>"" then
            'Get information about the Object (User) and start modifying values.
            objUser.GetInfo
           
            'This is just to display which user is being processed.
            WScript.Echo Now & ": Processing User - " & objUser.DisplayName
       
            'Disable the "User Mailbox Store Defaults"
            objUser.Put "mDBUseDefaults", "FALSE"
           
            If UserArray(3) = "" Then
                objUser.PutEx ADS_PROPERTY_CLEAR, "mDBStorageQuota", 0
            Else
                objUser.Put "mDBStorageQuota", UserArray(3)
            End If
           
            If userArray(2) = "" Then
                objUser.PutEx ADS_PROPERTY_CLEAR, "mDBOverQuotaLimit", 0
            Else
                objUser.Put "mDBOverQuotaLimit", UserArray(2)
            End If
           
            If userArray(1) = "" Then
                objUser.PutEx ADS_PROPERTY_CLEAR, "mDBOverHardQuotaLimit", 0
            Else
                objUser.Put "mDBOverHardQuotaLimit", UserArray(1)
            End If
               
            'Save
            objUser.SetInfo   
        Else
            WScript.Echo Now & ": Not an Exchange user - " & objUser.DisplayName
        End If
    Else
        WScript.Echo Now & ": Not found - " & UserArray(0)
        Err.Clear
    End If   
Loop

'Close the Input file
MbxName.Close

'Clean up
Set objUser = Nothing
Set MbxName = Nothing
Set nUser = Nothing
Set UserArray = Nothing
Set objFSO = Nothing
End Sub
'-----------------End Modification-------------------

Share:

Read More