Exchange PowerShell Table Null-Valued Expression

I am trying to create a script that will output certain information and am running into an issue receiving the following error "You cannot call a method on a null-valued expression."  I know why I am getting this message but don't know how to resolved this.  Here is the part of the script that I'm working on:

$table = New-Object system.data.datatable("QuotaReport")
$col1 = New-Object system.data.datacolumn DisplayName, ([string])
$col2 = New-Object system.data.datacolumn Alias, ([string])
$col3 = New-Object system.data.datacolumn TotalItemSize, ([string])
$col4 = New-Object system.data.datacolumn ProhibitSendQuota, ([string])
$col5 = New-Object system.data.datacolumn ItemCount, ([string])
$col6 = New-Object system.data.datacolumn StorageLimitStatus, ([string])
$col7 = New-Object system.data.datacolumn LastLogonTime, ([string])
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
$table.columns.add($col4)
$table.columns.add($col5)
$table.columns.add($col6)
$table.columns.add($col7)

foreach ($mb in (Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | Where-Object {$_.StorageLimitStatus -eq "ProhibitSend" -or $_.StorageLimitStatus -eq "IssueWarning"}))
    {
    $row = $table.newrow()
    $row.Displayname = $mb.DisplayName
    $row.Alias = $mb.Alias
    $row.TotalItemSize = $mb.TotalItemSize.value.toMB()
    $row.ProhibitSendQuota = $mb.ProhibitSendQuota.value.toMB()
    $row.ItemCount = $mb.ItemCount
    $row.StorageLimitStatus = $mb.StorageLimitStatus
    $row.LastLogonTime = $mb.LastLogonTime
    $table.rows.add($row)
    }

Attached is partial output for $table as can be seen there are no values for Alias or ProhibitSendQuota as these are parameters of Get-Mailbox and not present in Get-Mailbox Statistics

