Google Cloud SQL Idle Instance Recommender

[nharrisdobson]

Apologies if this is in the wrong place..

I am working on some code to surface recommendations via the Google Cloud SQL Idle Instance Recommender. For the other Recommendations via Recommender API (Idle IP, Idle Persistent Disk etc) I am able to use the recommender.resourceLink when passing a call to the recommender API for compute.address.IdleResourceRecommender/recommendations for example and get an output similar to the below:

[
  {
    "accountID": "xxxxx",
    "costNanos": -776000000,
    "costUnits": "-7",
    "currency": "USD",
    "description": "Save cost by deleting idle IP address 'xxxxxx'.",
    "duration": "2592000s",
    "id": "projects/xxxxxx/locations/europe-west3/recommenders/google.compute.address.IdleResourceRecommender/recommendations/xxxxxx",
    "primaryImpact": {
      "category": "COST",
      "costProjection": {
        "cost": {
          "currencyCode": "USD",
          "nanos": -776000000,
          "units": "-7"
        },
        "duration": "2592000s"
      }
    },
    "priority": "P4",
    "projectNumber": null,
    "recommenderSubtype": "DELETE_ADDRESS",
    "region": "europe-west3",
    "resourceLink": "//compute.googleapis.com/projects/xxxxxx/regions/europe-west3/addresses/xxxxxx",
    "resourceName": "xxxxxx",
    "state": "ACTIVE"
  }
]

 In these use cases, I can use resourceLink to match resources to recommendations. 

I wanted to do the same for the Cloud SQL Idle Instance recommendations, and whilst I can obtain selfLink (and replace the host segment with "" to iterate through without the host string) 

_.each(databases, function(database) {

        var resource_link = database.selfLink.replace("https://sqladmin.googleapis.com/v1/", "")

        database["resourceLink"] = resource_link

    })

this isn't valid to use resourceLink for Cloud SQL as it doesnt appear to be part of the recommendations attributes pulled back via this specific API (code below taken from the Idle IP Recommender).

    _.each(recommendations, function (recommendation) {

        count ++

        console.log(recommendation.resourceLink)

        var rec_resource_link = recommendation.resourceLink.replace("//compute.googleapis.com/", "")

        var database = _.find(databases, function(db) {

            return db.resourceLink == rec_resource_link

        })

Wondered if anybody had any detail on the attributes if something else may give me the path I desire? The attribute in the Idle IP recommender provides me with the following:

"resourceLink": "//compute.googleapis.com/projects/xxxxxx/regions/europe-west3/addresses/xxxxxx",

a major difference being I can pull this into my datasources (this again is from Idle IP)

#GET LIST OF RECOMMENDATIONS
datasource "ds_recommendations" do
iterate $ds_recommenders
request do
run_script $js_recommender_call, val(iter_item,"accountID"), val(iter_item, "region")
end
result do
encoding "json"
collect jmes_path(response, "recommendations[*]") do
field "accountID", val(iter_item, "accountID")
field "projectNumber", val(iter_item, "projectNumber")
field "region", val(iter_item, "region")
field "id", jmes_path(col_item, "name")
field "description", jmes_path(col_item, "description")
field "resourceLink", jmes_path(col_item, "content.overview.resource")
field "resourceName", jmes_path(col_item, "content.overview.resourceName")
field "primaryImpact", jmes_path(col_item, "primaryImpact")
field "costUnits", jmes_path(col_item, "primaryImpact.costProjection.cost.units")
field "costNanos", jmes_path(col_item, "primaryImpact.costProjection.cost.nanos")
field "duration", jmes_path(col_item, "primaryImpact.costProjection.duration")
field "currency", jmes_path(col_item, "primaryImpact.costProjection.cost.currencyCode")
field "priority", jmes_path(col_item, "priority")
field "recommenderSubtype", jmes_path(col_item, "recommenderSubtype")
field "state", jmes_path(col_item, "stateInfo.state")
end
end
end

[ms4446 · Accepted solution]

Here is how you might structure your code based on the structure of the returned recommendation object from the Cloud SQL Idle Instance recommender:

{
"name": "projects/PROJECT_ID/locations/LOCATION/recommenders/google.cloudsql.instance.RECOMMENDER/recommendations/RECOMMENDATION_ID",
"description": "The instance has shown low levels of activity during the observation period.",
"lastRefreshTime": "2023-06-27T00:00:00Z",
"primaryImpact": {
"category": "COST",
"costProjection": {
"cost": {
"currencyCode": "USD",
"units": "-1"
},
"duration": "2592000s"
}
},
"recommenderSubtype": "LOW_ACTIVITY",
"content": {
"operationGroups": [
{
"operations": [
{
"action": "test",
"resource": "//sqladmin.googleapis.com/projects/PROJECT_ID/instances/INSTANCE_ID",
"resourceType": "sqladmin.googleapis.com/Instance",
"path": "/status",
"value": "STOPPED"
}
]
}
]
}
}

You can see that the resource field in the content.operationGroups.operations object might contain the equivalent of the resourceLink field you're looking for. You can adjust your existing code to extract this information:

# GET LIST OF RECOMMENDATIONS
datasource "ds_recommendations" do
iterate $ds_recommenders
request do
run_script $js_recommender_call, val(iter_item,"accountID"), val(iter_item, "region")
end
result do
encoding "json"
collect jmes_path(response, "recommendations[*]") do
field "accountID", val(iter_item, "accountID")
field "projectNumber", val(iter_item, "projectNumber")
field "region", val(iter_item, "region")
field "id", jmes_path(col_item, "name")
field "description", jmes_path(col_item, "description")
field "resourceLink", jmes_path(col_item, "content.operationGroups[0].operations[0].resource")
field "resourceName", jmes_path(col_item, "content.operationGroups[0].operations[0].resource")
field "primaryImpact", jmes_path(col_item, "primaryImpact")
field "costUnits", jmes_path(col_item, "primaryImpact.costProjection.cost.units")
field "costNanos", jmes_path(col_item, "primaryImpact.costProjection.cost.nanos")
field "duration", jmes_path(col_item, "primaryImpact.costProjection.duration")
field "currency", jmes_path(col_item, "primaryImpact.costProjection.cost.currencyCode")
field "priority", jmes_path(col_item, "priority")
field "recommenderSubtype", jmes_path(col_item, "recommenderSubtype")
field "state", jmes_path(col_item, "stateInfo.state")
end
end
end

[nharrisdobson]

Thanks! I do indeed now get a result when I put a console log in place to verify:

console.log: "//sqladmin.googleapis.com/v1/projects/xxxxxx/locations/europe-west1/instances/xxxxxx-db"