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 and describes SQL scripts which can help identify problems with Spok Mobile registrations.

After Spok Mobile has been installed, several SQL scripts are available which can perform different functions within the system.

Scripts which Troubleshoot Corrupted Registrations

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

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

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

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 on which the Oracle script was executed.

Data in each file must be formatted the same way and sorted by telephone number. The format of the data should be 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, LocalDeviceID 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 while an outline of the process is included below, 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 the Spok Enterprise 4.7 Administrator's Guide.

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

  10. Capture the output in 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 to 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 to 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 LocalDeviceID values in the different locations:
    • Identify the LocalDeviceId that is correct.
    • If incorrect LocalDeviceId values are identified, update the incorrect LocalDeviceId values.