Powershell中的Json到CSV

Question

我正在使用Powershell，使用LogAnalyticsQuery模块使用Invoke-LogAnalyticsQuery查询Azure日志分析。

我有结果

{
    "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]
            ]
        }
    ]
}

从上面的示例中，我如何使用powershell将其转换为类似的内容？

Reportname, Computer, TimeGenerated, AggregatedValue

Manual Value, JsonValue, JsonValue, JsonValue

Answer 1

您可以这样做：

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

其中$json是字符串变量，其中包含问题示例中的值。

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

Answer 2

您可以使用convertfrom-json并最终使用export-csv导出：

$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

希望能帮上忙。