How to Run a Batch Update of All Customer Records for Web Access

Status:	Published
Version:	1.0
Authors:	HTL QA
Applies to:	Supportworks ESP

How to Run a Batch Update of All Customer Records for Web Access

This document gives details of how you can quickly give all customers access to SelfService, by suitably updating their records all at once using SQL, rather than having to go through each customer's record individually in the normal way.

The easiest way to achieve this is to set up one customer's access options as you require using the normal client GUI, then obtain the underlying numerical value of this set of options from the database (via SQL) and finally apply this value to all customer records (again via SQL). This simple method assumes that you want all customers to end up with exactly the same set of access options. If you want different kinds of customers to have different degrees of Web (or Auto Responder) access, then you would have to pick one "model" customer of each kind and repeat the exercise that many times, applying each set of access options according to the relevant customer criteria.

The following instructions are applicable to the simple scenario, and it is assumed that your database is SwSQL. For other databases, you should contact your Database Administrator to see what query tools are available. Before proceeding, please ensure that you have a full backup of your Customer table (userdb).

1. Log into Supportworks via the client and set the required Web access options for a given customer. This information is on the Customer Details Form and the section may be called "SelfService Rights" or "Access to Service Desk". Once the change has been made, make a note of the Customer ID.
2. Run the Interactive SQL program by selecting Start > Programs > Hornbill Core Services > Interactive SQL.
3. Enter the following SQL statements (replacing Customer-ID with the Customer ID you noted in Step 1):

   use swdata;

   select keysearch, webflag from userdb where keysearch = 'Customer-ID';

4. Note the WebFlag value from the results returned and enter the following SQL statement (replacing value with the actual returned value):

   update userdb set webflag = value;

   On completion of the update, all customer records will have the same access options.

5. You can set the same access password for each customer by using the following SQL statement (replacing password with the required password). Please consider the security implications of this approach before proceeding:

   update userdb set password = 'password';

   The password here would typically be the Supportworks (or blank) password you initially wish to allocate to customers, which they must subsequently change if your Supportworks application allows it. If changing the password is not possible on the application, you could use an appropriate expression to compute a unique but memorable password for each customer.

   Alternatively, if you require customer access using NT domain or Microsoft Active Directory authentication, you can enter either of the following SQL statements, as appropriate:

   update userdb set password = '_NTD:NT-domain'; (for NT domain authentication)

   update userdb set password = '_ADS:concat(firstname,'.',surname,'@domain')'; (for Microsoft Active Directory authentication)

   Note that, for NT domain or Microsoft Active Directory authentication, the Customer ID in each record must be the same as the customer's NT ID or User Principal Name (UPN), as appropriate.

The changes you have made will now be immediately available.