locked
System.Object[] - Export to CSV or HTML RRS feed

  • Question

  • Hi Team,

    am trying to get the service information from office 365 through the below script. However , am unable to get the exact value in the column "Messages"

    $cred = get-credential

    #$a = "<style>BODY{background-color:peachpuff;}</style>"
    $a = "<style>"
    $a = $a + "BODY{background-color:peachpuff;}"
    $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
    $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
    $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:PaleGoldenrod}"
    $a = $a + "</style>"
    $jsonPayload = (@{userName=$cred.username;password=$cred.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)
    $events | get-member
    $events.Events  
    $events.Events | Select-object StartTime, Title, EndTime, LastUpdatedTime, Id, Status, Messages | Export-Csv "D:\ramki\servicestat1.Csv" | Out-String

    Please see the screen shot.

    

    Out put in the text file is coming in the below format. but i need to export in CSV / HTM

    Monday, November 6, 2017 4:17 PM

Answers

  • Hi rush2ramki,

    To resolve the “system.object[]” issue, there is a PowerShell script demo below you could refer to.

      $cred = get-credential
    
    
     #$a = "<style>BODY{background-color:peachpuff;}</style>"
    
     $a = "<style>"
    
     $a = $a + "BODY{background-color:peachpuff;}"
    
     $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
    
     $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
    
     $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:PaleGoldenrod}"
    
     $a = $a + "</style>"
    
     $jsonPayload = (@{userName=$cred.username;password=$cred.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)
    
     $events | get-member
    
     $events.Events  
    
     Function GetMessage()
    
     {
    
     $text="";
    
     foreach($message in $($_).Messages)
    
     { 
    
     $text=$message.MessageText;
    
     }
    
     return $text;
    
     }
    
     
    
     $events.Events | Select-object StartTime, Title, EndTime, LastUpdatedTime, Id, Status, @{Name="Messages";Expression={GetMessage}}  | ConvertTo-html -head $a -As TABLE |Out-File "C:\test\ser9.html" 

    The result as below:

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    Tuesday, November 28, 2017 1:22 AM

All replies

  • Hi rush2ramki,

    You could change “Export-Csv” as “Out-File”.

    Per my test in my sharepoint online environment, if I use Export-Csv to export the information to csv file, I received the same issue as yours. System.Object[] is in the message column.

    If I change to Out-File to export information to csv file, there is information not System.Object[] in the message column.

    More reference:

    Using the Out-File Cmdlet.

    https://technet.microsoft.com/en-us/library/ee176924.aspx

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, November 7, 2017 3:40 AM
  • Hello Sara 

    Thanks for your help.

    Two things i observed, when i put the command like for CSV below

    $events.Events | Format-Table StartTime, Title, EndTime, LastUpdatedTime, Id, Status, Messages -wrap| out-file "D:\ramki\servicestat2.Csv"

    The System.Object[] is not appeared , and got the value exactly, only if i open in the TXT file / Notepad  . if i open the same CSV file in Excell , the format got collapsed like below

    When i try to get in HTLM file with below commad

    $events.Events | Format-Table  StartTime, Title, EndTime, LastUpdatedTime, Id, Status, Messages -Wrap| Out-File D:\ramki\Test.htm

    System.Object[] is appeared in the Messages column. Please see the below. i would the result in HTM format instead of CSV, which is more convenient to go through the results in email as well

     

    Thanks in Advance and am also trying with other commands . Please see the below exact results in TXT file (Opened the CSV in Notepad). See the messages column, it has lot of results like user impact, More info , {@{MessageText Title =....etc) i suspect, the multiple values in messages creating the issues.

    In the message column, i would like to get the first line value {@{MessageText = Title: 

    nothing more than that.

    i would like to get the below result in HTML format.


    • Edited by rush2ramki Tuesday, November 7, 2017 5:54 AM Added
    Tuesday, November 7, 2017 5:43 AM
  • Hi rush2ramki,

    We can expand property for Messages and then export to html file like the code below:

    $events.Events | Select-object -ExpandProperty Messages -ErrorAction SilentlyContinue| Format-Table StartTime, Title, EndTime, LastUpdatedTime, Id, Status, , MessageText | ConvertTo-html -head $a -As TABLE |Out-File "C:\test\ser7.html" 

    More references:

    PowerShell to query Intune Health Dashboard.

    https://blogs.technet.microsoft.com/configmgrdogs/2016/08/08/powershell-to-query-intune-health-dashboard/

    Gets the health events from O365 via API call.

    https://gist.github.com/alexinnes/0b6983c75e30ea7f5e99

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    • Edited by Sara Fan Friday, November 10, 2017 10:08 AM
    Friday, November 10, 2017 10:07 AM
  • Hi Sara,

    Thanks for your updates.

    Iam facing the connectivity issue now while i executing the scripts

    invoke-restmethod : The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

    At D:\Ramki\Scripts\HealthCheck\ServiceHealth.PS1:10 char:12

    + $cookie = (invoke-restmethod -contenttype "application/json" -method Post -uri " ...

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException

        + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

    invoke-restmethod : The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

    At D:\Ramki\Scripts\HealthCheck\ServiceHealth.PS1:12 char:12

    + $events = (invoke-restmethod -contenttype "application/json" -method Post -uri " ...

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException

        + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

    get-member : You must specify an object for the Get-Member cmdlet.

    At D:\Ramki\Scripts\HealthCheck\ServiceHealth.PS1:13 char:11

    + $events | get-member

    +           ~~~~~~~~~~

        + CategoryInfo          : CloseError: (:) [Get-Member], InvalidOperationException

        + FullyQualifiedErrorId : NoObjectInGetMember,Microsoft.PowerShell.Commands.GetMemberCommand

     

    Monday, November 13, 2017 10:11 AM
  • Hi rush2ramki,

    I encountered the same connection issue as yours.

    Because the certificates of the site https://api.admin.microsoftonline.com/shdtenantcommunications.svc/Register and https://api.admin.microsoftonline.com/shdtenantcommunications.svc/GetEvents have expired.

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    • Edited by Sara Fan Tuesday, November 14, 2017 1:11 AM
    Tuesday, November 14, 2017 1:11 AM
  • Hi Sara,

    So for this , should i contact MS for certificate expired. or do i need to look on this my local machine

    Tuesday, November 14, 2017 6:13 AM
  • Hi rush2ramki,

    Thank you for reporting the issue.

    And we will report the issue internally.

    You could use the API in the article below to check if it can work.

    https://msdn.microsoft.com/office-365/office-365-service-communications-api-reference

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    • Edited by Sara Fan Tuesday, November 14, 2017 8:55 AM
    Tuesday, November 14, 2017 8:42 AM
  • Hi Sara,

    is this correct.? iam not much in scripting. it would be good if you corrected the below to make it work


    $cred = get-credential
    $jsonPayload = (@{userName=$cred.username;password=$cred.GetNetworkCredential().password;} | convertto-json).tostring()
    $cookie = (invoke-restmethod -contenttype "application/json" -method Post -uri "Get https://manage.office.com/api/v1.0/Tenant ID/ServiceComms/Services" -body $jsonPayload).RegistrationCookie
    $jsonPayload = (@{lastCookie=$cookie;locale="en-US";preferredEventTypes=@(0,1)} | convertto-json).tostring()
    $events = (invoke-restmethod -contenttype "application/json" -method Post -uri "GET https://manage.office.com/api/v1.0/Tenant ID /ServiceComms/Services" -body $jsonPayload)
    $events | get-member


    Wednesday, November 15, 2017 11:14 AM
  • Hi rush2ramki,

    Sorry for delayed reply.

    Quoted from the article.

    “The Office 365 Service Communications API is a REST service that allows you to develop solutions using any web language and hosting environment that supports HTTPS and X.509 certificates.”

    It is the rest service. We cannot use it in PowerShell script. We should call the REST service.

    For more information, refer to the article below.

    Complete basic operations using SharePoint REST endpoints.

    https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/complete-basic-operations-using-sharepoint-rest-endpoints

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, November 27, 2017 9:53 AM
  • Hi Sara 

    Thanks. now its working fine with OLD one. but still am facing with 

    System.Object[]

    Monday, November 27, 2017 5:34 PM
  • Hi rush2ramki,

    To resolve the “system.object[]” issue, there is a PowerShell script demo below you could refer to.

      $cred = get-credential
    
    
     #$a = "<style>BODY{background-color:peachpuff;}</style>"
    
     $a = "<style>"
    
     $a = $a + "BODY{background-color:peachpuff;}"
    
     $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
    
     $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
    
     $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:PaleGoldenrod}"
    
     $a = $a + "</style>"
    
     $jsonPayload = (@{userName=$cred.username;password=$cred.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)
    
     $events | get-member
    
     $events.Events  
    
     Function GetMessage()
    
     {
    
     $text="";
    
     foreach($message in $($_).Messages)
    
     { 
    
     $text=$message.MessageText;
    
     }
    
     return $text;
    
     }
    
     
    
     $events.Events | Select-object StartTime, Title, EndTime, LastUpdatedTime, Id, Status, @{Name="Messages";Expression={GetMessage}}  | ConvertTo-html -head $a -As TABLE |Out-File "C:\test\ser9.html" 

    The result as below:

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    Tuesday, November 28, 2017 1:22 AM
  • Hi Sara

    Thanks a lot for your effort to guide me. Let me check and update you shortly

    Friday, December 1, 2017 4:23 PM
  • Excellant Sara. It working like charm!
    Wednesday, December 6, 2017 10:42 AM
  • Hi Sara,

    Just  a final query. Are we able to change the color format,  if any service status went as "Service Degraded" like higlighted in RED"

    Tuesday, January 2, 2018 4:43 PM