RE: Exchange Query

  • From: "Nick Parkes" <nick.parkes@xxxxxxxxxxxxxxxxx>
  • To: "[ExchangeList]" <exchangelist@xxxxxxxxxxxxx>
  • Date: Tue, 18 Oct 2005 10:27:10 +0100

Sandeep,

You could try this.....

I also run off graphs on-demand from the intranet using the historical data 
file.

--Nick



''''''''''''''''''''''
' This Script generates a report on all Exchange mailbox enabled users and 
their mailbox sizes
'
''''''''''''''''''''''



'Global variables
Dim gMailboxSizes
Dim gMailboxItems
Set gMailboxSizes = CreateObject("Scripting.Dictionary")
Set gMailboxItems = CreateObject("Scripting.Dictionary")

strComputer = "SERVERNAME"
UserName = ""
Password = ""


Const ForAppending = 8
Const ForWriting = 2


'Get date/time formatted as YYYYMMDD_HHMMSS
strDate= Year(Now) & Right(Month(Now)+100, 2) & Right(Day(Now)+100, 2) & "_" & 
Right(Hour(Now)+100, 2) & Right(Minute(Now)+100, 2) & Right(Second(Now)+100, 2)


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' START OF USER CONFIGURABLE VARIABLES 
'''''''''''''''''''''''''''''''''''''''''''''

strLogFile=" Mailboxes.log"
strDataFile=" Mailboxes.csv"
strHistFile=" MailboxesHistory.csv"

' END OF USER CONFIGURABLE VARIABLES 
'''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


PrintLog "Starting Exchange Mailbox report script"

DeleteFile

QueryMailboxSizes

QueryADForExchangeUsers

SendMail











Sub QueryADForExchangeUsers()

        Dim varTest, samAccountName
        Dim Email

        printlog("Executing LDAP query to retrieve Exchange users")

        Set rootDSE = GetObject("LDAP://RootDSE";)
        DomainContainer = rootDSE.Get("defaultNamingContext")

        Set conn = CreateObject("ADODB.Connection")
        conn.Provider = "ADSDSOObject"
        conn.Open "ADs Provider"

        ldapStr = "<LDAP://"; & DomainContainer & ">;(&(&(& (mailnickname=*) (| 
(&(objectCategory=person)(objectClass=user)(|(homeMDB=*)(msExchHomeServerName=*)))(&(objectCategory=person)(objectClass=contact))
 ))));adspath;subtree"

        Set rs = conn.Execute(ldapStr)



        Printdata "samAccountName,displayName," &_
          "MailboxSize(MBytes)," &_
          "AmountOverLimit(MBytes)," &_
          "MailStorageIssueWarning(MBytes)," &_
          "MailStorageProhibitSend(MBytes)," &_
          "MailStorageProhibitSendAndReceive(MBytes)," &_
          "MailboxItems(Qty)," &_
          "telephoneNumber,department,title," &_
          "homeDirectory, logonCount," &_
          ""

        While Not rs.EOF
              Set f = GetObject(rs.Fields(0).Value)

              If instr(f.displayName, "Resource - ") Or instr(f.displayName, 
"SystemMailbox") Then
                'Skip record
              Else

              Select Case f.Class
                    Case "user"
                         
                        users = users + 1
                        samAccountName = f.samAccountName

                        mDBStorageQuota=round(f.mDBStorageQuota/1024)
                        mailboxsz=round(MailBoxSize(f.displayName)/1024)
                        If mDBStorageQuota > 0 and mailboxsz > mDBStorageQuota 
Then
                                overlimit=mailboxsz-mDBStorageQuota
                        Else
                                overlimit=0
                        End If


                        If f.mDBUseDefaults = FALSE Then
                                mDBStorageQuota= round(f.mDBStorageQuota/1024)
                                mDBOverQuotaLimit= 
round(f.mDBOverQuotaLimit/1024)
                                mDBOverHardQuotaLimit= 
round(f.mDBOverHardQuotaLimit/1024)
                        Else
                                mDBStorageQuota="Default/No Limit"
                                mDBOverQuotaLimit="Default/No Limit"
                                mDBOverHardQuotaLimit="Default/No Limit"

                        End If

                        Printdata (f.samAccountName & "," &_
                                        f.displayName & "," &_
                                        mailboxsz & "," &_
                                        overlimit & "," &_
                                        mDBStorageQuota & "," &_
                                        mDBOverQuotaLimit & "," &_
                                        mDBOverHardQuotaLimit & "," &_
                                        MailboxItems(f.displayName) & "," &_
                                        f.telephoneNumber & "," &_
                                        f.department & "," &_
                                        f.title & "," &_
                                        f.homeDirectory & "," &_
                                        f.logonCount & "," &_
                                "")
              End Select


              End If

              rs.MoveNext
        Wend

        printlog (users & " Exchange users found")



End Sub





Sub QueryMailboxSizes()
        Dim strQuery
        Dim objItem
        Dim colItems
        Dim SWBemlocator
        Dim objWMIService

        printlog ("Getting Mailbox sizes from " & strComputer)

        strQuery="Select * from Exchange_Mailbox"
        Set SWBemlocator = CreateObject("WbemScripting.SWbemLocator")
        Set objWMIService = 
