Dynamic color changing text box based on Pipeline Build Status

Hello ,
I am trying to create a textbox whose background color will change based on the status of the latest build. I need this visualization on Dashboard rather than on canvas.

Thanks,
Sachin

What does the data look like? The text and field that is used to calculate color are the 2 helpful ones.

You should be able to do this in TSVB if your data is time series or Vega pretty easily depending on your data.

Thanks Aaron for responding ,
based on the build result , whether succeeded or failed , I want to create a textbox which will reflect the build number and the environment to which this build belongs (DEV, TEST, VAL, PROD)

data is like below
{
"buildNumber": "12146",
"result": "succeeded",
"status": "completed",
"tags": [
"dev"
],
"triggerInfo": {},
"triggeredByBuild": null,
"validationResults": ,
"environment": "DEV"
},

Thanks,
Sachin

So succeeded = green and failed = red?

Is this time series data? Do you have a date or @timestamp field identified in the index pattern?

yes i want to reflect as succeeded = green and failed = red
This is azure build pipeline data , yes this data also has a startTime and finishTime as
"startTime": "2020-10-20T12:04:26.474168+00:00"
"finishTime": "2020-10-20T12:09:12.631846+00:00"

I think this might need to be done in Vega. What does the expected visualization look like?

Is it 4 boxes, one for each zone (DEV, TEST, VAL, PROD). Then the build number along with the color coding? Or am I missing something and there are more than 4 boxes or more build numbers?

hi Aaron,
yes we need same as you mentioned , below is the mock that we want to create
image

Is your entire index only 4 records? Curious how you would pinpoint just those 4 records if there is a lot more in the index.

I have time this weekend where I can mock this up on Vega for you but having a better understanding of the index and mapping would help.

For example I was planning on using the below for a sample. Not sure if it's correct.

PUT color-change

POST color-change/_doc
{
"buildNumber": "12146",
"result": "succeeded",
"environment": "DEV",
"@timestamp": "2020-09-06T12:10:30Z"
}

POST color-change/_doc
{
"buildNumber": "12146",
"result": "succeeded",
"environment": "TEST",
"@timestamp": "2020-09-06T11:10:30Z"
}

POST color-change/_doc
{
"buildNumber": "12146",
"result": "succeeded",
"environment": "VAL",
"@timestamp": "2020-09-06T10:10:30Z"
}

POST color-change/_doc
{
"buildNumber": "12146",
"result": "failed",
"environment": "PROD",
"@timestamp": "2020-09-06T09:10:30Z"
}

Hi Aaron,
Our build data is having multiple records ,
we want to reflect latest build number based on the build finish time for a particular environment.

Understand. I should have time this weekend to mock something up for you.

Thanks Aaron for your help!!
This is urgent for us and appreciate you taking time out during the weekend.

Can you change the index name and run this to see if that's the results you wanted? The visualization seems like it might take quite a bit of work so let's just get the query first that returns the data.

GET CHANGEME/_search
{
  "size": 0,
  "aggs": {
    "group": {
      "terms": {
        "field": "environment.keyword"
      },
      "aggs": {
        "group_docs": {
          "top_hits": {
            "size": 1,
            "sort": [
              {
                "finishTime": {
                  "order": "desc"
                }
              }
            ]
          }
        }
      }
    }
  }
}
```Hi Aaron, 
Thanks for your previous reply, it took us into right direction.

Now we are able to have the required data based on data section of vega.
Our complete requirement is -
we have an index which have records of multiple projects.
So we want to fetch data for a single project -> then we need to fetch data for a particular environment(DEV, TEST, VAL, PROD) -> then we need to sort that data based on finish time in descending order -> and finally we want to fetch the top hit data -> then we need to display "buildid" field in the box -> and based on "result" field(Succeeded, Failed), we need to change the color of the box to green and red.

As of now, we have written this script for vega-

{ 
  "$schema":"https://vega.github.io/schema/vega/v3.json", 
  "data": [ { 
    "name": "aggregations",
    url: { 
    "index": "_index1",
    "body":  {
    "size": 0,
  "aggs": {
    "group": {
      "terms": {
        "field": "project.name.keyword"
        , "include": "project1"
      },
      "aggs": {
        "environmentdata": {
          "terms": {
            "field": "environment.keyword",
            "include": "DEV", 
          },
          "aggs": {
            "environmentdata_docs": {
                        "top_hits": {
            "size": 1,
            "sort": [
              {
                "finishTime": {
                  "order": "desc"
                }
              }
            ]          }
            }
          }        
        }        
      }
      }
    }
    }
  },
      "format": {"property" : "aggregations.group.buckets" },
       	 "transform": [
      {
    "type": "formula",
    "as": "id",
    "expr": "datum.environmentdata.buckets[0].environmentdata_docs.hits.hits[0].id"
    }
    ]
  } ],
  
   "marks": [ { 
    "type": "rect", 
    "from": { "data": "aggregations" }, 
    "encode": { 
      "update": { 
        "width": {"value": 100},         
        "height": {"value": 60},        
        "cornerRadius" : {value:10},
      } 
    } 
  },
  
  {
  "type" : "text",
  "from": {"data": "aggregations"},
      "encode": {
      "update": { 
      "text" : {"field": "id" }
      "align" : {"value": "right"}
      "fontSize": {"value": 30}
      }
      }
  }
  ] 
} 

data fetched from index if we run data section from dev tools-
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4598,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "group" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "project1",
          "doc_count" : 1596,
          "environmentdata" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "DEV",
                "doc_count" : 10,
                "environmentdata_docs" : {
                  "hits" : {
                    "total" : {
                      "value" : 10,
                      "relation" : "eq"
                    },
                    "max_score" : null,
                    "hits" : [
                      {
                        "_index" : "_index1",
                        "_type" : "_doc",
                        "_id" : "",
                        "_score" : null,
                        "_source" : {
                          "host" : "abc",
                          "queueOptions" : null,
                          "url" : "",
                          "triggerInfo" : { },
                          "ProjectName" : "project1",
                          "status" : "completed",
                          "quality" : null,
                          "BussinessUnit" : "BU",
                          "tags" : [ ],
                          "environment" : "DEV",
                          "controller" : null,
                          "finishTime" : "0000-00-00T00:00:00.000000+00:00",
                          "id" : 11111,
                          "buildNumber" : "00000000.0",
                          "result" : "succeeded",
                          "SolutionGroup" : "ABC",
                          "reason" : "manual",
                          "queuePosition" : null,
                          "project" : {
                            "abbreviation" : null,
                            "revision" : 0000,
                            "state" : "wellFormed",
                            "name" : "project1"
                          },
                          "deletedReason" : null,
                          "validationResults" : [ ],
                        },
                        "sort" : [
                          0000000000000
                        ]
                      }
                    ]
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Now can you please help on how to fetch  value of fields "result" and "id"  inside mark from second bucket.(For this, we have added transform block also, but it seems something is incorrect)
and then, based on  the value of field "result", we want the colors to be changed.

Thanks

I was going to do 1 query then just loop through it but by your response this way might be easier for you to use and adjust.

This method just does 4 different queries and saves in 4 different data tables. One for each zone.

Then you can see how I did the color coding. You can just apply the same technique to boxes to make it all look pretty.

{
  "$schema": "https://vega.github.io/schema/vega/v5.json",
  "title": "Pipeline Status",
  "data": [
    {
      "name": "table-dev",
      "url": {
        "index": "color-change",
        "body": {
          "query": {
            "bool": {
              "must": [{"term": {"environment.keyword": {"value": "DEV"}}}]
            }
          },
          "size": 1,
          "sort": {"finishTime": {"order": "desc"}}
        }
      },
      "format": {"property": "hits.hits"}
    },
    {
      "name": "table-prod",
      "url": {
        "index": "color-change",
        "body": {
          "query": {
            "bool": {
              "must": [{"term": {"environment.keyword": {"value": "PROD"}}}]
            }
          },
          "size": 1,
          "sort": {"finishTime": {"order": "desc"}}
        }
      },
      "format": {"property": "hits.hits"}
    },
    {
      "name": "table-test",
      "url": {
        "index": "color-change",
        "body": {
          "query": {
            "bool": {
              "must": [{"term": {"environment.keyword": {"value": "TEST"}}}]
            }
          },
          "size": 1,
          "sort": {"finishTime": {"order": "desc"}}
        }
      },
      "format": {"property": "hits.hits"}
    },
    {
      "name": "table-val",
      "url": {
        "index": "color-change",
        "body": {
          "query": {
            "bool": {
              "must": [{"term": {"environment.keyword": {"value": "VAL"}}}]
            }
          },
          "size": 1,
          "sort": {"finishTime": {"order": "desc"}}
        }
      },
      "format": {"property": "hits.hits"}
    }
  ],
  "marks": [
    {
      "type": "text",
      "from": {"data": "table-dev"},
      "encode": {
        "enter": {
          "text": {"field": "_source.buildNumber"},
          "fill": [
            {"test": "datum._source.result == 'succeeded'", "value": "green"},
            {"test": "datum._source.result == 'failed'", "value": "red"}
          ],
          "x": {"value": 10},
          "y": {"value": 10}
        }
      }
    },
    {
      "type": "text",
      "encode": {
        "enter": {
          "text": {"value": "DEV"},
          "x": {"value": 10},
          "y": {"value": 0}
        }
      }
    },
    {
      "type": "text",
      "from": {"data": "table-prod"},
      "encode": {
        "enter": {
          "text": {"field": "_source.buildNumber"},
          "fill": [
            {"test": "datum._source.result == 'succeeded'", "value": "green"},
            {"test": "datum._source.result == 'failed'", "value": "red"}
          ],
          "x": {"value": 50},
          "y": {"value": 10}
        }
      }
    },
    {
      "type": "text",
      "encode": {
        "enter": {
          "text": {"value": "PROD"},
          "x": {"value": 50},
          "y": {"value": 0}
        }
      }
    },
    {
      "type": "text",
      "from": {"data": "table-test"},
      "encode": {
        "enter": {
          "text": {"field": "_source.buildNumber"},
          "fill": [
            {"test": "datum._source.result == 'succeeded'", "value": "green"},
            {"test": "datum._source.result == 'failed'", "value": "red"}
          ],
          "x": {"value": 90},
          "y": {"value": 10}
        }
      }
    },
    {
      "type": "text",
      "encode": {
        "enter": {
          "text": {"value": "TEST"},
          "x": {"value": 90},
          "y": {"value": 0}
        }
      }
    },
    {
      "type": "text",
      "from": {"data": "table-val"},
      "encode": {
        "enter": {
          "text": {"field": "_source.buildNumber"},
          "fill": [
            {"test": "datum._source.result == 'succeeded'", "value": "green"},
            {"test": "datum._source.result == 'failed'", "value": "red"}
          ],
          "x": {"value": 130},
          "y": {"value": 10}
        }
      }
    },
    {
      "type": "text",
      "encode": {
        "enter": {
          "text": {"value": "VAL"},
          "x": {"value": 130},
          "y": {"value": 0}
        }
      }
    }
  ]
}

The output for this is simple but looks like the below.

image

1 Like

Thanks much Aaron for your quick response. Its really helped us a lot !!

Now, we want one more help from your side-

  1. Since we have created one control visualization of dropdown type where we are selecting the project name, so now, we want to fetch the data for vega visualization which you shared in your last post based on the project selected in dropdown.

For this point, we have read something about binding in signal inside vega, but not sure whether it could help, still exploring. Please guide us how to achieve this use case.

  1. We need the text to be enclosed in a box or rectangle for which color will be changed based on result(succeeded/failed).

Your help would be much appreciated !!!

Thanks

So if you want to pass in a control visualization then I'm not sure this method will work.

You have to set context to true in Vega in order to capture what that filter is passing in from a dashboard. When you do this you can no long use a query and can only use aggs for the body of the request.

As for #2 I can help out with that and make it look better when I get some time.

Ok, so meanwhile can you please help us on how to fetch specific field values from _source as we are able to get the required data with aggregations also, only part we are stuck is on how to fetch id and result fields from source inside text mark if we use aggregations.
Once this is resolved, then we can use context = true and maybe will get our use case worked.

Below is the structure of data we get-

  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4598,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "group" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "project1",
          "doc_count" : 1596,
          "environmentdata" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "DEV",
                "doc_count" : 10,
                "environmentdata_docs" : {
                  "hits" : {
                    "total" : {
                      "value" : 10,
                      "relation" : "eq"
                    },
                    "max_score" : null,
                    "hits" : [
                      {
                        "_index" : "_index1",
                        "_type" : "_doc",
                        "_id" : "",
                        "_score" : null,
                        "_source" : {
                          "id" : 11111,
                          "result" : "succeeded",
                        },
                        "sort" : [
                          0000000000000
                        ]
                      }
                    ]
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Thanks

@preeti1211 Here is what I got using my dataset. Also you might want to add another transformation for the order the boxes are displayed.

I don't think this is exactly what you are looking for but might give you a good idea on how to adjust it on your end.

{
  "$schema": "https://vega.github.io/schema/vega/v5.json",
  "data": [
    {
      "name": "table",
      "url": {
        "index": "color-change",
        "body": {
          "aggs": {
            "group": {
              "terms": {"field": "environment.keyword"},
              "aggs": {
                "group_docs": {
                  "top_hits": {
                    "size": 1,
                    "sort": [{"finishTime": {"order": "desc"}}]
                  }
                }
              }
            }
          },
          "size": 1,
          "sort": {"finishTime": {"order": "desc"}}
        }
      },
      "format": {"property": "aggregations.group.buckets"}
    },
    {
      "name": "table-flattened",
      "source": "table",
      "transform": [
        {
          "type": "formula",
          "as": "buildNumber",
          "expr": "datum.group_docs.hits.hits[0]._source.buildNumber"
        },
        {
          "type": "formula",
          "as": "environment",
          "expr": "datum.group_docs.hits.hits[0]._source.environment"
        },
        {
          "type": "formula",
          "as": "finishTime",
          "expr": "datum.group_docs.hits.hits[0]._source.finishTime"
        },
        {
          "type": "formula",
          "as": "result",
          "expr": "datum.group_docs.hits.hits[0]._source.result"
        },
        {"type": "identifier", "as": "boxposition"}
      ]
    }
  ],
  "scales": [
    {
      "name": "x",
      "type": "band",
      "range": "width",
      "padding": 0.1,
      "round": true,
      "domain": {"data": "table-flattened", "field": "boxposition"}
    }
  ],
  "marks": [
    {
      "type": "rect",
      "from": {"data": "table-flattened"},
      "encode": {
        "enter": {
          "fill": [
            {"test": "datum.result == 'succeeded'", "value": "green"},
            {"test": "datum.result == 'failed'", "value": "red"}
          ],
          "stroke": {"value": "#652c90"},
          "x": {"scale": "x", "field": "boxposition"},
          "y": {"value": 0},
          "width": {"value": 150},
          "height": {"value": 50},
          "opacity": {"value": 1},
          "cornerRadius": {"value": 3},
          "strokeWidth": {"value": 2},
          "stroke": {"value": "black"},
        }
      }
    },
    {
      "type": "text",
      "from": {"data": "table-flattened"},
      "encode": {
        "enter": {
          "text": {"field": "environment"},
          "x": {"scale": "x", "field": "boxposition", "offset": 75},
          "y": {"value": 20},
          "align": {"value": "center"},
          "fontSize": {"value": 20},
          "fill": { "value": "white" }
        }
      }
    },
    {
      "type": "text",
      "from": {"data": "table-flattened"},
      "encode": {
        "enter": {
          "text": {"field": "buildNumber"},
          "x": {"scale": "x", "field": "boxposition", "offset": 75},
          "y": {"value": 40},
          "align": {"value": "center"}
          "fill": { "value": "white" }
        }
      }
    }
  ]
}
1 Like

Thanks Aaron. It helped us a lot in achieving our usecase accurately.

your quick response for our problem statement is really appreciating.

Thanks
Preeti

1 Like

Hi Aaron,

I need your one more help.

Usecase-
We want a visualization which should depict the last 10 days build status for each environment(Dev,Test,Val,Prod) where cell color will show status/result of build - succeeded(green) and failed(Red).

Please suggest which visualization should be used for this use case and how can we achieve it.

We have tried using heatmap but it didnot help.
Can we done it using vega. If yes, how can we achieve it using vega.

Thanks