Google Cloud SQL API to get a list of Cloud SQL instances and generate a yaml for datadog

TL;DR

result is on GIST at

https://gist.github.com/kyle-hailey/b7527e38128660ad7c35f06fe820d1be

guess I'm a grumpy old man, but computer interfaces should be easier.

Have you ever found yourself frustrated with cloud services and their interfaces? You're not alone. As a frequent user of Cloud SQL instances, I find myself constantly adding and deleting instances, leading to the tedious task of editing conf.yaml files for Datadog monitoring.

NOTE: if you use GCP or RDS APIs for automated instances creation, deletion, cloning, parameter setting etc, please share. The world needs more practical examples. What methods do you use? Github? Ansible? Puppet? Cloudformation?

Computers were meant to automate tasks like these, yet we still find ourselves stuck in outdated processes.

It's not just the conf.yaml files that cause frustration, but also APIs and tools like JQ that can be cryptic and difficult to navigate. While GCP is simpler to use than RDS, real examples are often missing, making it challenging to apply API specs to practical use cases.

In an ideal world, there would be automated conf.yaml creation and auto-discovery for Cloud SQL instances. A web interface with point-and-click options would make managing instances a breeze, with advanced options available under a separate tab. It's time for cloud services to catch up with modern technology and make our lives easier.

Situation: I have a set of Cloud SQL instances. I'm adding and deleting them as I make clones and test copies and throw them away. Problem is I have to edit the conf.yaml for Datadog to monitor the new instances and stop monitoring the old instances. Its an annoying waste of time. Computers were meant for automating this kind of work.

Datadog should make automated conf.yaml creation. Actually we should skip dealing with conf.yaml all together. There should be auto discovery. There should be a web interface with point and click to choose options, main options should only be shown with advanced options shown under and advanced tab. Similiarly I find most APIs challenging as practical example are usually sorely missing. Real examples are so much more valuable than the API spec.

Summary:, I find

• APIs lacking examples (though GCP is much simpler than RDS though RDS is much more powerful)

• JQ opaque and cryptic

• conf.yaml antiquated

OK, to automate things

1. get list of instances for GCP via API

2. parse out the instance name and the public and private IP ( I connect DATADOG with internal IP because it stays stable, I use public IP in the name so its easy to connect from anywhere quickly if I need to copy the IP and connect to one of the databases)

3. Generate the the conf.yaml for datadog

( you need to set up gcloud on your host first and get your_project id from the cloud sql dashboard)

PID="your_project"

$ curl -s -H  \
  "Authorization: Bearer $(gcloud auth print-access-token)" \
"https://sqladmin.googleapis.com/sql/v1beta4/projects/${PID}/instances"


