How to amend a ConneX status in order to delete the workspace

There are instances where you may see that your workspace has not provisioned as expected, for example the workspace has been displaying a Pending state for a longer period of time than anticipated.

In the Pending state, the workspace may not be deleted since the option is not available from the drop down. To resolve this issue, we will need to modify the workspace state in the Atlas Database. Please see below for the steps.

Please note: This workspace was never actually provisioned (as it was in a Pending state) and so this workspace does not really exist in the tenant.  This process is carried out so that the workspace may be deleted from Atlas and no longer shown in ConneX.

Pre-requisites

Your user will need SharePoint Global Admin permissions.

Obtain the Atlas Database Password

First we will need to access the password that Atlas uses to access its internal database.  

• Open the Atlas Resource Group (rg-atlas-xxxx-xxxx) in your browser.  Locate the App Service for atlas (app-atlas-api-XXXXXX-XXXX) with API in the name.  Select this.

• From the API locate the Connection Strings section in the Configuration under Settings, and locate the AtlasConnexContext.  When you select this item, you will see the Database Connection String Used by Atlas to connect to the SQL Server backend.

• In the Value field locate the current Password in the string.  Copy this password to your clipboard or notepad to be used later.

Create Firewall Rule to Access the SQL Database

Next we need to add the computer\laptop IP address to the SQL Server firewall settings in order to connect to the database.

• Locate the SQL Server in the Resource Group.  This will be identified with the type SQL Server.  Select it.

• From the SQL Server, under Security and Networking, go to Firewall Rules and you will see the Add your client IPv4 address (with your IP).  Click on this, and then Save the settings.

 

Connect to the SQL Database to run Queries

We can now connect to the Atlas SQL Database.

• Locate the SQL Database resource within the Resource Group.  This will be identified as SQL Database.  Select it.

• From the SQL Database click on Query Editor (Preview) .  You will be prompted to sign in with credentials.  Paste the password that you copied in section Get Atlas Database Password above.
• We will first list any workspaces that have a Status value different from 2.  (Status = 2 is the state of a workspace that is correctly provisioned in Atlas).  The query in the image below will return the Workspace ID, Title and Status columns for all Workspaces whose Status is different from 2.  This may return more than one value, and you may need to review closely the results returned to identify the workspace you are looking for.

• Another Option is to look for the Workspace Name (Title).  This might be able to narrow the possibilities down a bit further.  In the query we will use the title field and try to locate any workspace with a name that contains part of a specific string (below example the query is looking for a workspace that has the text Team01 in the title.  We use % to indicate wildcards before and after the word.

• We now have the ID of the workspace(s) we wish to change the status of.  We will run an Update Query to change the value of the status column to 2 for the specific workspace, that we will identify via the ID column.  The result will indicate that 1 row has been affected by the query.  IMPORTANT:  The Where Clause is important as without a Where clause in the query you may change all workspaces to a status value 2.  We use the ID of the workspace in the Where clause as this is the Primary Key of the Table and will always be unique.

• We can now verify the status of the updated row.

Unfortunately, this on its own, is not enough.  We can see that the status of the workspace won’t change.  Even if we refresh ConneX Workspaces, we will still see the Pending status.

We will have to verify the status on the Atlas Search Service.

• Access the the Atlas resource Group (rg-atlas-xxxx-xxxx) in your browser.  Locate the Search Service for your atlas deployment (srch-atlas-XXXXXX-XXXX). Click on the Search Service resource.

• From the Search Service Resource, in the Overview we click on Indexes and click on workspaces.

• From the workspaces search explorer we will be able to confirm that the Search Index is still showing the Pending Status (value 0) instead of the value we updated in the Atlas Database previously.  To verify this we will need to:
• Add the following Query in the Query String.  The GUID that is added will be specific to your Workspace (below is used for this documentation only).  Same ID used in the SQL Database Query, previously.

$filter=id eq 'Workspace GUID'
$filter=id eq 'bc4ea4d2-14a0-4baa-b13f-dcf6fe1ec4d0'

• Select the corresponding API Version (there are various Preview versions, I have selected non preview version of the API – 2020-06-30).
• Click on Search button.  This will bring out the query results and if we scroll down until we find “status” we should see that the index still has a 0 value.

To change this value, to the same value we updated the status value in the SQL Database, we will need to use an Application like Postman (https://www.postman.com/downloads/).  This application will allow you to update the Index value via a Post request.  This Application is free, and you will be able to use it exclusively for this correction without signing up in the portal (this just requires you provide an email address).  If you do decide to keep the application for future use, it will definitely be worth your while to sign-up.  Download the version of the App and install on your PC/Laptop.  REMEMBER to uninstall the App when the workspace(s) have been corrected.

• Once the application is installed you can click on a link that allows you to use the application without signing in.

We will now begin setting up the Post Request for this:

• From the URL box select POST from the dropdown.
• You will now paste the following text in the URL box.

The SearchResourceName (srch-atlas-XXXXX-XXXX) will be taken from the name of the Search Service Resource in the Atlas Resource Group.

Similar to the below image:

• Click on the Header Pane and add 2 keys:
  • • Content-Type.  Here you simply type application/json
    • api-key. Here you will need to get one of the Search Service admin Keys.

The Search Service API keys can be found clicking on the search service (srch-atlas-XXXXXX-XXXX) in the Atlas Resource Group.  Select Keys from the left panel. 

• Copy this Key to the Postman Application.  See below image.
  
  
• In the Postman application, click on the Body panel.  Select raw and enter the GUID in id.  (The GUID in id is the Workspace GUID mentioned previously in this document.  Yours will be different from the one below).

Image below shows the body section:

• Once the above has been entered we can click the Send button in the Postman application.  The result returned should have a status code of 200 and look like below image.

We should now be able to refresh ConneX and search for the workspace that was previously showing as Pending. This should now appear as provisioned with the possibility of selecting Delete:

REMEMBER: This workspace was never actually provisioned (as it was in a Pending state). So this workspace does not really exist in the tenant. This process is carried out so that the workspace may be deleted from Atlas and no longer shown in ConneX.