Mailbox Statistics Exchange 2007

raju1706
raju1706 used Ask the Experts™
on
In need a powershell query which can get the following details for users lastlogon > 60days

First Name,Last Name,Display Name,Alias, Name, Server, City, Database, Country/Region, Department,Hidden From Address Lists,Office,Phone,Postal Code,Primary SMTP Address,State Or Province,Storage Group,Recipient Type Details

I think Get-Mailbox, Get-Mailboxstastics and Get-User cmdelts have to be used.

Thanks in Advance

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
#        Name:  get-lastlogon.ps1
#      Author:  Eric Woodford - <a href="http://www.ericwoodford.com
#" title="www.ericwoodford.com
#">www.ericwoodford.com
#</a>        Date:  09/26/2007
#        Description:  Mailbox last logon information to single CSV file.
#              Display Name, Last Logon Time
#

#create header for CSV file.
$CSVFilePath = 'c:\mail_LastLogon.csv'
$strDate = get-date -uformat "%Y/%m/%d"
$strDate | out-file -filepath $CSVFilePath -encoding ascii
$strOutputString = "Display Name,LastLogonTime"
$strOutputString | out-file -filepath $CSVFilePath -encoding ascii -append

#set this to match your environment.
$Computers = get-qadComputer -searchRoot 'corp.ent/Member Servers/Exchange Servers'
foreach ($computer in $computers) {
        $users = Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer $computer.name | Select-Object MailBoxDisplayName, LastLogonTime
        # Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer $computer.name | Select-Object MailBoxDisplayName, LastLogonTime
        foreach ($user in $users) {
                   $date= [string] $user.LastLogonTime
                   if ($date.length -eq 0) {
                        $strOutputString = """"+ $user.MailBoxDisplayName + """, N/A"                          
                }
                else {
                        $strOutputString = """"+ $user.MailBoxDisplayName + """," + $date.substring(4,2)+"/"+$date.substring(6,2) +"/"+ $date.substring(0,4)
                }
           $strOutputString | out-file -filepath $CSVFilePath -encoding ascii -append
        }
}

http://www.ericwoodford.com/book/export/html/168
http://www.ericwoodford.com/powershell-script-2-getting-last-logon-date-exchange-mailboxes

Author

Commented:
@sekarc4u Thanks for the response. I cannot use Quest cmdlets.
Chris DentPowerShell Developer
Top Expert 2010

Commented:
Get-MailboxStatistics isn't too much help because it does not only show user logins on the mailbox. It shows any account that logs in, including system accounts.

Still, it can be done, it's just the first part is quite complex. I assume you want this out to a file?
# Filter to get users who have logged in within the last 60 days
$Date = (Get-Date).AddDays(-60).Date.ToFileTime()
$LdapFilter = "(&(objectClass=user)(objectCategory=person)(lastLogonTimeStamp>=$Date)(mail=*))"

$Searcher = New-Object DirectoryServices.DirectorySearcher($LdapFilter)
$Searcher.PageSize = 1000

$Searcher.FindAll() | ForEach-Object { $_.Properties["distinguishedname"][0] } | Get-User | ForEach-Object {
  $Mailbox = $_ | Get-Mailbox

  $_ | Select-Object FirstName, LastName, DisplayName, @{n='Alias';e={ $Mailbox.Alias }}, Name,
    @{n='Server';e={ $Mailbox.ServerName }}, City, @{n='Database';e={ $Mailbox.Database }}, 
    CountryOrRegion, Department, @{n='HiddenFromAddressLists';e={ $Mailbox.HiddenFromAddressListsEnabled }},
    Office, Phone, PostalCode, WindowsEmailAddress, StateOrProvince, RecipientTypeDetails
} | Export-Csv "YourOutput.csv" -NoTypeInformation

Open in new window

I have not exported StorageGroup, it's included in the Database field.

Chris
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
@Chris.

I am a beginner in powershell , if you could explain the script it would be  great.

Secondly can you also add Storage Limit Status to the script?

Thank You!!
PowerShell Developer
Top Expert 2010
Commented:
I've added a lot of comments which will, hopefully, go some way towards explaining what it's up to.
# Get the date, 60 days ago, in FileTime format (e.g. 129447936000000000)
$Date = (Get-Date).AddDays(-60).Date.ToFileTime()
# Create an LDAP Filter to pull users where the lastLogonTimeStamp value is no more than 60 days old
$LdapFilter = "(&(objectClass=user)(objectCategory=person)(lastLogonTimeStamp>=$Date)(mail=*))"

# Create a searcher (.NET class, for advanced searching. Quest's AD CmdLets would be easier)
$Searcher = New-Object DirectoryServices.DirectorySearcher($LdapFilter)
# Enable Paging so we can return more than 1000 results
$Searcher.PageSize = 1000

# Find the results, then feed them into Get-User and start looping through each
$Searcher.FindAll() | ForEach-Object { $_.Properties["distinguishedname"][0] } | Get-User | ForEach-Object {

  # Get the mailbox for the current user ($_)
  $Mailbox = $_ | Get-Mailbox
  # Get mailbox statistics for the current users mailbox ($Mailbox)
  $Statistics = $Mailbox | Get-MailboxStatistics

  # Start building output
  $_ | Select-Object FirstName, LastName, DisplayName, @{n='Alias';e={ $Mailbox.Alias }}, Name,
    @{n='Server';e={ $Mailbox.ServerName }}, City, @{n='Database';e={ $Mailbox.Database }}, 
    CountryOrRegion, Department, @{n='HiddenFromAddressLists';e={ $Mailbox.HiddenFromAddressListsEnabled }},
    Office, Phone, PostalCode, WindowsEmailAddress, StateOrProvince, RecipientTypeDetails,
    @{n='StorageLimitStatus';e={ $Statistics.StorageLimitStatus }}

# Export everything we've made to a file
} | Export-Csv "YourOutput.csv" -NoTypeInformation

Open in new window

When we build our output, we're using a bunch of fields we get back from Get-User, then we have to use Custom Properties (@{Name='SomePropertyName';Expression={ "Something to figure out the value" }}) to add in everything else we're after from Get-Mailbox and Get-MailboxStatistics. We have to do that to combine output from so many different things.

It's still going to be a bit arcane for anyone starting out in PS, it's not the simplest snippet in the world.

Chris

Author

Commented:
Excellent!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial