Exporting to CSV

[geonaute123]

I am trying to export 2 metrics of a specific management zone to CSV: the number of transactions and response time, however I get the following error with this powershell code:

[Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }

$DYNATRACE_API_TOKEN = 'tu_token_de_acceso'
$ZONA_GESTION = 'Mngz-WSZonaClienteSFC'

$METRICA_TRANSACCIONES = "builtin:service.requestCount.total:filter(eq(mzName,'$ZONA_GESTION')):splitBy()"
$METRICA_TIEMPO_RESPUESTA = "builtin:service.response.time:filter(eq(mzName,'$ZONA_GESTION')):splitBy()"

$respuesta_transacciones = Invoke-RestMethod -Uri "https://{tu-dynatrace-managed}/e/{tu-environment-id}/api/v2/metrics/query?metricSelector=$METRICA_TRANSACCIONES" -Headers @{ "Authorization" = "Api-Token $DYNATRACE_API_TOKEN"; "Content-Type" = "application/json" } -Method Get
$respuesta_tiempo_respuesta = Invoke-RestMethod -Uri "https://{tu-dynatrace-managed}/e/{tu-environment-id}/api/v2/metrics/query?metricSelector=$METRICA_TIEMPO_RESPUESTA" -Headers @{ "Authorization" = "Api-Token $DYNATRACE_API_TOKEN"; "Content-Type" = "application/json" } -Method Get

if ($respuesta_transacciones -eq $null -or $respuesta_tiempo_respuesta -eq $null) {
Write-Output "Error al obtener los datos."
exit
}

$transacciones = $respuesta_transacciones.result[0].data | ForEach-Object { [PSCustomObject]@{ ServiceID = $_.dimensions[0]; Transactions = $_.values[0] } }
$tiempo_respuesta = $respuesta_tiempo_respuesta.result[0].data | ForEach-Object { [PSCustomObject]@{ ServiceID = $_.dimensions[0]; ResponseTime = $_.values[0] } }

$datos_combinados = $transacciones | ForEach-Object {
$id = $_.ServiceID
$respuesta = $tiempo_respuesta | Where-Object { $_.ServiceID -eq $id }
[PSCustomObject]@{ ServiceID = $_.ServiceID; Transactions = $_.Transactions; ResponseTime = $respuesta.ResponseTime }
}

$datos_combinados | Export-Csv -Path "dynatrace_service_metrics.csv" -NoTypeInformation
Write-Output "Datos exportados exitosamente a dynatrace_service_metrics.csv"

The error is: line 6 character 28 and line 7 char 31: Invalid operation (system.net.nhttpwebrequest:httpwebrequest) 

fullyqualifiederrorid: webcmdletwebresponseexception,microsoft.powershell.commands.invokerestmethodcommand

What I am doing wrong? Thanks. 

[MaximilianoML · Accepted solution]

Hello, @geonaute123!

First of all, I'm not a PowerShell Script Guru, but I'll try to help you.

I did some changes in your code and I'll let my code snippet and my explanation below. 🙂

# Disable SSL certificate validation (use with caution)
[Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }

# Define variables
$DYNATRACE_API_TOKEN = 'your_access_token'
$ZONA_GESTION = 'Mngz-WSZonaClienteSFC'
$DYNATRACE_MANAGED = 'your_dynatrace_managed'
$ENVIRONMENT_ID = 'your_environment_id'

# Define metric selectors
$METRICA_TRANSACCIONES = "builtin:service.requestCount.total:filter(eq(mzName,'$ZONA_GESTION')):splitBy()"
$METRICA_TIEMPO_RESPUESTA = "builtin:service.response.time:filter(eq(mzName,'$ZONA_GESTION')):splitBy()"

# Perform API requests with error handling
try {
    $respuesta_transacciones = Invoke-RestMethod -Uri "https://$DYNATRACE_MANAGED/e/$ENVIRONMENT_ID/api/v2/metrics/query?metricSelector=$($METRICA_TRANSACCIONES)" -Headers @{ "Authorization" = "Api-Token $DYNATRACE_API_TOKEN"; "Content-Type" = "application/json" } -Method Get
    $respuesta_tiempo_respuesta = Invoke-RestMethod -Uri "https://$DYNATRACE_MANAGED/e/$ENVIRONMENT_ID/api/v2/metrics/query?metricSelector=$($METRICA_TIEMPO_RESPUESTA)" -Headers @{ "Authorization" = "Api-Token $DYNATRACE_API_TOKEN"; "Content-Type" = "application/json" } -Method Get
} catch {
    Write-Output "Error making the request: $_"
    exit
}

# Verify responses are not null
if ($respuesta_transacciones -eq $null -or $respuesta_tiempo_respuesta -eq $null) {
    Write-Output "Error obtaining data."
    exit
}

# Verify the response structure
if (-not $respuesta_transacciones.result -or -not $respuesta_tiempo_respuesta.result) {
    Write-Output "Unexpected response structure."
    exit
}

# Process the transaction data
$transacciones = $respuesta_transacciones.result[0].data | ForEach-Object { 
    [PSCustomObject]@{ 
        ServiceID = $_.dimensions[0]; 
        Transactions = $_.values[0] 
    } 
}

# Process the response time data
$tiempo_respuesta = $respuesta_tiempo_respuesta.result[0].data | ForEach-Object { 
    [PSCustomObject]@{ 
        ServiceID = $_.dimensions[0]; 
        ResponseTime = $_.values[0] 
    } 
}

# Combine the data based on ServiceID
$datos_combinados = $transacciones | ForEach-Object {
    $id = $_.ServiceID
    $respuesta = $tiempo_respuesta | Where-Object { $_.ServiceID -eq $id }
    [PSCustomObject]@{ 
        ServiceID = $_.ServiceID; 
        Transactions = $_.Transactions; 
        ResponseTime = $respuesta.ResponseTime 
    }
}

# Export combined data to CSV
$datos_combinados | Export-Csv -Path "dynatrace_service_metrics.csv" -NoTypeInformation
Write-Output "Data successfully exported to dynatrace_service_metrics.csv"

Variable Definitions:

• Ensure that the $DYNATRACE_MANAGED and $ENVIRONMENT_ID variables are correctly defined.
• Properly interpolate variables within the URL by using $($variable) syntax.

Error Handling:

• Added try-catch blocks around the API requests to catch and display any errors that occur during the HTTP requests.

Response Verification:

• Added checks to verify that the responses are not null and have the expected structure before processing the data.

Processing and Combining Data:

• Used ForEach-Object to process the transaction and response time data.
• Combined the data based on the ServiceID to ensure that the final CSV contains both metrics for each service.

[geonaute123]

Thanks for the answer. I am trying to get these 2 metrics to a CSV from a list of host that pertains to the "Mngz-WSZonaClienteSFC" management zone.