Importing Tags from XLSX into vScope

No Comments

vScope supports importing tags from a xlsx document. A xlsx document can be created in Microsoft Excel but also exported from a Google Sheets document.

Tag import from xlsx is performed through the vScope API:

POST http://<vscope ip:port>/rest/action/bulktag/xlsx

This endpoint takes a xlsx document as payload and with some query parameters explained further down you can control how the import is handled.

Example Import

Below is a very simple Google Sheets document with one sheet and two columns.

The first column specifies how we should find the row in vScope which will receive the tag. In this case we are simply using Name to find a match. 

The second column specifies what tag we should set on the matched row. Here we set the tag Administrative Contact on each matched row in vScope to the value on each row in the sheet.

In this example we could translate the rows above to:

  • Set Administrative Contact = Erik Eriksson on row where Name = ad-isl-test.isltest.local
  • Set Administrative Contact = Anders Andersson on row where Name = ad.isl.local
  • …

If any cell in Administrative Contact is empty it means that any existing value in vScope should be removed for the matched row when we apply changes.

You may match on any tag in vScope. You may also set any tag you like. The above data is only an example.

Let’s examine the machines we want to tag in vScope.

The machines we are interested in are the first four rows in the Virtual Machines resource table.

The API endpoint used for tagging is:

POST http://<vscope ip:port>/rest/action/bulktag/xlsx

This method is a POST method which expects the xlsx document as payload. It also expects some query parameters to be set. Let’s use our example to determine what we should set as values.

  • resourceType = Virtual Machines
    • Determines the resource we want as source for the rows we want to tag. In our example, we want to tag Virtual Machines.
  • matchColumn = Name
    • Specifies which column in the sheet contains the values we want to use to match rows to rows in vScope. In our example, we want to map the rows using the values in the Name column.
  • valueColumn = Administrative Contact
    • Specifies which column in the sheet contains the values we want to set on the matched rows in vScope. In our example, we want to set the values that are found in Administrative Contact.
  • matchTag = Name
    • Specifies which tag in vScope should be used to match the values in matchColumn. Since those values are names of the virtual machines we want to use the Name tag for matching.
  • targetTag = <empty>
    • Specifies the name of the tag we should set on the matched rows in vScope. If not specified, it will be same as valueColumn which in this case is Administrative Contact. This is enough for this example so we leave targetTag unset.
  • apply = false
    • Finally the apply parameter decides if changes should actually be applies or not. If set to false then vScope will only return results of what would happen if apply was true. Let’s try the first with apply set to false.

So, the request we should do:

POST http://<vscope ip:port>/rest/action/bulktag/xlsx?resourceType=Virtual%20Machines&matchColumn=Name&matchTag=Name&valueColumn=Administrative%20Contact&apply=false

Include the xlsx document in the POST as body and make sure that the Content-Type header in the request is set to appliation/octet-stream, otherwise vScope might not understand the payload in the POST:

Let’s perform the request. The reply will be in JSON format and look like below if everything went fine. Otherwise any error messages should provide help for debugging and rearranging data.

{
  "applied": false,
  "rowsTotal": 4,
  "rowsProblem": 0,
  "rowsUpdated": 4,
  "rowsNoChange": 0,
  "rowsRemoved": 0,
  "rows": [
    {
      "rowNumber": 2,
      "uuids": [
        "NTY0ZGY3ZjgtNDE0ZS00YzNkLTIzZDQtNTZiNjljZTlhMzYz"
      ],
      "action": "set",
      "targetTagName": "Administrative Contact",
      "targetTagValue": "Erik Eriksson"
    },
    {
      "rowNumber": 3,
      "uuids": [
        "NTY0ZDU4ZGMtNWEwYy00M2VlLTkwYzEtZDFmM2M2NzJhZjZm"
      ],
      "action": "set",
      "targetTagName": "Administrative Contact",
      "targetTagValue": "Anders Andersson"
    },
    {
      "rowNumber": 4,
      "uuids": [
        "NTY0ZGM4ODItMTBkZS0zZTRhLTY1MGYtMjJlMTQyYjNkY2Rh"
      ],
      "action": "set",
      "targetTagName": "Administrative Contact",
      "targetTagValue": "Anna Andersson"
    },
    {
      "rowNumber": 5,
      "uuids": [
        "NTY0ZGVlMDEtOTYwNC01OTFlLTc4NDUtNTdjYjE1ZDllZTIw"
      ],
      "action": "set",
      "targetTagName": "Administrative Contact",
      "targetTagValue": "Erika Eriksson"
    }
  ]
}

From the results we can see that 4 rows were processed in total and 4 rows will have their values updated if we run the same request with apply = true.

What tag and value is set on each row can be found in the rows field. The first row says that the Administrative Contact will be set to Erik Eriksson. The action is ‘set’ which means that there was no previous value. Action can be one of the following:

  • not_found
    • No matching row in vScope found. Will increment rowsProblem count.
  • multiple_found
    • Multiple matching rows in vScope found. Will increment rowsProblem count.
  • no_change
    • The value we want to set already is set on the row. There will be no change and rowsNoChange will be incremented.
  • set
    • No previous value existed and the value will be set. Increments rowsUpdated.
  • updated
    • Previous value existed and was replaced with the new one. Increments rowsUpdated.
  • removed
    • Previous value existed and was removed. Increments rowsRemoved.

The problematic actions are not_found and multiple_found which means that these rows will not be processed with apply=true.

Your goal should be to have a count of 0 in rowsProblem which means that the tag importer managed to map each row in the sheet to exactly one row in vScope.

So let’s try to apply the tags by using the same request but with apply set to true.

The result in vScope after completing the request and refreshing table explorer:

Feel free to experiment with imports and how you match rows in the sheet with rows in vScope. Remember that you can use apply=false to perform a dry run without causing changes.

Leave a Reply