PowerShell Script : Office 365 Mailbox Sizes and Mailbox Quotas Email Reporting.

 

We all love office 365.

Especially I like the Exchange Online most.

On Exchange Online when we create any new mailboxes we do set the various storage limits on the user mailbox and we assign different mailbox size as per the user requirements or company policies.

Sometime, we need to know the reports of:

1.     How much mailbox space is assigned to the user?

2.     How much his currently mail box usage is.

3.     What is the name of mailbox database?

Monitoring and Reporting are the two key main areas in our IT industry. We should monitor our IT stuff and we should often generate and view the reports.

I have created this script to know about the mailbox status of my all Office 365 Exchange Online mailboxes and the current usage of them.  It also contains the information on, Users Last Login time , Log Off time, size of this issue warning quota limit and size of total deleted items.

How to run the script?

It’s simple.

1.     Configure, your SMTP server, TO, FROM, Subject filed in the script.

2.     Run the script

3.     It ask you for your Office 365 Administrator Credentials.

4.     After providing credential, it will send an HTML email to the provided TO email id.

That’s all J Simple. Isnt?

 

You can download the script from Technet Gallery : http://gallery.technet.microsoft.com/scriptcenter/Office-365-Mailbox-Sizes-92b94563

 

#==================| Satnaam Waheguru Ji |===============================   
#              
#            Author  :  Aman Dhally    
#            E-Mail  :  amandhally@gmail.com    
#            website :  www.amandhally.net    
#            twitter :   @AmanDhally    
#            blog    : http://newdelhipowershellusergroup.blogspot.in/   
#            facebook: http://www.facebook.com/groups/254997707860848/    
#            Linkedin: http://www.linkedin.com/profile/view?id=23651495    
#    
#            Creation Date    : 03-June-2014 
#            File    :          Email Reporting on Office 365 mail Statictic 
#           
#            Version : 3    
#              
#   
#            My Pet Spider :          /^(o.o)^\     
#  
 
