Challenges in retrieving health status from Office365

This topic contains 3 replies, has 3 voices, and was last updated by  Tom Brooks 3 weeks, 6 days ago.

  • Author
    Posts
  • #78148

    Deep
    Participant

    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]($detail.Messages).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

  • #78168

    Don Jones
    Keymaster

    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.

  • #78243

    Deep
    Participant

    Thanks a lot for the explanation Don..

  • #78246

    Tom Brooks
    Participant

    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: https://www.edbmails.com/pages/ost-to-pst-converter-for-ost-to-pst.html

You must be logged in to reply to this topic.