Overview
Managed app configuration can be used with Eleos. If a customer uses mobile device management (MDM), the MDM provider (or client if they are their own) can pass Managed Device ID (or “managedDeviceId”) and this ID can be received by Eleos. For more information see Eleos’ documentation: https://dev.eleostech.com/platform/#operation/login. The MDM provider needs to ensure they push this policy they create to all tablets, so it will likely need to be pushed for any new devices that are added.
Eleos.Sessions Table
In the ELEOS.Sessions SQL table in the TMS, we keep a record of that by Driver and Date. A customer can look by UserName for the latest session and see the ManagedDeviceId column on the far right.
We populate the ManagedDeviceId from the headerData we get initially from the authentication request. The header data is also what we use to populate the AppVersion column and MobilePlatform column in the ELEOS.Sessions table.
When an entry comes to the table without the ManagedDeviceId but does contain the AppVersion and the MobilePlatform ("eleos-mobile-app-version" and "eleos-mobile-app-platform"), that typically indicates that it is a driver’s personal device like a phone or a tablet that did not have the policy pushed to it, since a personal device is not controlled by the MDM.
The ELEOS.usp_ValidateSession procedure will update the App, Version, and DeviceId passed on a "Verify" or a "Login" action.
If there are raw logins for the App and Platform values that in turn are not passing those values to the sessions table, there may be a problem and opening a ticket with ASR Support may be warranted.
Several customers have built reports on this table to track which drivers last used a device, and what Truck it was in during that timeframe with Workflow and ELD. The following queries can be used by a customer to assist with creating SSRS reports.
Query To Find Current Device IMEI
Clients that utilize a Mobile Device Management (MDM) provider and have managed device ids passed to the integration will have their current device IMEI is located within the ELEOS.Sessions table. The following query can be used to find these current devices by their managed device ids within your database:
Note that changing the @ShowAllSerialNumbers bit variable in the query below from @True to @False will restrict the query to only show devices that have a ManagedDeviceId that is populated and not null:
/********************************************************************************
CONFIGURE SCRIPT DEFAULTS AND VARIABLES.
********************************************************************************/
-- Set Values for Lookup and Speed/Access.
;SET NOCOUNT ON
;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Set Variables used for Query Performance.
;DECLARE @True BIT = 1
,@False BIT = 0
/* Set Variable used for filtering Active Tablet Serial Numbers.
Set this to @False to only show Tablet Serial Numbers that are populated */
;DECLARE @ShowAllSerialNumbers BIT = @True
/********************************************************************************
BUILD TABLE OF LATEST SESSIONS FOR THE USERS.
********************************************************************************/
-- Create Temporary Table for Optimal Speed.
;CREATE TABLE #SessionTablets (
[DriverId] VARCHAR(50)
,[TabletId] VARCHAR(100)
)
-- Load Temporary Table from the Session Tables.
;WITH cteLatestSession AS
(
SELECT [DriverId] = S.[UserName]
,[TabletId] = S.[ManagedDeviceId]
,[Sequence] = ROW_NUMBER()
OVER ( PARTITION BY S.[ManagedDeviceId]
ORDER BY S.[CreatedDt] ASC )
FROM ELEOS.Sessions S
WHERE S.[Active] = @True
AND S.[ManagedDeviceId] <> ''
)
INSERT INTO #SessionTablets (
[DriverId]
,[TabletId]
)
SELECT [DriverId] = S.[DriverId]
,[TabletId] = S.[TabletId]
FROM cteLatestSession S
WHERE S.[Sequence] = 1
/********************************************************************************
BUILD TABLE OF CURRENT VEHICLES FOR THE USERS.
********************************************************************************/
-- Create Temporary Table for Optimal Speed.
;CREATE TABLE #UserVehicles (
[DriverId] VARCHAR(50)
,[TractorId] VARCHAR(50)
)
-- Load Temporary Table from the Duty Status Logs.
;WITH cteLatestActivity AS
(
SELECT [DriverId] = S.[DriverCode]
,[TractorId] = S.[DeviceName]
,[Sequence] = ROW_NUMBER()
OVER ( PARTITION BY S.[DriverCode]
ORDER BY S.[DateTime] DESC )
FROM ELEOS.CurrentDutyStatus S
)
INSERT INTO #UserVehicles (
[DriverId]
,[TractorId]
)
SELECT [DriverId] = A.[DriverId]
,[TractorId] = A.[TractorId]
FROM cteLatestActivity A
WHERE A.[Sequence] = 1
/********************************************************************************
SUMMARIZE INFORMATION ABOUT VEHICLES AND CURRENT USAGE.
********************************************************************************/
;SELECT -- TMWSuite Information.
[TractorNumber] = T.[trc_number]
,[Fleet] = T.[trc_fleet]
,[VIN Number] = T.[trc_serial]
,[Default Trailer] = T.[trc_trailer1]
-- Driver Information.
,[Default Driver Id] = T.[trc_driver]
,[Current Driver Id] = UV.[DriverId]
,[Current Driver Alt Id] = M.[mpp_otherid]
,[Current Driver Name] = COALESCE(M.[mpp_firstname] + ' ' , '')
+ COALESCE(M.[mpp_lastname] , '')
,[Current Driver Phone] = M.[mpp_currentphone]
-- Device Information.
,[Active Tablet Serial] = ST.[TabletId]
FROM -- Use Tractor Profile as Default Set.
dbo.TractorProfile T
-- Get Information about Current ELD Driver in Vehicle (1-1).
LEFT JOIN #UserVehicles UV
ON T.[trc_number] = UV.[TractorId]
-- Get Information about Current Tablet Usage for Driver (1-1).
LEFT JOIN #SessionTablets ST
ON UV.[DriverId] = ST.[DriverId]
-- Get Information about Driver Profile for the ELD Session (1-1).
LEFT JOIN dbo.ManPowerProfile M
ON UV.[DriverId] = M.[mpp_id]
WHERE -- Restrict to only Active Units and Tablets.
T.[trc_status] <> 'OUT'
AND ( CASE WHEN ( @ShowAllSerialNumbers = @False )
THEN
CASE WHEN ( ST.[TabletId] IS NOT NULL )
THEN 1
ELSE 0
END
WHEN @ShowAllSerialNumbers = @True THEN 1
END = 1 )
/********************************************************************************
CLEAN UP TABLE AND DROP TEMPORARY VALUES.
********************************************************************************/
;DROP TABLE #SessionTablets
;DROP TABLE #UserVehicles
Latest Device by Driver for TMWSuite
The following script can be used to find the latest device by Driver for TMWSuite:
;WITH cteLatestLogins as
(
SELECT [UserName] = S.[Username]
,[LastAccessDt] = S.[LastVerifyDt]
,[MobilePlatform] = S.[MobilePlatform]
,[AppVersion] = S.[AppVersion]
,[ManagedDeviceId] = S.[ManagedDeviceId]
,[SequenceDesc] = ROW_NUMBER()
OVER ( PARTITION BY S.[UserName]
ORDER BY S.[LastVerifyDt] DESC
,S.[CreatedDt] DESC )
FROM ELEOS.Sessions S WITH (NOLOCK)
WHERE S.[Active] = 1
)
SELECT [UserName] = L.[Username]
,[LastAccessDt] = L.[LastAccessDt]
,[MobilePlatform] = COALESCE(L.[MobilePlatform] , '')
,[AppVersion] = COALESCE(L.[AppVersion] , '')
,[ManagedDeviceId] = COALESCE(L.[ManagedDeviceId] , '')
FROM cteLatestLogins L
LEFT JOIN dbo.ManPowerProfile M WITH (NOLOCK)
ON L.[UserName] = M.[mpp_id]
WHERE L.[SequenceDesc] = 1
AND M.[mpp_status] <> 'OUT'
ORDER BY [UserName]