#------------------------------------------------------------------------------------------------------------  
 
 
function Send-O365MailStats { 
     
 
     
begin { 
 
    try { 
            Write-Output "$(get-date) : Script Start." 
            Write-Output "$(get-date) : Asking for Office365 Administrative Credentials." 
            $UserCredential = Get-Credential 
            Write-Output "$(get-date) : Creating a Online PS Session with Office 365." 
            $ouSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection -ErrorAction 'Stop' -ErrorVariable 'ConnectionError' 
            Write-Output "$(get-date) : Importing PS Session." 
            Import-PSSession $ouSession -AllowClobber -ErrorAction 'Stop' -ErrorVariable 'SessionError' 
        } 
     
    catch { 
            $ConnectionError 
            $SessionError 
         
        } 
    } 
     
 
process { 
     
    try { 
        #Your SMTP sever 
        $smtp = 'YOUR EMAIL SERVER' 
        # To E-Mail Address 
        $to = 'AMANdhally@gmail.com' 
        # From E-Mail Address 
        $from = 'Scripter@YourDOMAIN.COM' 
        # Subject of the Email 
        $subject = 'Office 365 MailBox Stats Report' 
        #Empty Array String 
        $body2 = @() 
        #Just a Counter 
        $int = 1 
        #All Magic Done here 
        Write-Output "$(get-date) : Running the Get-Mailbox and Get-MailboxStatistics cmdlet" 
        $userList = Get-Mailbox -Filter " RecipientType -eq 'UserMailbox'"   | Get-MailboxStatistics  
         
        #Processing 
        Write-Output "$(get-date) : Processing the Results." 
        foreach ( $user in $userList) 
                { 
                   #$user | fl * -Force 
 
                   $body2 += "<tr>" 
                   $body2 += "<td>" + $int++ + "</td>"  
                   $body2 += "<td>" + $user.DisplayName + "</td>" 
                    
                   #manuplating  
                   $UserTa= $user.TotalItemSize.value.ToString().split(" ")[0] 
                   $UserTb = $user.TotalItemSize.value.ToString().split(" ")[1]  
                   $userTotalSize =    $UserTa +  " " + $UserTb 
 
                   $body2 += "<td>" + $userTotalSize + "</td>" 
                    
                   $body2 += "<td>" + $user.LastLogonTime + "</td>" 
                   $body2 += "<td>" + $user.LastLogoffTime + "</td>" 
                   $body2 += "<td>" + $user.ServerName + "</td>" 
                   $body2 += "<td>" + $user.DatabaseName + "</td>" 
 
                   #Manuplating  
                   $userDDLa = $user.TotalDeletedItemSize.value.ToString().split(" ")[0] 
                   $userDDLb = $user.TotalDeletedItemSize.value.ToString().split(" ")[1] 
                   $userDelted =   $userDDLa + " " +   $userDDLb 
 
                   $body2 += "<td>" + $userDelted + "</td>" 
 
                   #Manuplated 
                   $userDBa = $user.DatabaseProhibitSendReceiveQuota.value.ToString().split(" ")[0] 
                   $userDBb = $user.DatabaseProhibitSendReceiveQuota.value.ToString().split(" ")[1] 
                   $userDBQuota = $userDBa + " " + $userDBb 
                    
                   $body2 += "<td>" + $userDBQuota + "</td>" 
 
                   #Manuplating 
                   $userWQa = $user.DatabaseIssueWarningQuota.value.ToString().split(" ")[0] 
                   $userWQb = $user.DatabaseIssueWarningQuota.value.ToString().split(" ")[1] 
                   $userWquota =  $userWQa + " " + $userWQb 
                    
                   $body2 += "<td>" + $userWquota+ "</td>" 
 
                   #Manuplating 
                   $UserDBPa = $user.DatabaseProhibitSendQuota.value.ToString().split(" ")[0] 
                   $UserDBPb = $user.DatabaseProhibitSendQuota.value.ToString().split(" ")[1] 
                   $userDBPS = $UserDBPa + " " + $UserDBPb 
 
                   $body2 += "<td>" + $userDBPS + "</td>" 
                   $body2 += "</tr>" 
                     
                  
                } 
         
            $body = "<h3>Office 365 {Exchnage Online}, User Mailbox Statics Report.</h3>" 
            $body += "<br>" 
            $body += "<br>" 
            $body += "<table border=2 style=background-color:silver;border-color:black >" 
            $body += "<tr>" 
            $body +=  "<th>S. No</th>" 
            $body +=  "<th>Display Name</th>" 
            $body +=  "<th>Total Mailbox Size </th>" 
            $body +=  "<th>Last LogonTime</th>" 
            $body +=  "<th>Last Logoff</th>" 
            $body +=  "<th>Server Name </th>" 
            $body +=  "<th>Database Name</th>" 
            $body +=  "<th>Deleted Item Size</th>" 
            $body +=  "<th>Prohibit S/R Quota</th>" 
            $body +=  "<th>Issue Warning Quota </th>" 
            $body +=  "<th>Prohibit Send Quota</th>" 
            $body += "</tr>" 
            $body += $body2 
            $body += "</table>" 
         
        #Sending Email Message to the $to 
        Write-Output "$(get-date) : Sending the Email." 
        Send-MailMessage -SmtpServer $smtp -To $to   -From $from -Subject $subject -Body $body  -BodyAsHtml -UseSsl 
             
        } 
     
    catch  
        { 
         
        } 
     
     
} 
     
 
end { 
         
    try { 
         
        #Remvoing the Session with Office 365 
        Write-Output "$(get-date) : Removing PS Session." 
        Remove-PSSession $ouSession  
        Write-Output "$(get-date) : Script End." 
        } 
         
    catch { 
        } 
    } 
} 
 
Send-O365MailStats 
 
# end of the script. 

The output should be look like the below screenshot.

You can download the script from Technet Gallery : http://gallery.technet.microsoft.com/scriptcenter/Office-365-Mailbox-Sizes-92b94563