{
  "items": [
    {
      "kind": "sql#instance",
      "state": "RUNNABLE",
      "databaseVersion": "POSTGRES_13",
      "settings": {
        "authorizedGaeApplications": [],
        "tier": "XXXXX",
        "kind": "sql#settings",
        "availabilityType": "ZONAL",
        "pricingPlan": "PER_USE",
        "replicationType": "SYNCHRONOUS",
        "activationPolicy": "ALWAYS",
        "ipConfiguration": {
          "privateNetwork": "projects/XXXXXX/global/networks/default",
          "authorizedNetworks": [
            {
              "value": "0.0.0.0/0",
              "name": "change this ",
              "kind": "sql#aclEntry"
            }
          ]
...

Output is not exactly friendly

It's more friendly in a JSON viewer like https://codebeautify.org/json-navigator-online

You can pull out a list of instances like

$ cat db.json | jq '.items[]   | 
               .name as $name | 
               .ipAddresses[] | 
               select(.type == "PRIVATE") | 
               {name: $name, ipAddress: .ipAddress}'

# results  
           
{
  "name": "mydb",
  "ipAddress": "10.X.X.X"
}
{
  "name": "mydb",
  "ipAddress": "10.X.X.X"
}

Can pull out both the PRIMARY and PRIVATE

cat /tmp/db.json | jq '.items[] |
       {  name:        .name, 
         ipAddresses: 
               [ .ipAddresses[] | 
                  select(.type == "PRIVATE" or .type == "PRIMARY")| 
                                .ipAddress
                ] |
       select(length > 0)}'

Now I can build a conf.yaml for Datadog.

Here is my template

entry=$( cat << EOF
  - host: DB_PRIVATE_IP 
    username: $USER
    password: $PW
    reported_hostname:  DB_NAME-DB_PUBLIC_IP
    collect_activity_metrics: true
    collect_bloat_metrics: false
    dbm: true
    dbname: $DB
    relations:
      - relation_regex: .*
    batch_max_concurrent_send: 100 
    samples:
        batch_max_concurrent_send: 100
    metrics:
        batch_max_concurrent_send: 100
    database_monitoring:
        metrics:
            batch_max_concurrent_send: 100
EOF
)  

Need the header for the conf.yaml

for i in 1; do
cat << EOF
init_config:
instances:
EOF
done > /tmp/conf.yaml

Now put it all together

# set these values

USER= 
PW= 
DB= 

DEBUG=1

# Debug function to print a message
debug() {
  if [[ $DEBUG == 1 ]]; then
     echo "# $1 (debug) "
    # echo -e "$1   "
  fi
}

# dummy entry for testing
entry=$( cat << EOF   
   - host: DB_PRIVATE_IP  
       reported_hostname:  DB_NAME_DB_PUBLIC_IP
EOF
)  

entry=$( cat << EOF    
  - host: DB_PRIVATE_IP 
    username: $USER
    password: $PW
    reported_hostname:  DB_NAME-DB_PUBLIC_IP
    collect_activity_metrics: true
    collect_bloat_metrics: false
    dbm: true
    dbname: $DB
    relations:
      - relation_regex: .*
    batch_max_concurrent_send: 100 
    samples:
        batch_max_concurrent_send: 100 
    metrics:
        batch_max_concurrent_send: 100
    database_monitoring:
        metrics:
            batch_max_concurrent_send: 100  
EOF
)  
     
 cmd=$( cat << EOF                              
 cat db.json | jq '.items[] | 
                             {name: .name, ipAddresses: 
                             [.ipAddresses[] | 
                             select(.type == "PRIVATE" or .type == "PRIMARY") |
                              .ipAddress] | 
                             select(length > 0)} | 
                             [.name] + (if .ipAddresses | 
                             length == 1 then [.ipAddresses[0], .ipAddresses[0]]
                              else .ipAddresses end) | @csv'     |\
       sed -e 's;\\\\;;g'  | sed -e 's/""/"/g' 
EOF
)


for i in 1; do
cat << EOF                              
init_config:
instances:
EOF
done > /tmp/conf.yaml


for mystring in `eval $cmd`; do
    debug  "----"
    debug $mystring
    debug "     "
    export DB_NAME=$(echo $mystring | cut -d',' -f1 | sed -e 's/"//g' ) 
    export DB_PUBLIC_IP=$(echo $mystring | cut -d',' -f2 |sed -e 's/"//g' ) 
    export DB_PRIVATE_IP=$(echo $mystring | cut -d',' -f3| sed -e 's/"//g'  ) 
    debug "DB_NAME $DB_NAME"
    echo -e "$entry"  | \
        sed -e "s/DB_NAME/$DB_NAME/" | \
        sed -e "s/DB_PUBLIC_IP/$DB_PUBLIC_IP/" | \
        sed -e "s/DB_PRIVATE_IP/$DB_PRIVATE_IP/"
     debug "----"
     debug "    "
 done >> /tmp/conf.yaml


sudo cp /etc/datadog-agent/conf.d/postgres.d/conf.yaml /etc/datadog-agent/conf.d/postgres.d/conf.yaml.$$
sudo cp /tmp/conf.yaml /etc/datadog-agent/conf.d/postgres.d/conf.yaml

You can check out the yaml with yaml lint

https://www.yamllint.com/