Extract data from json using powershell

Question

I executing below command using strem from powershell and I am getting response in form of json using the below code

$session = New-SSHSession -ComputerName $S_1 -Credential $cred

$Strem = New-SSHShellStream -SSHSession $Session

        $cmd_4 = $Strem.WriteLine("shell.connect('USER@X92SL224XXX2XX:3306')")
        sleep -Seconds 5
        $Strem.read()
        $pass = $Strem.WriteLine("password")
        sleep -Seconds 5
        $Strem.read()
        $cmd_5 = $Strem.WriteLine("var cluster = dba.getCluster('IBDCluster')")
        sleep -Seconds 5
        $Strem.read()
        $cmd_6 = $Strem.WriteLine("cluster.status()")
        sleep -Seconds 5
        $ClusterStatus = $Strem.read()

[DBG]: PS C:\Windows\system32>> 
cluster.status()
{
    "clusterName": "IBDCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "X92SL224XXX2XX:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.", 
        "topology": {
            "X92SL224XXX1XX:3306": {
                "address": "X92SL224XXXXXXX:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "5.7.36"
            }, 
            "X92SL224XXX2XX:3306": {
                "address": "X92SL224XXX2XX:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "5.7.36"
            }, 
            "X92SL224XXXX3XX:3306": {
                "address": "X92SL224XXX3XX:3306", 
                "instanceErrors": [
                    "ERROR: group_replication has stopped with an error."
                ], 
                "memberRole": "SECONDARY", 
                "memberState": "ERROR", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)", 
                "version": "5.7.36"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "X92SL224XXXXXXX:3306"
}
[48;5;254m[38;5;23m My[0m[48;5;254m[38;5;166mSQL [0m[48;5;237m[38;5;15m X92SL224QDBA2DB:3306 ssl [0m[48;5;221m[38;5;0m JS [0m[48;5;0m> [0m

I need to fetch all the address, memberRole, memberState from the above data.

I was trying to convert from Json but getting error like

ConvertFrom-Json : Invalid JSON primitive: cluster.status.

Please let me know how to get the data from above

mklement0 · Accepted Answer · 2022-05-23 16:26:30Z

2

It looks like you simply need to remove the cluster.status() line that precedes the JSON text in the multi-line string that $Strem.Read() returns, as well as the extra line that follows it:

$ClusterStatus = $Strem.Read() -replace '^.+|.+$' | ConvertFrom-Json

Note: -replace '^.+|.+$' removes the first and last line from the input string, without also trying to remove a newline that follows / precedes them; however, these newlines are incidental whitespace that doesn't affect the operation of ConvertFrom-Json.

edited May 23, 2022 at 16:26

answered May 23, 2022 at 15:50

mklement0

452k68 gold badges727 silver badges986 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mklement0 Over a year ago

@EmptyCoder, please see my update, which now also removes the last line.

Empty Coder Over a year ago

Thanks for the response. the first and last line is getting removed using $Strem.Read() -replace '^.+|.+$' itself. but when I am executing ConvertFrom-Json, it is showing only values for "X92SL224XXX2XX:3306", not for all. Is it coz the output is not in json? because I need to fetch the values from the output as mentioned in the code for all the 3 servers

mklement0 Over a year ago

@EmptyCoder, I assume that's just the way in which the custom object graph that the JSON was converted to prints to the display. All the objects and properties should be there, which you can verify by piping the result to ConvertTo-Json or Format-Custom.

Collectives™ on Stack Overflow

Extract data from json using powershell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related