Regards

Aman Dhally

Come and join my journey of : “100 Days of Self Improvement” on

 

Facebook: https://www.facebook.com/100DoSI 

If you like, you can follow me on Twitter and Facebook. You can also check my “You Tube” channel for PowerShell video tutorials. You can download all of my scripts from “Microsoft TechNet Gallery”.

Powershell and MS Office : Setting User Initials in MS Office using Powershell.

 
Hi,

I hope you all are doing fine and enjoying my blog post.

I think we covered lots of things from setting wallpapers using Powershell to the generating reports from Active Directory, and this shows how powerful Powershell is and how we can use it in our various task to automate.

I simply love Powershell, not because it is Powerful but because it is one of (I want to say only) scripting language available, which is easy to learn and easy to use.

Ok, now lets discuss about my today’s task.

If in your company users are heavily using Word, Excel, PowerPoint, then you are aware from how, valuable are comments in.

To add a comment, you have to click on “Review ” tab and need to click on “New Comment” icon.
So now you may be ask, what is the big deal?

Nice question, when few users, or any team , works on a single file, and when they add comments in to the file, the comments are shown as “User Initials”.

So, what if your “User Initials” in Ms Office are wrong, then no one knows who commented an who added the notes.

Download link : http://gallery.technet.microsoft.com/scriptcenter/Fix-Users-Initial-in-35c2801b



you can see my user initials are set wrong. and when i add comments they are coming as “SAM” rather then “AD”.

This is a problem, for example, if you have 100+ user or so and incase few one’s user initials are wrong, how you are going to fix it?

Manually? Yes, you can, but, that is not a good option for scripters.

Powershell Script? Awesome, let see how we are do this.

This user initials for Ms Office application are stored in, 

“HKCU:\Software\Microsoft\Office\Common\UserInfo” in key “UserInitials”.

In this script.We are going to set this key using “$env:USERNAME” environment variable.

Here is the script code.

# stroing $env:Username in to  a Username variable $userName =  $env:USERNAME

# Checking the current  Initials in the registry and storing in a variable $oldInitials = (Get-ItemProperty -Path HKCU:\Software\Microsoft\Office\Common\UserInfo).UserInitials

# spliting username # if your username had space you can use => $split = $username.Split() $split = $username.Split(".")

# joining spiting username and getting a first words only switch ($split.Length)               {                      0      { Write-Warning "Not able to find username." ; break         }                      1      { $newUserInitials =  $split[0].Remove(1)   ; break    }                      2      { $newUserInitials =  $split[0].Remove(1) + $split[1].Remove(1)   ; break     }                      3      { $newUserInitials =  $split[0].Remove(1) + $split[1].Remove(1)  + $split[3].Remove(1)    ; break  }                      4      { $newUserInitials =  $split[0].Remove(1) + $split[1].Remove(1)  + $split[3].Remove(1)  +  $split[4].Remove(1)   ; break  }                      5      { $newUserInitials =  $split[0].Remove(1) + $split[1].Remove(1)  + $split[3].Remove(1)  +  $split[4].Remove(1)  + $split[5].Remove(1) ; break  }               }

# if old user intitails not matched with Our one if ( $oldInitials -ne $newUserInitials )        {        set-ItemProperty -Path HKCU:\Software\Microsoft\Office\Common\UserInfo -Name UserInitials -Value $newUserInitials -Force        Write-Host "User Initials Fixed" -ForegroundColor 'Green'        } else        {        Write-Host "User Initials matched."        }

In above screenshot, you have seen that my user initials are set wrong in MS Office.

Now lets run the script and see and if this fix it. , Make sure that you all Office app are closed .

I run the script and and it is showing me that it fixed it.



Now open any office app and see if this really fixed it.

Seems good.

 

now add some comments.

Yaay.!!!!!. it fixed.



Download link : http://gallery.technet.microsoft.com/scriptcenter/Fix-Users-Initial-in-35c2801b

Thanks

Aman Dhally