Updating User Card Numbers Using SQL

KB Home   |   Updating User Card Numbers Using SQL

Main.UpdatingUserCardNumbersUsingSQL History

Hide minor edits - Show changes to output

February 23, 2016, at 07:33 PM by Kyle Baggott - Broken link
Changed line 4 from:
# the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]),
to:
# the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id.html#ch-user-mgmt-card-id-update | here]]),
Changed lines 3-6 from:
Some large sites maintain an external database of user's and their card numbers.   We usually recommend sites use the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]), or directly from Active Directory.  However in some situations it is more convenient to use SQL to efficiently update the user's card numbers (and optionally card `PINs) in PaperCut.

The approach involves creating and populating an import table and then running an SQL statement to update the card numbers on the PaperCut users.
to:
Some large sites maintain an external database of user's and their card numbers.  To make use of these card numbers in PaperCut we usually recommend sites use one of the built-in options:
# the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]),
# directly from Active Directory or LDAP (see [[https://www.papercut.com/products/ng/manual/ch-sys-mgmt-
user-group-sync.html#ch-sys-mgmt-user-imp-card-id | here ]]).
# the SQL card look-up feature.  This allows you to define your own SQL statement to match a user to a card number.  This is described in detail in our manual [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-db-lookup.html | here]].

However in some situations it is more convenient to use SQL to efficiently update the user's card numbers (and optionally card `PINs) in PaperCut.
The approach involves creating and populating an import table and then running an SQL statement to update the card numbers on the PaperCut users.
Changed line 114 from:
----
to:
----
Changed lines 43-44 from:
This is done with the following SQL statement:
to:
The following is an example SQL for Microsoft SQL Server.  ```IMPORTANT: If you modify this for another DB ensure that the card number is lower-cased and trimmed of white-space when copying into PaperCut.
Added line 111:
----
Changed lines 34-35 from:
* @@card_number@@ field must be unique, and should be normalized to lower-case and remove leading and trailing spaces.
to:
* @@card_number@@ field '''must''' be unique, and '''must''' be normalized to lower-case and remove leading and trailing spaces.
Changed lines 94-95 from:
Is is important to ensure no duplicate card numbers are imported into PaperCut.  If two users are assigned the same card number, then neither user will be able to use the card number for authentication in PaperCut.
to:
Is is '''very important''' to ensure no duplicate card numbers are imported into PaperCut.  If two users are assigned the same card number, then neither user will be able to use the card number for authentication in PaperCut.
Changed lines 3-4 from:
Some large sites maintain an external database of user's and their card numbers.  We usually recommend sites use the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]), or directly from Active Directory.  However in some situations it is more convenient to use SQL to efficiently update the user's card numbers (and optionally card PINs) in PaperCut.
to:
Some large sites maintain an external database of user's and their card numbers.  We usually recommend sites use the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]), or directly from Active Directory.  However in some situations it is more convenient to use SQL to efficiently update the user's card numbers (and optionally card `PINs) in PaperCut.
Added lines 92-93:
This method should be used to only update ''existing'' users in the database.  PaperCut does not support adding new users via SQL.
Changed lines 105-106 from:
If you have any questions about this procedure please feel free to contact our development team at support@papercut.com .
to:
If you have any questions about this procedure, or need to make a modification to support custom requirements and would like ti checked, please feel free to contact our development team at support@papercut.com .
Changed lines 3-4 from:
Some large sites maintain an external database of user's and their card numbers.  We usually recommend sites use the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]), or directly from Active Directory.  However in some situations it is more convenient to use SQL to efficiently update the user's card numbers in PaperCut.
to:
Some large sites maintain an external database of user's and their card numbers.  We usually recommend sites use the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]), or directly from Active Directory.  However in some situations it is more convenient to use SQL to efficiently update the user's card numbers (and optionally card PINs) in PaperCut.
Added line 54:
     and tbl_user.internal = 'N'
Changed lines 64-81 from:
to:
The above SQL will only update users that are in the @@ext_user_card_number_import@@ table.  If you would prefer to clear the card number field in PaperCut if the user doesn't exist in the @@ext_user_card_number_import@@ table then you should also run the following SQL statement.

 update tbl_user set
    card_number = '',
    modified_date = GETDATE(),
    modified_by = '[external card number import]'
 where
tbl_user.deleted = 'N'
and tbl_user.internal = 'N'
and tbl_user.card_number <> ''
and not exists (
select user_name
from ext_user_card_number_import eucn
where
tbl_user.user_name = eucn.user_name
)

