Alteryx Server Knowledge Base

Alteryx_KB · ‎08-09-2021

How to Identify and Fix Null/Incorrect values in Subscriptions collection

When upgrading to an Alteryx Server version 2020.1 or later, users can run into issues during or after upgrade if they have Subscriptions (Paid Subscriptions) that contain null Expiration Dates. This article explains how to correct these null values.

If you are upgrading to 2020.3+, you do not need to update these values.

If you have already run the Pre-Upgrade Check application (found here) and have the PDF report, go straight to the "Update Subscriptions with Null ExpirationDate/ExpDate by Query" section for steps on how to correct the identified users.
Please refer to the article above if you would like to run an Alteryx application to check these values, rather than the MongoDB queries below.

Environment

Alteryx Server
- Upgrading to version 2020.1 or 2020.2

If you are upgrading to a later version, you do not need to update these values.

Prerequisites

Robo 3T. Install Link
Take a backup of the database: MongoDB Backups | Alteryx Help

Procedure

Find Subscriptions with null ExpirationDate/ExpDate

Connect to AlteryxGallery using Robo 3T (See article to connect to Robo 3T)
Once you connect to Robo 3T, expand AlteryxGallery, expand Collections, double-click subscriptions.
Run the following commands to find the subscriptions where Type=2 and ExpDate is null:

db.subscriptions.find({$and: [{ExpDate : null}, {Type : 2}]})

You can paste these into the grey box and press the green Play button to execute them:

If no results are returned, no nulls were found and no updates need to be made.
If results are returned, you will see all the subscriptions that need to be updated. See the section below.
- If you need to identify how many subscriptions are found, add .count() to the end of the query:

db.subscriptions.find({$and: [{ExpDate: null}, {Type : 2}]}).count()

Update Subscriptions

Update Subscriptions via Gallery

As much as possible, you should make edits from the Users page in Gallery > Admin > Subscriptions. If you run into issues making the changes via the Gallery, advanced users can make the edits directly in the database by following the steps below. If you would like assistance with the manual database edits, please contact Alteryx Support.

To locate the subscription in the Gallery:

Copy the alphanumeric ID value returned from the query. The id is the highlighted part below:

({ "_id" : ObjectId("7909a4618f6e460798ba39dc923b87cc") }

If you have the PDF output of the Pre-Check application, you can copy the Mongo ID from the "MongoID Query" field. It will also look like the example above.

Navigate to the Gallery Admin section > Subscriptions.
Paste the value into the search box.
Click on the user and make the necessary updates.
Repeat for all users returned by the report/query.

Update Subscriptions with Null ExpirationDate/ExpDate by Query (Advanced)

PLEASE NOTE: Making direct edits to the database is only for advanced usage. You must have a database backup in place prior to making any changes.

This field is safe to mass-update with the following queries. This must be set to a date in the future.

Run the following command in order to update the null ExpirationDate and ExpDate fields. These commands will update all the offending subscriptions.

db.subscriptions.update({$and: [{ExpDate: null}, {Type : 2}]}, {$set: {"ExpDate" : ISODate("2055-10-05T13:57:02.346Z")}}, {multi:true})

Examples of appropriate values:

"ExpDate" : ISODate("2027-05-30T09:45:34.808Z"),

Please note: ExpDate is a DateTime value. See link to MongoDB Schema for Alteryx Server 2019.4 .