Don't forget to register for our panel discussion with Dean Stoecker, Amy Holland, and Mark Frisch occurring next Wednesday, June 1!

2022-05-26 Updates: Email: If you're not seeing emails be delivered from the Community, please check your spam and mark the Community emails as not junk. Thank you for your patience.

Alteryx Server Knowledge Base

Definitive answers from Server experts.

How to Identify and Fix Null/Incorrect values in Users collection - MongoDB

Alteryx_KB
Alteryx
Alteryx
Created

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


When upgrading to an Alteryx Server version 2020.1 or later, users can run into issues during or after upgrade if they have specific items in MongoDB  (AlteryxGallery.users) that contain nulls. This article explains how to correct these null values.

If you have already run the Pre-Upgrade Check application (found here) and have the PDF report, go straight to the "Update Users" 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+

 

Prerequisites

 

 

Find Users with Null/Incorrect values

 
  1. Connect to the AlteryxGallery database using Robo 3T (See article to connect to Robo 3T).
  2. Once you connect to Robo 3T, expand AlteryxGallery, expand Collections, double-click users.
  3. Run the following commands to find the user records with null/incorrect values:
Users with null FirstName
db.users.find({ "FirstName" : null })
Users with null LastName
db.users.find({ "LastName" : null })
Users with null Email
db.users.find({ "Email" : null })
Users with null SubscriptionId
db.users.find({ "SubscriptionId" : null })
Users with Curator set to null
db.users.find( {$and : [ { "Curator" : { $exists: true } }, { "Curator" : null} ] } )
Users where the type of AccountLockedAt is anything other than a Date or Null
db.users.find({ $and : [ { "AccountLockedAt" : { $not : { $type : 9 } } }, { "AccountLockedAt" : { $ne : null } } ] })


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

  1. If no results are returned, no nulls were found and no updates need to be made.
  2. If results are returned, you will see all the users that need to be updated. See the section below.
    • If you need to identify how many users are found, add .count() to the end of the query:
db.users.find({FirstName:null}).count()

 

Update Users

 

Update Users via Gallery


As much as possible, you should make edits from the Users page in Gallery > Admin > Users. 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 users in the Gallery so you can make the necessary edits:
  1. 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.
  1. Navigate to the Gallery Admin section > Users in a browser.
  2. Paste the value into the search box.
  3. Click on the user and make the necessary updates.
  4. Repeat for all users returned by the report/query.
 

Update Users directly in the database (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.
 
  1. Right-click each document (identified from above) and click Edit Document...
    • If you have the output of the Pre-Check workflow, you can copy the Mongo ID query from the PDF. You can paste these into the grey box and press the green Play button to execute them:
  1. Enter the appropriate values
***Please note***: these are key-value pairs. The Key is case-sensitive, and values must be entered as their corresponding types. If you would like assistance with the manual database edits, please contact Alteryx Support .
  • Examples of appropriate values
FieldExample ValueType
FirstName
"FirstName" : "John",
String
LastName
"LastName" : "Doe",
String
Email
"Email" : "jdoe@company.com",
String
SubscriptionId
"SubscriptionId" : "5ba32738bbc49613444282c5",
String
Curator
"Curator" : true,
Boolean
AccountLockedAt
"AccountLockedAt" : ISODate("2021-07-20T11:38:44.311+0000"),
"AccountLockedAt" : null,
Date or Null
  1. Click the Validate button in the bottom-left corner to ensure the data is entered in proper JSON format.
  2. Click Save
  3. Repeat this for all necessary objects.

 

Update Users 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.

Alternatively, you can update all the offending fields by query.

You will need the ObjectId of the user. To locate:
  1. Click the > icon to expand the object.
  2. Locate _id and right-click the corresponding Value. Select Copy Value
Set FirstName
db.users.update({_id : ObjectId("OBJECTIDOFUSER")}, {$set : {FirstName : "VALUEGOESHERE"}})
Set LastName
db.users.update({_id : ObjectId("OBJECTIDOFUSER")}, {$set : {LastName : "VALUEGOESHERE"}})
Set Email
db.users.update({_id : ObjectId("OBJECTIDOFUSER")}, {$set : {Email : "VALUEGOESHERE"}})
Set SubscriptionId
db.users.update({_id : ObjectId("OBJECTIDOFUSER")}, {$set : {SubscriptionId: "VALUEGOESHERE"}})
Set Curator to a Boolean
db.users.update({_id : ObjectId("OBJECTIDOFUSER")}, {$set : {Curator: false}})
Set AccountLockedAt to a null
db.users.update({_id : ObjectId("OBJECTIDOFUSER")}, {$set : {AccountLockedAt: null }})



If you need to set multiple nulls at a time, please contact Alteryx Support.



    Additional Resources

     
    No ratings