topic Exporting to CSV in Dynatrace API

Exporting to CSV

geonaute123 — Mon, 20 May 2024 07:20:56 GMT

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.

Re: Exporting to CSV

MaximilianoML — Fri, 17 May 2024 08:56:03 GMT

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.

Re: Exporting to CSV

geonaute123 — Fri, 17 May 2024 10:26:47 GMT

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.