SWBemlocator.ConnectServer(strComputer,"\root\MicrosoftExchangeV2",UserName,Password)
        Set colItems = objWMIService.ExecQuery(strQuery,,48)

        For Each objItem in colItems

                printlog ("Adding size & totalitems for displayname=" & 
objItem.MailboxDisplayName)
                If gMailboxSizes.Exists(objItem.MailboxDisplayName) Then
                  printlog ("DUPLICATE DisplayName exists for " & 
objItem.MailboxDisplayName & " with size " & objItem.Size & " and TotalItesm " 
& objItem.TotalItems)
                Else
                  gMailboxSizes.Add objItem.MailboxDisplayName, objItem.Size
                  gMailboxItems.Add objItem.MailboxDisplayName, 
objItem.TotalItems
                End If

        Next
        

End Sub


Function MailboxSize(displayName)

        If gMailboxSizes.Exists(displayName) Then
            MailboxSize=gMailboxSizes(displayName)
        Else
            MailboxSize=0
        End If

End Function

Function MailboxItems(displayName)

        If gMailboxItems.Exists(displayName) Then
            MailboxItems=gMailboxItems(displayName)
        Else
            MailboxItems="Error"
        End If

End Function




Sub printlog(strMessage)
        'Open log file
        'Append to log file
        '

        Dim oFSO, oFile
        Dim strData
        Dim strPattern, strReplace
        Dim strD

        strD = FormatDateTime(Now(),0)

        Set oFSO = CreateObject("Scripting.FileSystemObject")

        If NOT oFSO.FileExists(strLogFile) Then
             Set oFile=oFSO.CreateTextFile(strLogFile)
             oFile.WriteLine strD & " - " & "Log file created."
             oFile.Close
        End If
        
        Set oFile = oFSO.OpenTextFile(strLogFile, ForAppending)
        oFile.WriteLine strD & " - " & strMessage
        oFile.Close

        Wscript.Echo strD & " - " & strMessage
        
End Sub

Sub printdata(strMessage)
        'Open data file
        'Append to data file
        '

        Dim oFSO, oFile
        Dim strData
        Dim strPattern, strReplace
        Dim strD

        strD = FormatDateTime(Now(),0)

        Set oFSO = CreateObject("Scripting.FileSystemObject")

        If NOT oFSO.FileExists(strDataFile) Then
             Set oFile=oFSO.CreateTextFile(strDataFile)
             printlog( "Data file created.")
             oFile.Close
        End If
        
        Set oFile = oFSO.OpenTextFile(strDataFile, ForAppending)
        oFile.WriteLine strD & "," & strMessage
        oFile.Close

        Set oFSO = Nothing


        'Append to history file
        Set oFSO = CreateObject("Scripting.FileSystemObject")

        If NOT oFSO.FileExists(strHistFile) Then
             Set oFile=oFSO.CreateTextFile(strHistFile)
             printlog( "History file created.")
             oFile.Close
        End If
        
        Set oFile = oFSO.OpenTextFile(strHistFile, ForAppending)
        oFile.WriteLine strD & "," & strMessage
        oFile.Close



        Set oFSO = Nothing
        
End Sub

Sub deletefile()
        Dim oFSO
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        If oFSO.FileExists(strDataFile) Then
             oFSO.DeleteFile(strDataFile)
             printlog ("Data file deleted.")
        End If
        Set oFSO = Nothing
End Sub



Sub sendmail()

    mymailserver = "smtpserver" 'Define a SMTP server here 
    recips = " MailboxReportRecipients@xxxxxxxxxx"
 
    sch = "http://schemas.microsoft.com/cdo/configuration/"; 
    Set cdoConfig = CreateObject("CDO.Configuration") 
    cdoConfig.Fields.Item(sch & "sendusing") = 2 
    cdoconfig.Fields.Item(sch & "smtpserver") = mymailserver
    cdoconfig.fields.update 
 
    Set cdoMessage = CreateObject("CDO.Message") 
    Set cdoMessage.Configuration = cdoConfig 
    cdomessage.From = "Administrator@xxxxxxxxxx"
    cdomessage.To = recips
    cdomessage.Subject = "Mailbox Report as of " & Now
    cdomessage.TextBody = "See file attached." & vbcrlf & vbcrlf 
    cdomessage.AddAttachment strDataFile
    cdomessage.Send 

    printlog ("Report emailed to " & recips)

   Set cdomessage = Nothing 
   Set cdoconfig = Nothing 

End Sub








________________________________________
From: Choughule, Sandeep IN BOM SISL [mailto:Sandeep.Choughule@xxxxxxxxxxx] 
Sent: 17 October 2005 07:30
Subject: Exchange Query

We using Exchange 2003, I want to create the report which includes the 
following points.
*       Users with Mailbox Greater than xxxMB 
*       Top User who can Sent / Receive mails with maximum mail size. 
Anybody know the query, which can be use to get details from Active Directory 
or from Exchange Server's So I can get the details or I have to do it manually 
one by one.  
Any suggestion appreciated.....

Thanks & Regards, 
Sandeep 

------------------------------------------------------------
The information contained in or attached to this email is intended only for the 
use of the individual or entity to which it is addressed. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are not authorised to and must not disclose, copy, distribute, 
or retain this message or any part of it. It may contain information which is 
confidential and/or covered by legal professional or other privilege (or other 
rules or laws with similar effect in jurisdictions outside England and Wales).

The views expressed in this email are not necessarily the views of Centrica 
plc, and the company, its directors, officers or employees make no 
representation or accept any liability for its accuracy or completeness unless 
expressly stated to the contrary.



Other related posts: