HOW TO: Device ID Case Sensitivity in AFW

Summary

These procedures address an issue where current device reports are not being displayed in AFW but when a history query is performed, the devices reports are included.  In this particular issue, the device ID includes letters, has been entered into UniTrac more than one time, and the letters in the device ID have been inconsistently capitalized between the multiple entries.

Detail

There are two methods to effect this repair.  Try Method 1 first.  If it does not resolve the issue, move on to Method 2.

Method 1: fixunits.sql

• Obtain the fixunits.sql file

• Run the fixunits.sql file on the AFW server.

• If it has positive results, edit the script and change "set @DOFIX = 0" to "set @DOFIX = 1" and run it again.

• If it does not have positive results, stop and contact the help desk

• Stop the Autofind Web and Autofind6 services. Restart the services in reverse order.

• Have all affected users log out of AFW then log back in.

• Check if the issue has been resolved by looking for AFW to receive a regular report (not a poll from AFW) from the device you have been having trouble with.

Method 2: Manual modification

Note: This process will delete the device and its history from AF6 / AFW.  Once completed, using Query-By-Date you will be able to restore the data from UniTrac to the AutoFind database.  The historical data retrievable is limited by how far back your UniTrac database is storing data.

1) Delete the affected device from Unitrac via the normal method through the user interface.

2) On your AutoFind Web server, run the following command against your AutoFind6 database.

(Note about copy and pasting - Sometimes the single quotes do not copy correctly and they need to be removed and then re-typed inside SQL Server Management Studio.)

SELECT * FROM units WHERE szUnitID = ' <AFFECTED UNIT ID HERE>’

Write down the kUnitID.  You will need it in the next step.

3) This query should return one row. If you get more than one row, please send the results to me and do not proceed with the remaining steps.

If only one row is returned, proceed with executing the next SQL statements.  These will clean up the database so that we do not leave orphaned data behind.  In each statement, "n" is the kUnitID that is returned from the previous query.

DELETE FROM Reports WHERE kUnitId=n

DELETE FROM ReportHistory WHERE kUnitId=n

DELETE FROM Messages WHERE kUnitId=n

DELETE FROM ACL WHERE kEntityID=n AND bGroup=0

The next statement deletes the device from the database.

DELETE FROM units WHERE szUnitID = ' <AFFECTED UNIT ID HERE>’

You should see a message stating that 1 row was affected.

4) Add the correct device name back into Unitrac through the user interface.

5) Check AFW for the new device name.

6) Right-click on the device in the Device window and select Query By Date

7) Enter your desired search date criteria and ensure the correct device ID is selected then click Retrieve.

8) Check if the issue has been resolved by looking for AFW to receive a regular report (not a poll from AFW) from the device you have been having trouble with.