vROPS – Automating Telegraf SQL Configuration

Let’s face it. There’s a certain charm in being lazy—a type of ingenuity that makes us find the easiest way to accomplish the most tedious tasks. If you’re managing VMware environments and have ever groaned at the thought of manually configuring Telegraf agents for SQL monitoring across a fleet of VMs in Aria, then congratulations—you’re like me.

Imagine being handed a list of virtual machines—each needing Telegraf configured for SQL monitoring. You’ll set up usernames, passwords, instances, and domains, one painstaking VM at a time. Multiply that by dozens or even hundreds of VMs, and you’ve got a recipe for sore wrists, missed deadlines, and existential crises.

But why suffer through the drudgery of repetitive configuration when you can make a script do all the work?

You, my friend, have unlocked the secret sauce, another “Powershell script” to be lazy!!! Think of it as the digital equivalent of hiring a minion to do your dirty work. With just a few parameters, this bad boy logs into VMware vRealize Operations (vR Ops), fetches the target VM’s details, and updates the Telegraf agent to monitor SQL—faster than you can say, “Did I save my changes?”

Here’s What the Script Does:

1. Logs Into vR Ops: Authenticates securely (because we’re lazy, not reckless).
2. Finds the Target VM: Searches for the VM by name like it’s Googling its crush.
3. Grabs the Telegraf Config: Checks if SQL monitoring is already enabled.
4. Adds the SQL Config (if Needed): Updates the agent settings to include SQL monitoring, saving you from endless clicks and typo-induced errors.

It’s like a sous-chef for your VMware environment. All you have to do is tell it what to do, and it handles the nitty-gritty details.

Here is how you execute it:

.\Configure-TelegrafSQL.ps1 -VMName "MyFancyVM" -SQLUsername "admin" -SQLPassword "s3cr3t!" -SQLInstance "MSSQLSERVER" -SQLDomain "mydomain.local" -VropsEndpoint "vrops.mycompany.com" -VropsUser "automation_user" -VropsPassword "securepassword"

and here is the code

# Accept input parameters instead of environment variables
param (
    [string]$VMName,
    [string]$SQLUsername,
    [string]$SQLInstance,
    [string]$SQLDomain,
    [string]$SQLPassword,
    [string]$VropsEndpoint,
    [string]$VropsUser,
    [string]$VropsPassword
)

# Display provided parameters
Write-Host "VMName: $VMName"
Write-Host "SQLUsername: $SQLUsername"
Write-Host "SQLInstance: $SQLInstance"
Write-Host "SQLDomain: $SQLDomain"

# Define configuration table
$configTable = @{
    VMName      = $VMName
    SQLUsername = $SQLUsername
    SQLPassword = $SQLPassword
    SQLInstance = $SQLInstance
    SQLDomain   = $SQLDomain
}

$vropsAddress = $VropsEndpoint
$vropsPassword = ConvertTo-SecureString $VropsPassword -AsPlainText -Force
$vRopsCred = New-Object System.Management.Automation.PSCredential -ArgumentList $VropsUser, $vropsPassword

# Set error action preference to stop on errors
$ErrorActionPreference = "Stop"

# Function: Acquire a token from vR Ops
function AcquireVropsToken {
    param(
        [PSCredential]$credentialFile,
        [string]$vropsServer
    )

    $vropsUser = $credentialFile.UserName
    $vropsPassword = $credentialFile.GetNetworkCredential().Password
    $BaseAuthURL = "https://$vropsServer/suite-api/api/auth/token/acquire"

    $AuthTable = @{ username = $vropsUser; password = $vropsPassword }
    $AuthJSON = $AuthTable | ConvertTo-Json -Compress

    $vropsSessionResponse = Invoke-RestMethod -Method POST -Uri $BaseAuthURL -Body $AuthJSON -ContentType "application/json"

    return @{ Authorization = "OpsToken $($vropsSessionResponse.'auth-token'.token)"; Accept = 'application/json'; "X-vRealizeOps-API-use-unsupported" = "true" }
}

# Function: Get an object by name from vR Ops
function GetObject {
    param(
        [string]$vropsObjName,
        [string]$resourceKindKey,
        [string]$vropsServer,
        $vropsToken
    )

    $encodedName = [System.Web.HttpUtility]::UrlEncode($vropsObjName)
    $checkerUri = "https://$vropsServer/suite-api/api/resources?resourceKind=$resourceKindKey&name=$encodedName&adapterKind=VMWARE&resourceStatus=DATA_RECEIVING"

    $Checker = Invoke-RestMethod -Method GET -Uri $checkerUri -Headers $vropsToken
    if ($Checker.pageInfo.totalCount -ge 1) {
        return $Checker.resourceList[0]
    }
    else {
        throw "Object not found: $vropsObjName"
    }
}

# Function: Get Telegraf agent configuration
function GetVropsTelegrafAgent {
    param(
        [string]$resourceID,
        [string]$vropsServer,
        $vropsToken
    )

    $BaseURL = "https://$vropsServer/suite-api/api/applications/agents/$resourceID/services"
    return Invoke-RestMethod -Method GET -Uri $BaseURL -Headers $vropsToken
}

# Function: Update Telegraf agent configuration
function UpdateVropsTelegrafAgent {
    param(
        [string]$resourceID,
        [string]$vropsServer,
        $vropsToken,
        $sqlTelegrafConfig,
        $configTable
    )

    $BaseURL = "https://$vropsServer/suite-api/api/applications/agents/$resourceID/services"
    $agentData = $sqlTelegrafConfig
    $agentNeeded = $false

    foreach ($service in $agentData.services) {
        if ($service.serviceName -eq "mssql" -and $service.configurations.Count -eq 0) {
            $agentNeeded = $true
            $newConfig = @{
                configName  = "MSSQL"
                isActivated = $true
                parameters  = @(
                    @{ key = "PASSWORD"; value = $configTable.SQLPassword },
                    @{ key = "USERNAME"; value = "$($configTable.SQLDomain)\$($configTable.SQLUsername)" },
                    @{ key = "INSTANCE"; value = $configTable.SQLInstance }
                )
            }
            $service.configurations += $newConfig
        }
    }

    if ($agentNeeded) {
        $updatedJson = $agentData | ConvertTo-Json -Depth 10 -Compress
        Invoke-RestMethod -Method POST -Uri $BaseURL -Headers $vropsToken -ContentType "application/json" -Body $updatedJson
    }
    else {
        throw "No update needed: MSSQL configuration is already present."
    }
}

# Main Execution
try {
    $headersPostAuth = AcquireVropsToken -credentialFile $vRopsCred -vropsServer $vropsAddress

    $resource = GetObject -vropsObjName $configTable.VMName -resourceKindKey 'VirtualMachine' -vropsServer $vropsAddress -vropsToken $headersPostAuth

    $sqlTelegrafConfig = GetVropsTelegrafAgent -resourceID $resource.identifier -vropsServer $vropsAddress -vropsToken $headersPostAuth

    UpdateVropsTelegrafAgent -resourceID $resource.identifier -vropsServer $vropsAddress -vropsToken $headersPostAuth -sqlTelegrafConfig $sqlTelegrafConfig -configTable $configTable

    Write-Host "Telegraf agent successfully updated."
} catch {
    Write-Error "Error occurred: $_"
}

Hope you found this helpful

vMAN