Troubleshooting Corrupted Registrations

Spok Mobile 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.

Spok Mobile 4.0+

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

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.6 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