Skip to main content
Older versions of Internet Explorer will not support certain site features. Chrome, Safari, Firefox, and Edge will provide the best experience.
Spok

Available SQL Scripts

This topic lists the SQL scripts available to run to identify problems with Spok Mobile registrations.

After the Spok Mobile product is installed, SQL scripts are available which can perform different functions within the system.

Corrupted Registrations SQL Scripts

These SQL scripts allow you to identify the list of registrations that are out-of-sync for configurations of Spok Mobile that are used with the Smart Suite product. Out-of-sync registrations can be caused by product changes involving the LocalDeviceID. These changes can cause problems with synchronization where registrations exist in one server and not the other server(s).

In order to identify corrupted registrations, you first need to get a list of registrations on three servers: Smart Suite (Host), Premise (MCS), and Hosted (MCH) for comparison.

To easily identify the registrations that are out-of-sync in the system, SQL scripts are provided for the 3.5, 4.0 and later versions of the Spok Mobile product.

The scripts return the following information:

  • SiteId
  • RegUserID
  • PhoneNumber
  • LDI
  • Email
  • Registration Status
  • AmcVendorDeviceId
  • RegistrationId

3.5

For version 3.5.x of Spok Mobile, the registrations_amcs_35.sql script can identify the out-of-sync registrations in the system.

registrations_amcs_35.sql This script is executed on the Premise (MCS) database.

Sample output file name: mcs_registrations.txt
 

0000800003|421298|john.smith@email.com |11560-2

0000800004|421278|john.smith@email.com |11560-2

0000800005|421277|john.smith@email.com |11560-2

0000800006|421299|john.smith@email.com |11560-2

1119977780|436037|mary.miller@email.com|11560-2

4.0+

For versions 4.x and later of Spok Mobile, the scripts below can identify the out-of-sync registrations in the system.

Hosted

registrations_amch_40.sql This script must be executed on the Hosted (MCH) database. A member of the Hosted Operations team needs to run this query. The script produces a spreadsheet that lists all registrations for that customer across all sites. Save the relevant columns from the spreadsheet in their original order as a CSV file. Then open the file with a text editor and replace ‘,’ with ‘|’ and rename the file to mch_registrations.txt.

 

Services

registrations_amcs_40.sql This script must be executed on the (MCS) database.

Sample output file name: mcs_registrations.txt
 

0000800003|421298|john.smith@email.com |11560-2

0000800004|421278|john.smith@email.com |11560-2

0000800005|421277|john.smith@email.com |11560-2

0000800006|421299|john.smith@email.com |11560-2

1119977780|436037|mary.miller@email.com|11560-2

 

Smart Suite Systems

For versions 4.x and later of Spok Mobile, the scripts below can identify the out-of-sync registrations on systems that are using a Smart Suite host system.

registrations_ss.sql This script must be executed on the Smart Suite database as the “atms” user.

Sample output file name: ss_registrations.txt
 

0000800003|421298|john.smith@email.com|11560-2

0000800004|421278|john.smith@email.com |11560-2

0000800005|421277|john.smith@email.com |11560-2

0000800006|421299|john.smith@email.com |11560-2

1119977780|436037|mary.miller@email.com |11560-2

What to Do With the Results

You can compare the query results in various ways. The following process describes one option. Because it uses a Linux diff command, all output files should be copied to a Linux server, such as the customer’s database server where the Oracle script was executed.

Data in each file has to be formatted the same way and sorted by the phone number. The format of the data should be the following: “phone#|LDI|EmailAddress|SiteID”.

Each query returns the current registrations sorted by telephone number. The results from the three queries can then be meaningfully compared. Specifically, for each site ID and telephone number, the LocalDeviceIDs must match. Please note that if two or more records with the same Site ID and telephone number are returned, these records must be inspected carefully.

Please note that an outline of the process is included below, but more detailed information on how to use the Smart Suite Database Server product can be found in Installing Smart Suite Database Server RHEL7.

To compare the results, perform the following steps:

  1. Identify the customer’s Site ID(s).

  2. Send a request to the Hosted Operations team, requesting the Hosted registrations for all sites, using queries found in the registrations_mch_40.sql script. You can reach the Hosted Operations team at hosted.monitoring@spok.com.

  3. Log into the Smart Suite Database server.

  4. In the Smart Suite Database server, start up SqlPlus as “atms” user.

  5. Run the registrations_ss.sql script.

  6. Verify that the query produces a ss_registrations.txt file.

  7. Remove the extra entries from the output file.

  8. Log into the Spok Mobile Enterprise server. For information on how to log into the Spok Mobile Enterprise environment, see Administrator’s Guide Spok Enterprise Administration.

  9. Using the SQL Server Management studio, run the query from the registrations_mcs.sql script. 

  10. Capture the output into a file called mcs_registrations.txt.

  11. Move the files to the Smart Suite database server.

  12. Sort the three files using Linux sort command such as the following:
    • sort ss_registrations.txt > ss_customer
    • sort mcs_registrations.txt > mcs_customer
    • sort mch_registrations.txt > mch_customer
  13. Run the Linux diff commands on the output files. For example:

    1. Compare the Smart Suite registrations against the Spok Mobile Enterprise (MCS) registrations.
      • diff -Bb ss_customer mcs_customer | grep ">"  | awk ‘{ print $2 }’  >  in_mcs_but_not_in_ss  
      • diff -Bb ss_customer mcs_customer | grep "<"  | awk ‘{ print $2 }’  >  in_ss_but_not_in_mcs
         
    2. Compare the Spok Mobile Enterprise (MCS) registrations against the Hosted (MCH) registrations.
      • diff -Bb mcs_customer mch_customer | grep ">"  | awk ‘{ print $2 }’  >  in_mcs_but_not_in_mch         
      • diff -Bb mcs_customer mch_customer | grep "<"  | awk ‘{ print $2 }’  >  in_mch_but_not_in_mcs

What to Do with the Comparison Results

  1. Identify the items that are in MCS, but not Smart Suite and MCH. Delete these items from MCS.

  2. Identify the items that are in MCS and MCH, but not Smart Suite. These items should either be deleted from MCS and MCH or added to Smart Suite (if missing from Smart Suite by mistake).

  3. Identify the items that are in Smart Suite and MCH, but not in MCS. Add these items into MCS by un-registering and registering the device. For detailed information on how to do this on the device, see the Spok Mobile documentation.

  4. Identify the items that are in Smart Suite, but not in MCS and MCH. These items should either be added to MCS and MCH (un-register and register) if they are valid, or removed from the Smart Suite pager table if they are no longer valid/active.

  5. Identify the items that have inconsistent LocalDeviceIDs in the different locations:
    • Identify the LocalDeviceId that is correct.
    • If incorrect LocalDeviceId values are identified, update the incorrect LocalDeviceId values.