Proper device table entries for OpenGTS integtration
Integration with OpenGTS only supports numbers, not characters. The unique id should be a number and you need to put it into the imeiNumber column in the OpenGTS database.
Ok, so you are saying that whatever number I put in the client app under "Device Identifier" should be put into the db as the imeiNumber field, correct? I've done that and I'm still getting "Unknown Device". Here is the output from the server log:
2018-11-04 15:18:17 INFO: HikariPool-1 - Starting...
2018-11-04 15:18:18 INFO: HikariPool-1 - Start completed.
2018-11-04 15:18:18 INFO: Query not provided: database.selectUsers
2018-11-04 15:18:18 INFO: Query not provided: database.selectUserUsers
2018-11-04 15:18:18 INFO: Query not provided: database.selectGroups
2018-11-04 15:18:18 INFO: Query not provided: database.selectDevices
2018-11-04 15:18:18 INFO: Query not provided: database.selectLatestPositions
2018-11-04 15:18:18 INFO: Query not provided: database.selectServers
2018-11-04 15:18:18 INFO: Query not provided: database.selectGroups
2018-11-04 15:18:18 INFO: Query not provided: database.selectDevices
2018-11-04 15:18:18 INFO: Query not provided: database.selectUserGroups
2018-11-04 15:18:18 INFO: Query not provided: database.selectUserDevices
2018-11-04 15:18:18 INFO: Query not provided: database.selectGroups
2018-11-04 15:18:18 INFO: Query not provided: database.selectAttributes
2018-11-04 15:18:18 INFO: Query not provided: database.selectUserAttributes
2018-11-04 15:18:18 INFO: Query not provided: database.selectGroupAttributes
2018-11-04 15:18:18 INFO: Query not provided: database.selectDeviceAttributes
2018-11-04 15:18:18 INFO: Query not provided: database.selectDevices
2018-11-04 15:18:18 INFO: Query not provided: database.selectDrivers
2018-11-04 15:18:18 INFO: Query not provided: database.selectUserDrivers
2018-11-04 15:18:18 INFO: Query not provided: database.selectGroupDrivers
2018-11-04 15:18:18 INFO: Query not provided: database.selectDeviceDrivers
2018-11-04 15:18:18 INFO: Query not provided: database.selectDevices
2018-11-04 15:18:18 INFO: Query not provided: database.selectCommands
2018-11-04 15:18:18 INFO: Query not provided: database.selectUserCommands
2018-11-04 15:18:18 INFO: Query not provided: database.selectGroupCommands
2018-11-04 15:18:18 INFO: Query not provided: database.selectDeviceCommands
2018-11-04 15:18:18 INFO: Query not provided: database.selectDevices
2018-11-04 15:18:18 INFO: Operating system name: Linux version: 4.4.0-119-generic architecture: amd64
2018-11-04 15:18:18 INFO: Java runtime name: Java HotSpot(TM) 64-Bit Server VM vendor: Oracle Corporation version: 25.171-b11
2018-11-04 15:18:18 INFO: Memory limit heap: 1758mb non-heap: 0mb
2018-11-04 15:18:18 INFO: Character encoding: UTF-8 charset: UTF-8
2018-11-04 15:18:18 INFO: Version: 4.1-SNAPSHOT
2018-11-04 15:18:18 INFO: Starting server...
2018-11-04 15:20:10 INFO: [2a7b1f0f] connected
2018-11-04 15:20:10 INFO: [2a7b1f0f: 5055 < 192.168.123.1] HEX: 504f5354202f3f69643d3938373132332674696d657374616d703d31353431333733363235266c61743d33342e3139373431353538266c6f6e3d2d3131382e343832323032392673706565643d302e302662656172696e673d302e3026616c7469747564653d3139352e302661636375726163793d302e3026626174743d3130302e3020485454502f312e310d0a436f6e74656e742d547970653a206170706c69636174696f6e2f782d7777772d666f726d2d75726c656e636f6465640d0a557365722d4167656e743a2044616c76696b2f322e312e3020284c696e75783b20553b20416e64726f696420372e312e313b204536353533204275696c642f33322e342e412e312e3534290d0a486f73743a207777772e627275636568626173652e6f72673a32353035350d0a436f6e6e656374696f6e3a204b6565702d416c6976650d0a4163636570742d456e636f64696e673a20677a69700d0a436f6e74656e742d4c656e6774683a20300d0a0d0a
2018-11-04 15:20:10 WARN: Unknown device - 987123 (192.168.123.1)
2018-11-04 15:20:10 INFO: [2a7b1f0f: 5055 > 192.168.123.1] HEX: 485454502f312e31203430302042616420526571756573740d0a636f6e74656e742d6c656e6774683a20300d0a0d0a```
Decoded POST:POST /?id=987123×tamp=1541373625&lat=34.19741558&lon=-118.4822029&speed=0.0&bearing=0.0&altitude=195.0&accuracy=0.0&batt=100.0 HTTP/1.1??Content-Type: application/x-www-form-urlencoded??User-Agent: Dalvik/2.1.0 (Linux; U; Android 7.1.1; E6553 Build/32.4.A.1.54)??Host: www.xxxxxxxxx.xxx:25055??Connection: Keep-Alive??Accept-Encoding: gzip??Content-Length: 0????```
An export of my device record in the DB (uniqueID and IP Address fields have been masked):
{
"accountID":"phones",
"deviceID":"brucesony",
"groupID":"",
"equipmentType":"",
"equipmentStatus":"",
"vehicleMake":"Sony",
"vehicleModel":"Xperia Z3+",
"vehicleID":"",
"licensePlate":"",
"licenseExpire":0,
"insuranceExpire":0,
"driverID":"",
"driverStatus":0,
"fuelCapacity":0,
"fuelEconomy":0,
"fuelRatePerHour":0,
"fuelCostPerLiter":0,
"fuelTankProfile":"LINEAR",
"speedLimitKPH":0,
"planDistanceKM":0,
"installTime":0,
"resetTime":0,
"expirationTime":0,
"uniqueID":"XXXXXXXXXXXXXXX",
"deviceCode":"gprmc",
"deviceType":"",
"pushpinID":"",
"displayColor":"#008f00",
"serialNumber":"",
"simPhoneNumber":"",
"simID":"",
"smsEmail":"",
"imeiNumber":"987123",
"dataKey":"",
"ignitionIndex":0,
"codeVersion":"backit-5.3",
"featureSet":"",
"ipAddressValid":"",
"lastTotalConnectTime":1539516740,
"lastDuplexConnectTime":0,
"pendingPingCommand":"",
"lastPingTime":0,
"totalPingCount":0,
"maxPingCount":0,
"commandStateMask":0,
"expectAck":0,
"expectAckCode":0,
"lastAckCommand":"",
"lastAckTime":0,
"dcsPropertiesID":"",
"dcsConfigMask":0,
"dcsConfigString":"",
"dcsCommandHost":"",
"supportsDMTP":0,
"supportedEncodings":7,
"unitLimitInterval":0,
"maxAllowedEvents":0,
"totalProfileMask":"",
"totalMaxConn":0,
"totalMaxConnPerMin":0,
"duplexProfileMask":"",
"duplexMaxConn":0,
"duplexMaxConnPerMin":0,
"lastTcpSessionID":"",
"ipAddressCurrent":"172.XX.XX.XXX",
"remotePortCurrent":0,
"listenPortCurrent":0,
"lastInputState":0,
"lastOutputState":0,
"statusCodeState":0,
"lastBatteryLevel":0,
"lastFuelLevel":0,
"lastFuelTotal":0,
"lastOilLevel":0,
"lastValidLatitude":34.19736266666666,
"lastValidLongitude":-118.4822565,
"lastValidHeading":0,
"lastValidSpeedKPH":0,
"lastGPSTimestamp":1539516746,
"lastEventTimestamp":1539516746,
"lastCellServingInfo":"",
"lastDistanceKM":0,
"lastOdometerKM":19.06,
"odometerOffsetKM":0,
"lastEngineOnHours":0,
"lastEngineOnTime":0,
"lastEngineOffTime":0,
"lastEngineHours":0,
"engineHoursOffset":0,
"lastIgnitionOnHours":0,
"lastIgnitionOnTime":0,
"lastIgnitionOffTime":0,
"lastIgnitionHours":0,
"lastStopTime":1438230867,
"lastStartTime":1438230785,
"lastMalfunctionLamp":0,
"lastFaultCode":"",
"isActive":1,
"displayName":"BruceZ1s",
"description":"Bruce\'s Phone (Sony Z3+)",
"notes":"",
"lastUpdateTime":1539516740,
"creationTime":1421990735,
"vehicleYear":null,
"fuelTankProfile2":null,
"lastFuelLevel2":0
}
]traccar.xml db queries:
<entry key='database.selectAllDevices'>
SELECT CONCAT('1', imeiNumber) AS id, imeiNumber AS uniqueId FROM Device WHERE imeiNumber REGEXP '^[0-9]+$';
</entry>
<entry key='database.insertPosition'>
START TRANSACTION;
UPDATE Device SET lastValidLatitude = :latitude, lastValidLongitude = :longitude, lastGPSTimestamp = UNIX_TIMESTAMP(:fixTime), lastUpdateTime = UNIX_TIMESTAMP(NOW()) WHERE imeiNumber = SUBSTRING(CAST(:deviceId AS CHAR(32)), 2);
SELECT @accountID := accountID, @deviceID := deviceID FROM Device WHERE imeiNumber = SUBSTRING(CAST(:deviceId AS CHAR(32)), 2);
INSERT INTO EventData (accountID, deviceID, timestamp, statusCode, latitude, longitude, speedKPH, heading, altitude, rawData, creationTime, address)
VALUES (@accountID, @deviceID, UNIX_TIMESTAMP(:fixTime), 0, :latitude, :longitude, :speed * 1.852, :course, :altitude, '', UNIX_TIMESTAMP(NOW()), :address);
COMMIT;
</entry>I'm sure it's something simple. Just a misconfigured field or something I've left out. Based on the db query to insert the record, I'm assuming what comes in the post as id gets translated into the :deviceId referenced in the query.
OpenGTS configuration file is probably outdated. I suspect it's missing some queries. Maybe you need to add database.selectDevices.
What would that query look like? I don't see a database.selectDevices in the default.xml that came with the traccar server. I'm running OpenGTS 2.6.1. I can upgrade to the latest 2.6.5 if you think that might be helpful.
It should look exactly like database.selectAllDevices query.
Ok, that did it. Adding that query, it's now updating the db properly. Thanks!
I'm running an OpenGTS server. I installed the Traccar Server (on Linux, using the manual method) and configured it to talk to the OpenGTS database as per the directions. I'm trying now to modify my existing device entries in the db to conform so that the Traccar client can properly update the table.
Previously, for my phone, the deviceID field was "brucesony". The uniqueID field was the phone's IMEI number and the imeiNumber field was blank.
Looking at the queries that the documentation had me add for the integration, I'm trying to figure out how it's supposed to work.
For the update SQL, there is this line:
UPDATE Device SET lastValidLatitude = :latitude, lastValidLongitude = :longitude, lastGPSTimestamp = UNIX_TIMESTAMP(:fixTime), lastUpdateTime = UNIX_TIMESTAMP(NOW()) WHERE imeiNumber = SUBSTRING(CAST(:deviceId AS CHAR(32)), 2);I guess this part is confusing:
SUBSTRING(CAST(:deviceId AS CHAR(32)),2)I take it "deviceId" is the value we have configured in the client for the "Device Identifier" field? Does this mean imeiNumber should be a substring of deviceId starting with the 2nd character?
Maybe it's easier to start from the other direction. Let's say in the app, I've configured the "Device Identifier" field with 123456. What should I see in the device table record for the 'deviceId', and 'imeiNumber' fields so that updates can happen properly?