I need to take the results of $table and then somehow get data from "Get-mailbox" adding to this table into the appropriate columns  (#2 and #4)

If I am going about this the wrong way I would appreciate being corrected.
DailyQuota.txt
winsystemsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris DentPowerShell DeveloperCommented:
Good morning,

This is one possible way of working around that problem.

$Mailboxes = Get-Mailbox -ResultSize Unlimited
$Mailboxes | ForEach-Object {
  $Mailbox = $_
  $Statistics = $Mailbox | Get-MailboxStatistics

  if ($Statistics.StorageLimitStatus -eq 'ProhibitSend' -or $Statistics.StorageLimitStatus -eq 'IssueWarning') {
    $Statistics | Select-Object DisplayName, 
      @{n='Alias';e={ $Mailbox.Alias }},
      @{n='TotalItemSize';e={ $_.TotalItemSize.Value.ToMb() }},
      @{n='ProhibitSendQuota';e={
        if (-not ($Mailbox.ProhibitSendQuota.IsUnlimited)) {
          $Mailbox.ProhibitSendQuota.Value.ToMb()
        } }},
      ItemCount,
      StorageLimitStatus,
      LastLogonTime,
      LastLoggedOnUserAccount
  }
}

Open in new window


HTH

Chris
0
winsystemsAuthor Commented:
While this does give desired output to screen it is not in a format I wish which is why I was trying to work with a table.  $Mailbox and $Statistics only have the last entry processed.  $Mailboxes, of course, has all the mailboxes.   I tried changing Line 4 to:
 
$Statistics = $Mailbox | Get-MailboxStatistics | Export.Csv C:\Temp\QuotaList.csv -NoType

However I only get the last mailbox processed  If you can offer a way to export this information I would be appreciative.
0
Chris DentPowerShell DeveloperCommented:
So you could convert back to the table structure you were using, something like this:
$table = New-Object system.data.datatable("QuotaReport")
$col1 = New-Object system.data.datacolumn DisplayName, ([string])
$col2 = New-Object system.data.datacolumn Alias, ([string])
$col3 = New-Object system.data.datacolumn TotalItemSize, ([string])
$col4 = New-Object system.data.datacolumn ProhibitSendQuota, ([string])
$col5 = New-Object system.data.datacolumn ItemCount, ([string])
$col6 = New-Object system.data.datacolumn StorageLimitStatus, ([string])
$col7 = New-Object system.data.datacolumn LastLogonTime, ([string])
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
$table.columns.add($col4)
$table.columns.add($col5)
$table.columns.add($col6)
$table.columns.add($col7)

$Mailboxes = Get-Mailbox -ResultSize Unlimited
$Mailboxes | ForEach-Object {
  $Mailbox = $_
  $Statistics = $Mailbox | Get-MailboxStatistics

  if ($Statistics.StorageLimitStatus -eq 'ProhibitSend' -or $Statistics.StorageLimitStatus -eq 'IssueWarning') {

    $row = $table.newrow()

    $row.Displayname = $Statistics.DisplayName
    $row.Alias = $Mailbox.Alias
    $row.TotalItemSize = $Statistics.TotalItemSize.value.toMB()
    $row.ProhibitSendQuota = $$Mailbox.ProhibitSendQuota.value.toMB()
    $row.ItemCount = $Statistics.ItemCount
    $row.StorageLimitStatus = $Statistics.StorageLimitStatus
    $row.LastLogonTime = $Statistics.LastLogonTime
    $table.rows.add($row)
  }
}

Open in new window

But if your goal is to export to CSV anyway then the snippet above simply needs Export-Csv moved a little:
$Mailboxes = Get-Mailbox -ResultSize Unlimited
$Mailboxes | ForEach-Object {
  $Mailbox = $_
  $Statistics = $Mailbox | Get-MailboxStatistics

  if ($Statistics.StorageLimitStatus -eq 'ProhibitSend' -or $Statistics.StorageLimitStatus -eq 'IssueWarning') {
    $Statistics | Select-Object DisplayName, 
      @{n='Alias';e={ $Mailbox.Alias }},
      @{n='TotalItemSize';e={ $_.TotalItemSize.Value.ToMb() }},
      @{n='ProhibitSendQuota';e={
        if (-not ($Mailbox.ProhibitSendQuota.IsUnlimited)) {
          $Mailbox.ProhibitSendQuota.Value.ToMb()
        } }},
      ItemCount,
      StorageLimitStatus,
      LastLogonTime,
      LastLoggedOnUserAccount
  }
} | Export-Csv C:\Temp\QuotaList.csv -NoType

Open in new window

That will export each of the properties we picked using the Select-Object command above. Export-Csv must be at the end because there's no way of forcing it to Append to an existing CSV file.

The syntax for the custom fields in Select-Object above is:

@{Name='ThePropertyName';Expression={ A Script Block to calculate the value }}

Name and Expression are truncated to n and e as short-hand.

There are quite a few ways to generate object output if you find that syntax frustrating, this is another alternative:
$Mailboxes = Get-Mailbox -ResultSize Unlimited
$Mailboxes | ForEach-Object {
  $Mailbox = $_
  $Statistics = $Mailbox | Get-MailboxStatistics

  if ($Statistics.StorageLimitStatus -eq 'ProhibitSend' -or $Statistics.StorageLimitStatus -eq 'IssueWarning') {
    New-Object PsObject -Property @{
      DisplayName             = $Statistics.DisplayName;
      Alias                   = $Mailbox.Alias;
      TotalItemSize           = $Statistics.TotalItemSize.Value.ToMb();
      ProhibitSendQuota       = $(
        if (-not ($Mailbox.ProhibitSendQuota.IsUnlimited)) {
          $Mailbox.ProhibitSendQuota.Value.ToMb()
        });
      ItemCount               = $Statistics.ItemCount;
      StorageLimitStatus      = $Statistics.StorageLimitStatus;
      LastLogonTime           = $Statistics.LastLogonTime;
      LastLoggedOnUserAccount = $Statistics.LastLoggedOnUserAccount
    }
  }
} | Export-Csv C:\Temp\QuotaList.csv -NoType

Open in new window

The main disadvantage with this method (under PowerShell 2) is that you fix the column order. That's fixed in PowerShell 3 where this extended syntax is introduced:
New-Object PsObject -Property ([Ordered]@{
  Property1 = "Value1";
  Property2  = "Value2";
})

Open in new window

Chris
0
Redefine Your Security with AI & Machine Learning

The implications of AI and machine learning in cyber security are massive and constantly growing, creating both efficiencies and new challenges across the board. Check out our on-demand webinar to learn more about how AI can help your organization!

winsystemsAuthor Commented:
Thank you for all of the information.

My ultimate goal would be to probably format in HTML and send this via an email to our support people daily telling them who has received Quota Warnings and who is "over the limit" and cannot send.  I haven't worked out all the details in this yet but like to try and do as much as possible myself learning as I go.

Any opinions as to which would be a better output Table vs. CSV to achieve this goal?
0
winsystemsAuthor Commented:
Oh I forgot ... I wish to sort by Display name which is also why I am thinking table over CSV
0
Chris DentPowerShell DeveloperCommented:
Oh okay, you can sort in PowerShell though. This is especially useful if you want HTML in the end.

I'll stick with developing this snippet for now:
$Mailboxes = Get-Mailbox -ResultSize Unlimited
$Mailboxes | ForEach-Object {
  $Mailbox = $_
  $Statistics = $Mailbox | Get-MailboxStatistics

  if ($Statistics.StorageLimitStatus -eq 'ProhibitSend' -or $Statistics.StorageLimitStatus -eq 'IssueWarning') {
    $Statistics | Select-Object DisplayName, 
      @{n='Alias';e={ $Mailbox.Alias }},
      @{n='TotalItemSize';e={ $_.TotalItemSize.Value.ToMb() }},
      @{n='ProhibitSendQuota';e={
        if (-not ($Mailbox.ProhibitSendQuota.IsUnlimited)) {
          $Mailbox.ProhibitSendQuota.Value.ToMb()
        } }},
      ItemCount,
      StorageLimitStatus,
      LastLogonTime,
      LastLoggedOnUserAccount
  }
} | Sort-Object DisplayName

Open in new window

We know we can still Export to CSV by adding "| Export-Csv <File>" after Sort-Object has run. We can also convert directly to HTML (in the same place):
...
} | Sort-Object DisplayName | ConvertTo-Html

