Challenges in retrieving health status from Office365

PowerShell Help
1

Hello guys,

I’m trying to fetch the health status report from office 365 portal.
I am able to retrieve all the required parameters but when I try to add “$Message” (which contains a detailed description of multiple lines) to a single cell in output csv file, I am not able to. As it is adding each line in a separate cell.
In short, each line in the “$Message” parameter is being added to a new cell & not on the same cell.
I developed the below script.
Can someone suggest me how I can get the details in a single cell?

Thanks in advance.
Let me know if any further explanation is required.

$path=Split-Path -parent $MyInvocation.MyCommand.Definition

$MyCred = Get-Credential

$jsonPayload = (@{userName=$MyCred.username;password=$MyCred.GetNetworkCredential().password;} | convertto-json).tostring()
$cookie = (invoke-restmethod -contenttype “application/json” -method Post -uri “https://api.admin.microsoftonline.com/shdtenantcommunications.svc/Register” -body $jsonPayload).RegistrationCookie
$jsonPayload = (@{lastCookie=$cookie;locale=“en-US”;preferredEventTypes=@(0,1)} | convertto-json).tostring()
$events = (invoke-restmethod -contenttype “application/json” -method Post -uri “https://api.admin.microsoftonline.com/shdtenantcommunications.svc/GetEvents” -body $jsonPayload)
$Issues = $events.Events

$outputFile =$path+“\O365_Health_status.csv”
#Check if output file is already present on the directory.
#Remove if it is exists
if (Test-Path $outputFile)
{
Remove-Item $outputFile
}

#Defining the output file headers
$output=“Title, ServiceName, status, starttime, EndTime, LastUpdatedTime, id, AffectedTenantCount, Message `n”
foreach($Issue in $Issues)
{

$detail = $Issue
$ServiceName = ($detail.AffectedServiceHealthStatus).ServiceName
$Title = $detail.Title
$status = $detail.Status
$starttime = $detail.StartTime
$Message =String.MessageText
$AffectedTenantCount = $detail.AffectedTenantCount
$EndTime =$detail.EndTime
$id = $detail.Id
$LastUpdatedTime = $detail.LastUpdatedTime

$output = $output+$Title+“,”+$ServiceName+“,”+$status+“,”+$starttime+“,”+$EndTime+“,”+$LastUpdatedTime+“,”+$id+“,”+$AffectedTenantCount+“,”+$Message+“`n”
Clear-Variable Title, ServiceName, status, starttime, EndTime, LastUpdatedTime, id, AffectedTenantCount, Message
}

Add-Content $outputFile $output

Write-Host "Script execution completed. The output file is saved at " $path -ForegroundColor Green

2

So, for one, you’re really working a helluva lot harder than you need to. PowerShell is perfectly capable of making CSV files for you - you don’t need to do it yourself. You just need to emit a PSCustomObject with the properties you want, and Export-CSV will make CSV for you.

function X {
 Param($issues)
foreach($Issue in $Issues)
{
$detail = $issue # not sure why you needed a new variable $detail here?
$props = @{ServiceName = ($detail.AffectedServiceHealthStatus).ServiceName
Title = $detail.Title
status = $detail.Status
starttime = $detail.StartTime
Message =[String]($detail.Messages).MessageText
AffectedTenantCount = $detail.AffectedTenantCount
EndTime =$detail.EndTime
$id = $detail.Id
LastUpdatedTime = $detail.LastUpdatedTime}
[pscustomobject]$props
}
}

# do whatever you do to get $issues
x $issues | Export-CSV filename.csv

Something like that. But bear in mind that CSV files aren’t meant to contain giant multi-line fields, especially if they’ll contain double quotes (which CSV would normally use to delimit a field) or commas. So you may need to “massage” the message first to remove commas, quotes, and newline characters.

3

Thanks a lot for the explanation Don…

4

For the large bunch of Outlook PST files, You have to go for some OST to PST Recovery Tool. If you have no idea or still confuse which one to go for, then you may try EdbMails OST TO PST Converter. The OST to PST Converter Tool exhibit some of the unmatchable features.

Some of its Features are

  • Repairs Corrupted OST files
  • Ultra-fast export of mails from OST to PST
  • Allow Saving of OST files in different Format

For demo version visit: Recover corrupt and damaged OST files