Changed line 90 from:
''Categories:'' [[Category.Miscellaneous | +]]
to:
''Categories:'' [[Category.Scripting | +]]
Changed lines 87-90 from:
If you have any questions about this procedure please feel free to contact our development team at support@papercut.com .
to:
If you have any questions about this procedure please feel free to contact our development team at support@papercut.com .

----
''Categories:'' [[Category.Miscellaneous | +]]
Changed lines 3-4 from:
Some large sites maintain an external database of user's and their card numbers.  We usually recommend sites use the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]), or import from Active Directory.  However in some situations it is more convenient to use SQL to efficiently update the user's card numbers in PaperCut.
to:
Some large sites maintain an external database of user's and their card numbers.  We usually recommend sites use the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]), or directly from Active Directory.  However in some situations it is more convenient to use SQL to efficiently update the user's card numbers in PaperCut.
Changed lines 3-4 from:
Some large sites maintain an external database of user's and their card numbers.  We usually recommend sites use the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]).  However in some situations it is more convenient to use SQL to efficiently update the user's card numbers in PaperCut.
to:
Some large sites maintain an external database of user's and their card numbers.  We usually recommend sites use the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]), or import from Active Directory.  However in some situations it is more convenient to use SQL to efficiently update the user's card numbers in PaperCut.
Changed lines 60-63 from:
* Updating 1 user's card number - less than 1 second.


to:
* Updating 1 user's card number in 350,000 users - less than 1 second.


Added lines 86-87:

If you have any questions about this procedure please feel free to contact our development team at support@papercut.com .
Changed line 17 from:
Create a table in the PaperCut database called @@ext_user_card_number_import@@.  This table should be defined with the following fields:
to:
Create a table in the PaperCut database (in the SQL Server instance PaperCut is currently running on) called @@ext_user_card_number_import@@.  This table should be defined with the following fields:
Changed lines 21-24 from:
A script for creating this table can be downloaded here: [[Attach:create_user_card_table.txt | create_user_card_table.sql ]].


to:
A script for creating this table can be downloaded here: [[Attach:create_user_card_table.txt | create_user_card_table.sql ]].  This should be run using the SQL Server Management Studio or equivilent.


Changed line 27 from:
The next step is to populate the import table (@@ext_user_card_number_import@@) with usernames and card numbers from the external system. How this is achieved is depends on where this data is stored and the format of the data.  Examples of how the import table can be populated may include:
to:
The next step is to populate the import table (@@ext_user_card_number_import@@) with usernames and card numbers from the external system. How this is achieved is depends on where this data is stored its format.  Examples of how the import table can be populated may include:
Changed lines 30-31 from:
* If the data is in the same SQL server instance it may be possible to import it directly with a single SQL statement.
to:
* If the data is in the same SQL server instance it may be possible to import it directly with a single cross-database SQL statement.
Changed lines 43-44 from:
This can be done with the following SQL statement:
to:
This is done with the following SQL statement:
Changed lines 56-57 from:
to:
Manually run this SQL command and verify that numbers are updating as expected.
Added lines 62-63:

Changed line 85 from:
to:
SQL Administrators may wish to run this command from time to time.
Changed line 27 from:
The next step is to populate the import table with usernames and card numbers from the external system. How this is achieved is depends on where this data is stored and the format of the data.  Examples of how the import table can be populated may include:
to:
The next step is to populate the import table (@@ext_user_card_number_import@@) with usernames and card numbers from the external system. How this is achieved is depends on where this data is stored and the format of the data.  Examples of how the import table can be populated may include:
Changed lines 19-20 from:
* card_number varchar(50) not null (unique index)
to:
* card_number varchar(100) not null (unique index)
Changed lines 39-40 from:
!! 4. Run script to update card numbers in PaperCut
to:
!! 4. Run SQL to update card numbers in PaperCut
Changed lines 73-74 from:
If card number are never reassigned between users, then it will not be possible to create duplicate card numbers in PaperCut using this technique.
to:
If card numbers are never reassigned to users, then it should not be possible to create duplicate card numbers in PaperCut using this technique.
Changed lines 39-40 from:
!! 4. Run script to update user's in PaperCut
to:
!! 4. Run script to update card numbers in PaperCut
Changed lines 71-72 from:
Is is important to not have duplicate card numbers imported into PaperCut.  If two users are assigned the same card number, then neither user will be able to use the card number for authentication in PaperCut.
to:
Is is important to ensure no duplicate card numbers are imported into PaperCut.  If two users are assigned the same card number, then neither user will be able to use the card number for authentication in PaperCut.
Changed lines 75-76 from:
To check that there are no duplicate card numbers in PaperCut, the followin query can be run and should return no results.
to:
To check that there are no duplicate card numbers in PaperCut, the following query can be run and should return no results.
Changed lines 69-82 from:
to:
!! Note on data consistency

Is is important to not have duplicate card numbers imported into PaperCut.  If two users are assigned the same card number, then neither user will be able to use the card number for authentication in PaperCut.

If card number are never reassigned between users, then it will not be possible to create duplicate card numbers in PaperCut using this technique.

To check that there are no duplicate card numbers in PaperCut, the followin query can be run and should return no results.

 select card_number, count(*)
 from tbl_user
 where deleted = 'N' and (card_number is not null and card_number <> '')
 group by card_number having count(*) > 1

Changed lines 5-6 from:
The approach involves creating and populating an import table and then running an SQL statement to updating the card numbers on the PaperCut users.
to:
The approach involves creating and populating an import table and then running an SQL statement to update the card numbers on the PaperCut users.
Changed lines 58-60 from:
* Updating every user card number - 30 seconds.
* Updating every 1 user's card number - less than 1 second.
to:
* Updating all 350,000 users' card numbers - 30 seconds.
* Updating 1 user's card number - less than 1 second.
Changed lines 30-31 from:
* If the data is in the same SQL server instance it may be able to be imported with a single SQL statement.
to:
* If the data is in the same SQL server instance it may be possible to import it directly with a single SQL statement.
Changed lines 21-24 from:
A script for creating this table can be downloaded here: Attach:create_user_card_table.txt.


to:
A script for creating this table can be downloaded here: [[Attach:create_user_card_table.txt | create_user_card_table.sql ]].


Added lines 1-69:
(:title Updating User Card Numbers Using SQL :)

Some large sites maintain an external database of user's and their card numbers.  We usually recommend sites use the built-in card import (see [[https://www.papercut.com/products/ng/manual/ch-user-mgmt-card-id-update.html | here]]).  However in some situations it is more convenient to use SQL to efficiently update the user's card numbers in PaperCut.

The approach involves creating and populating an import table and then running an SQL statement to updating the card numbers on the PaperCut users.

This article describes how to achieve this using Microsoft SQL Server database.

!! 1. Prerequisites

* PaperCut must be configured to run on Microsoft SQL Server as described [[https://www.papercut.com/products/ng/manual/ch-ext-db.html | here]].
* A source of card numbers linking them to usernames is required.
* Familiarity with SQL Server to run and configure database scripts.

!! 2. Create the card import table in the PaperCut database.

Create a table in the PaperCut database called @@ext_user_card_number_import@@.  This table should be defined with the following fields:
* user_name varchar(50) not null (primary key)
* card_number varchar(50) not null (unique index)

A script for creating this table can be downloaded here: Attach:create_user_card_table.txt.



!! 3. Populate the card import table

The next step is to populate the import table with usernames and card numbers from the external system. How this is achieved is depends on where this data is stored and the format of the data.  Examples of how the import table can be populated may include:
* Write a custom program or script to transform and import the data into the table.
* Use SQL Server integration services to import the data from another data source.
* If the data is in the same SQL server instance it may be able to be imported with a single SQL statement.

The following rules should be applied to imported data:
* @@user_name@@ field should match a name in the the @@tbl_user.user_name@@ field.
* @@card_number@@ field must be unique, and should be normalized to lower-case and remove leading and trailing spaces.

This step should be scheduled to regularly when card numbers in the source system are updated.


!! 4. Run script to update user's in PaperCut

Once the import table @@ext_user_card_number_import@@ is populated/updated, the PaperCut user records can be updated with the new card numbers. 

This can be done with the following SQL statement:

 update tbl_user set
    card_number = LOWER(LTRIM(RTRIM(eucn.card_number))),
    modified_date = GETDATE(),
    modified_by = '[external card number import]'
 from
    tbl_user inner join ext_user_card_number_import eucn
        on tbl_user.user_name = eucn.user_name
 where
    tbl_user.deleted = 'N'
    and tbl_user.card_number <> eucn.card_number


This statement is very efficient, only updating the records that require updating.  Performance testing on database containing 350,000 users and 350,000 records in the @@ext_user_card_number_import@@ table showed:
* Updating every user card number - 30 seconds.
* Updating every 1 user's card number - less than 1 second.

!! 5. Scheduling regular updates

To keep the PaperCut card numbers up-to-date with the source card system, steps 3 and 4 above should be scheduled to run on a regular basis.

If steps 3 and 4 are scheduled independently, ensure that step 4 is scheduled to start after step 3 has completed.



Comments

Share your findings and experience with other PaperCut users. Feel free to add comments and suggestions about this Knowledge Base article. Please don't use this for support requests.

Article last modified on February 23, 2016, at 07:33 PM
Printable View   |   Article History   |   Edit Article