Export Results to e-mail/ OR in CSV format to e-mail

Question

_{Thursday, February 23, 2017 12:15 PM}

Hi guys,

I have a script I'd like to send in the body of an e-mail/ or send as an attachment in CSV form. The script reports users with NO litigation hold enabled.

$admin = '[email protected]'

$password = Get-Content "C:\O365\[email protected]" | ConvertTo-SecureString
$cred = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $admin,$password
   
$exchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $cred -Authentication Basic –AllowRedirection -ErrorAction Stop
Import-PSSession $exchangeSession –AllowClobber -ErrorAction Stop
      
#Get a list of all users that have E3 licenses applied but with Litigation Hold NOT enabled
Get-Mailbox -RecipientTypeDetails UserMailbox -Filter {PersistedCapabilities -eq "BPOS_S_Enterprise" -and LitigationHoldEnabled -ne $true}

Remove-PSSession $exchangeSession

Any help would be appreciated.

thanks - travis

All replies (9)

_{Thursday, February 23, 2017 2:25 PM ✅Answered}

Forgot a "|" in front of the select, sorry. Add that and it should work (I'll update my example)

There's no place like 127.0.0.1

_{Thursday, February 23, 2017 12:31 PM}

Use  export-csv after get-mailbox without the 'FL'  and then use send-mailmessage

Get-Mailbox | Where {$_.LitigationHoldEnabled -match "False"} | export-csv c:\test.csv 

send-mailmessage -from "[email protected]" -to "[email protected]", "[email protected]" -bcc [email protected] -subject "Hello World" -body "See attachment below." -Attachment "c:\test.csv" -smtpServer smtp.example.com

Cheers,

Ruud
Twitter:    Blog: AzureStack.Blog  LinkedIn:    
Note: Please “Vote As Helpful” if you find my contribution useful or “Mark As Answer” if it does answer your question. That will encourage me - and others - to take time out to help you.

_{Thursday, February 23, 2017 12:33 PM}

Hi Travis,

well ... first of all:

• Remove the "fl" alias completely. Format-Commands destroy data.

You can export to csv by using ... Export-Csv. You can convert to html by using ... ConvertTo-Html.

You can send mails by using Send-MailMessage, which has an "-Attachments" and a "-BodyAsHtml" parameter.

Cheers and good luck,
Fred

There's no place like 127.0.0.1

_{Thursday, February 23, 2017 12:52 PM}

thanks guys -

If I want to export data for those who DO have Litigation Hold ENABLED. The CSV file generates a whole lot of columns which are of no use. Can you tell me how to export only those columns I'm interested in?

i.e. Columns DisplayName/E-mail Address/Litigation Hold

Here is the script I have now which works fine, just want to be able to refine results:

$admin = '[email protected]'

$password = Get-Content "C:\O365\[email protected]" | ConvertTo-SecureString
$cred = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $admin,$password
   
$exchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $cred -Authentication Basic –AllowRedirection -ErrorAction Stop
Import-PSSession $exchangeSession –AllowClobber -ErrorAction Stop

Get-Mailbox | Where {$_.LitigationHoldEnabled -match "true"} | export-csv c:\temp\test.csv

send-mailmessage -from "" -to "--" -subject "Hello World" -body "See attachment below." -Attachment "c:\temp\test.csv" -smtpServer --

Remove-PSSession $exchangeSession

_{Thursday, February 23, 2017 1:19 PM}

You can "select" them:

Get-Mailbox | Where {$_.LitigationHoldEnabled -match "true"} | Select Column1, Column2, Column3 | export-csv c:\temp\test.csv

Just replace the column names as needed.

Cheers,
Fred

There's no place like 127.0.0.1

_{Thursday, February 23, 2017 1:51 PM}

Hi Fred,

I amended this line: Get-Mailbox | Where {$_.LitigationHoldEnabled -match "true"} select 'WindowsEmailAddress', 'LitigationHoldEnabled' | export-csv c:\temp\test.csv

But when I run script I get error:

Where-Object : A positional parameter cannot be found that accepts argument 'Select'.
At \\ls_$\

• ... t-Mailbox | Where {$_.LitigationHoldEnabled -match "true"} Select 'Wi ...
  +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : InvalidArgument: (:) [Where-Object], ParameterBindingException
      + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.WhereObjectCommand

_{Friday, February 24, 2017 9:31 AM}

hi fred,

what does the -ne command mean?

_{Friday, February 24, 2017 9:40 AM}

hi fred,

how about if i wanted to add the results to the body of the e-mail as opposed to output csv.

_{Friday, February 24, 2017 12:48 PM}

Hi Travis,

1) -ne

The "-ne" operator is used for comparisons and stands for "Not Equal". Example:

dir C:\ | ? { $_.Name -ne "Windows" }

This will return all objects in the path C:\ whose name is not equal to "Windows".

2) Mail body

You can - instead of writing to file - convert the objects to html (using ConvertTo-Html).

When using Send-MailMessage you can then use the output of this as parameter for "Body" and tell the command to treat the body as html (which after all it is):

$body = Get-Mailbox | Where {$_.LitigationHoldEnabled -match "true"} | Select Column1, Column2, Column3 | ConvertTo-html

send-mailmessage -Body $body -BodyAsHtml # Add other parameters for sending mail

Cheers,
Fred

There's no place like 127.0.0.1