通过Powershell转换excel中的json值

Question

$filepath = $PSScriptRoot
Set-Location $filepath
$lastmonth = $true
function GetAccessToken
{
$azureCmdlet = get-command -Name Get-AZContext -ErrorAction SilentlyContinue
if ($azureCmdlet -eq $null)
{
$null = Import-Module AZ -ErrorAction Stop;
}
$AzureContext = & "Get-AZContext" -ErrorAction Stop;
$authenticationFactory = New-Object -TypeName Microsoft.Azure.Commands.Common.Authentication.Factories.AuthenticationFactory
if ((Get-Variable -Name PSEdition -ErrorAction Ignore) -and ('Core' -eq $PSEdition))
{
[Action[string]]$stringAction = { param ($s) }
$serviceCredentials = $authenticationFactory.GetServiceClientCredentials($AzureContext, $stringAction)
}
else
{
$serviceCredentials = $authenticationFactory.GetServiceClientCredentials($AzureContext)
}

# We can't get a token directly from the service credentials. Instead, we need to make a dummy message which we will ask
# the serviceCredentials to add an auth token to, then we can take the token from this message.
$message = New-Object System.Net.Http.HttpRequestMessage -ArgumentList @([System.Net.Http.HttpMethod]::Get, "http://foobar/")
$cancellationToken = New-Object System.Threading.CancellationToken
$null = $serviceCredentials.ProcessHttpRequestAsync($message, $cancellationToken).GetAwaiter().GetResult()
$accessToken = $message.Headers.GetValues("Authorization").Split(" ")[1] # This comes out in the form "Bearer <token>"

$accessToken
}
function GetHeaders
{
param (
[string]$AccessToken,
[switch]$IncludeStatistics,
[switch]$IncludeRender,
[int]$ServerTimeout
)

$preferString = "response-v1=true"

if ($IncludeStatistics)
{
$preferString += ",include-statistics=true"
}

if ($IncludeRender)
{
$preferString += ",include-render=true"
}

if ($ServerTimeout -ne $null)
{
$preferString += ",wait=$ServerTimeout"
}

$headers = @{
"Authorization"          = "Bearer $accessToken";
"prefer"                 = $preferString;
"x-ms-app"               = "LogAnalyticsQuery.psm1";
"x-ms-client-request-id" = [Guid]::NewGuid().ToString();
}

$headers
}

$json = '
{
"type": "Usage",
"timeframe": "TheLastMonth",
"dataset": {
"granularity": "Monthly",
"aggregation": {
"totalCost": {
"name": "PreTaxCost",
"function": "Sum"
}
},
"grouping": [
{
"type": "Dimension",
"name": "ResourceId"
}
]
}
}
'
<#
else
{
$json = '
{
"type": "Usage",
"timeframe": "MonthToDate",
"dataset": {
"granularity": "Daily",
"aggregation": {
"totalCost": {
"name": "PreTaxCost",
"function": "Sum"
}
},
"grouping": [
{
"type": "Dimension",
"name": "ResourceId"
}
]
}
}
'

}
#>
$subscriptionlist = Get-AzSubscription | where {$_.State -eq 'Enabled'}
$accessToken = GetAccessToken
$headers = GetHeaders $accessToken -IncludeStatistics:$null -IncludeRender:$null -ServerTimeout 1000
$i = 0
$c = 50
$b = 100
$subscriptionlist | foreach {
$i++
$subid = $_.Id
$subscriptionname = $_.name
$uri = "https://management.azure.com/subscriptions/$subid/providers/Microsoft.CostManagement/query?api-version=2019-11-01"
$response = Invoke-WebRequest -UseBasicParsing -Uri $uri -ContentType "application/json" -Headers $headers -Method POST -Body $json
write-host "$i . $subscriptionname" -ForegroundColor Green
if($i -eq $b)

$PreTaxvirtualmachineCostvalue = 0
$PreTaxCost1 = 0
(($response.Content | ConvertFrom-Json).properties.rows) | foreach {
$costdata = $null
$PreTaxCost = $null
$BillingMonth = $null
$ResourceId = $null
$Currency = $null

$costdata = $_
$PreTaxCost = $costdata[0]
$BillingMonth = $costdata[1]
$ResourceId = $costdata[2]
$Currency = $costdata[3]
$resourceName = ($ResourceId -split '/')[-1]
$Resourcetype = (($ResourceId -split '/providers/')[1] -split '/')[0..1] -join '/'
$PreTaxvirtualmachineCostvalue = $PreTaxvirtualmachineCostvalue + $PreTaxCost

$PreTaxCost1 = ([math]::Round($PreTaxCost, 2)) + $PreTaxCost1


}

我想知道azure资源的成本，我创建了上面的查询。我想将json数据转换为excel，同时我还想将以下数据添加到查询Application_Name中Application_OwnerBusiness_OwnerCost_CodeEnvironment_Name位置资源组订阅

请同样帮助我。

Answer 1

您可以在foreach语句' (($response) '中添加以下代码段。Content | ConvertFrom-Json).properties.rows) | foreach {........ .properties.rows除了你的代码。

$item = New-Object PSCustomObject
$item | Add-Member -NotePropertyName "PreTax Cost"  -NotePropertyValue $PreTaxCost
$item | Add-Member -NotePropertyName "Billing Month"  -NotePropertyValue $BillingMonth 
$item | Add-Member -NotePropertyName "Resource Id"  -NotePropertyValue  $ResourceId
$item | Export-Csv -Path D:Output.csv -Append -NoTypeInformation

基本上，您创建了一个具有多列单行值的对象，并将其导出到excel。在迭代过程中，所有的数据都会以csv文件的形式追加到excel中。