Json to CSV in Powershell

Question

I'm working with Powershell, querying Azure Log analytics with the LogAnalyticsQuery module using Invoke-LogAnalyticsQuery.

I have the results

{
    "tables": [
        {
            "name": "PrimaryResult",
            "columns": [
                {"name": "Computer","type": "string"},
                {"name": "TimeGenerated","type": "datetime"},
                {"name": "AggregatedValue","type": "real"
                }
            ],
            "rows": [
                ["VPN-Server","2018-02-20T07:30:00Z",5.083333333333333],
                ["SARMAD-SurfacePro4","2018-02-20T07:30:00Z",14.598250052664012],
                ["VPN-Server","2018-02-20T07:00:00Z",4.9523809523809526],
                ["SARMAD-SurfacePro4","2018-02-20T07:00:00Z",12.104500129109331],
                ["SARMAD-SurfacePro4","2018-02-20T08:00:00Z",20.936097813082174],
                ["VPN-Server","2018-02-20T08:00:00Z",4.245614035087719]
            ]
        }
    ]
}

From the sample above, how I can use powershell to convert it to something like this?:

Reportname, Computer, TimeGenerated, AggregatedValue

Manual Value, JsonValue, JsonValue, JsonValue

henrycarteruk · Accepted Answer · 2018-02-20 09:27:01Z

2

You can use convertfrom-json and can finally use export-csv to export:

$a='{"tables":[{"name":"PrimaryResult","columns":[{"name":"Computer","type":"string"},{"name":"TimeGenerated","type":"datetime"},{"name":"AggregatedValue","type":"real"}],"rows":[["VPN-Server","2018-02-20T07:30:00Z",5.083333333333333],["SARMAD-SurfacePro4","2018-02-20T07:30:00Z",14.598250052664012],["VPN-Server","2018-02-20T07:00:00Z",4.9523809523809526],["SARMAD-SurfacePro4","2018-02-20T07:00:00Z",12.104500129109331],["SARMAD-SurfacePro4","2018-02-20T08:00:00Z",20.936097813082174],["VPN-Server","2018-02-20T08:00:00Z",4.245614035087719]]}]}'
($a | ConvertFrom-Json).tables.columns.name

Hope it helps.

edited Feb 20, 2018 at 9:27

henrycarteruk

13.3k2 gold badges40 silver badges40 bronze badges

answered Feb 20, 2018 at 9:22

Ranadip Dutta

9,5493 gold badges33 silver badges48 bronze badges

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

3 Comments

Ranadip Dutta Over a year ago

@Data-base: Acceptance would be appreciable.

Data-Base Over a year ago

one more question what about the rows? I get then in one line like 1,2,3,1,2,3, is there away to split them them?

Ranadip Dutta Over a year ago

Yes even you can use -Split "," to split it or you can directly use the string method $object.split(',')

fdafadf · Accepted Answer · 2018-02-20 12:06:24Z

1

You can do it similar to this:

function Get-Tables
{
    param([Parameter(ValueFromPipeline=$true)]$Json)

    process
    {
        foreach ($table in ($Json | ConvertFrom-Json).tables)
        {
            foreach ($row in $table.rows)
            {
                $result = New-Object PSObject
                $columnIndex = 0

                foreach ($column in $table.columns)
                {
                    Add-Member -InputObject $result -MemberType NoteProperty -Name $column.Name -Value ($row[$columnIndex++])
                }

                $result
            }
        }
    }
}

$json | Get-Tables | Export-Csv

Where $json is string variable with value from the question example.

$json = '{  "tables": [   ...  ] }'

edited Feb 20, 2018 at 12:06

answered Feb 20, 2018 at 11:17

fdafadf

8295 silver badges14 bronze badges

3 Comments

Data-Base Over a year ago

getteing error ConvertFrom-Json : Invalid JSON primitive: . At line:7 char:37 + foreach ($table in ($Json | ConvertFrom-Json).tables) + ~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [ConvertFrom-Json], ArgumentException + FullyQualifiedErrorId : System.ArgumentException,Microsoft.PowerShell.Commands.ConvertFromJsonCommand

Data-Base Over a year ago

what about if it was Json and not a string? the data is the results of the Invoke-LogAnalyticsQuery, then I guess I need to remove the ConvertFrom-Json

Data-Base Over a year ago

I need to use $results.Response.Content when I run $results = Invoke-LogAnalyticsQuery and the result is a string ... now it works :)

Collectives™ on Stack Overflow

Json to CSV in Powershell

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related