Open in new window

It'll be pretty raw, but it can be made very pretty if you implement a bit of CSS. If I steal one from one of my other scripts, we can do this:
$HtmlHead = "
<title>Mailbox report</title>
<style type='text/css'>
    body {
      font-size: 10pt;
      font-family: sans-serif;
      padding: 0px; 
      margin: 0px; 
      overflow: auto;
    }

    table        { width: 100%; border-collapse: collapse }
    a            { color: #717D7D; Display: block; }
    a.top        { color: #717D7D; Display: block; font-size: 8pt; }
    td.left      { background-color: white; width: 100px; text-align: left; }
    td.right     { background-color: white; width: 50px; text-align: right; }
    td, th       { color: black; padding: 2px; }
    th           { background-color: #C7C7C7; text-align: left }
    td           { background-color: #F7F7F7; text-align: left }
</style>"

$Mailboxes = Get-Mailbox -ResultSize Unlimited
$Mailboxes | ForEach-Object {
  $Mailbox = $_
  $Statistics = $Mailbox | Get-MailboxStatistics

  if ($Statistics.StorageLimitStatus -eq 'ProhibitSend' -or $Statistics.StorageLimitStatus -eq 'IssueWarning') {
    $Statistics | Select-Object DisplayName, 
      @{n='Alias';e={ $Mailbox.Alias }},
      @{n='TotalItemSize';e={ $_.TotalItemSize.Value.ToMb() }},
      @{n='ProhibitSendQuota';e={
        if (-not ($Mailbox.ProhibitSendQuota.IsUnlimited)) {
          $Mailbox.ProhibitSendQuota.Value.ToMb()
        } }},
      ItemCount,
      StorageLimitStatus,
      LastLogonTime,
      LastLoggedOnUserAccount
  }
} | Sort-Object DisplayName | ConvertTo-Html -Head $HtmlHead

Open in new window

It is entirely possible to redirect that to a file (Out-File), or it could be used as the body of an Email (Send-MailMessage -BodyAsHtml -Body $HtmlReport, see below).
$HtmlHead = "
<title>Mailbox report</title>
<style type='text/css'>
    body {
      font-size: 10pt;
      font-family: sans-serif;
      padding: 0px; 
      margin: 0px; 
      overflow: auto;
    }

    table        { width: 100%; border-collapse: collapse }
    a            { color: #717D7D; Display: block; }
    a.top        { color: #717D7D; Display: block; font-size: 8pt; }
    td.left      { background-color: white; width: 100px; text-align: left; }
    td.right     { background-color: white; width: 50px; text-align: right; }
    td, th       { color: black; padding: 2px; }
    th           { background-color: #C7C7C7; text-align: left }
    td           { background-color: #F7F7F7; text-align: left }
</style>"

$Mailboxes = Get-Mailbox -ResultSize Unlimited
$HtmlReport = $Mailboxes | ForEach-Object {
  $Mailbox = $_
  $Statistics = $Mailbox | Get-MailboxStatistics

  if ($Statistics.StorageLimitStatus -eq 'ProhibitSend' -or $Statistics.StorageLimitStatus -eq 'IssueWarning') {
    $Statistics | Select-Object DisplayName, 
      @{n='Alias';e={ $Mailbox.Alias }},
      @{n='TotalItemSize';e={ $_.TotalItemSize.Value.ToMb() }},
      @{n='ProhibitSendQuota';e={
        if (-not ($Mailbox.ProhibitSendQuota.IsUnlimited)) {
          $Mailbox.ProhibitSendQuota.Value.ToMb()
        } }},
      ItemCount,
      StorageLimitStatus,
      LastLogonTime,
      LastLoggedOnUserAccount
  }
} | Sort-Object DisplayName | ConvertTo-Html -Head $HtmlHead

Send-MailMessage -BodyAsHtml -Body $HtmlReport -To you@domain.example -From you@domain.example -SmtpServer mailserver -Subject "Mailbox report"

Open in new window

PowerShell is quite lovely for it's flexibility :)

Chris
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Exchange

From novice to tech pro — start learning today.