GeoEvent: Tracking Asset's Active/Inactive Status

[EricIronside]

• 
   target="_self">Complex Track Gap Requirements
• Complex Reporting Requirements
• 'Keep It Simple' Solution
• Real-time Device Updates
• Real-time Device Status
• Group Status
• Publish the Views

Complex Track Gap Requirements

I was recently asked to provide a solution that would identify the status of a set of devices.  Each device could be 'Active' or 'Inactive' based on the amount of time that had elapsed since the last message received.  On first glance this seemed like a good solution for the Track Gap Detector, however there was an additional detail in the requirements that threw that solution out the window: Each class of device had a different time window for active/inactive status (as shown below).

So for "Red" devices, they were sending events every 15 seconds and needed to be marked as inactive if more than 8 events (or 120 seconds) had been missed. But for "Pink" devices, they were sending events every 10 minutes and needed to be marked as inactive if more than 2 events (or 20 minutes) had been missed.

Complex Reporting Requirements

To make matters even harder, a report was needed that would indicate the number of each device that was active/inactive. This report needed to be accurate and updated in real-time.  GeoEvent can handle the real-time updates of singular events, but aggregation and caching of state is NOT something GeoEvent does (with the exception of a few operations like enter/exit).  

'Keep It Simple' Solution

I considered having a different Track Gap Detector for each group, but that wasn't sustainable if the list of devices was to grow.  I also considered writing some sort of custom processor to cache the state of each TRACK_ID and provide some sort of aggregate count of group status.  But then I came to my senses and realized there is one tool in our GIS arsenal that is expert at on the fly status calculations and aggregations: Database Views (or Query Layers if you prefer). 

A database view is created in a relational database (SQL Server Express in this instance) by ArcCatalog. You use SQL to define the view. Each view is based on an underlying database table (feature class) and is refreshed for each request. So you can use database views to do all sorts of wonderful things to your data that you can't do elsewhere.

Real-time Device Updates

To start, I used GeoEvent to write the latest updates from each of the devices to a feature class in the database. These records contained a lot of information from the device, but the most important fields for this blog are Name (TRACK_ID), Type (Group), and MsgDatetime (last time the device reported in). This feature class was stored in the same relational database as the configuration table above (I left out the threshold in seconds because it can be calculated).

   

This configuration table can easily be maintained or even expanded as more devices and/or new types of devices are added to the system. 

Real-time Device Status

Now for our first view.  To determine the status of any device, we need to compare the age of the last event record to the inactive threshold. In SQL Server, we can use the DATEDIFF function to subtract the message time from the current time. The result (in seconds) tells us how old each record is.

Age of Last Message = DATEDIFF( seconds, device.MsgDatetime, SYSUTCDATETIME() )

To calculate the inactive threshold in seconds, we multiply the report interval by the inactive threshold:

Inactive Threshold (seconds) = config.ReportInterval * config.Threshold

For the status, we need an CASE WHEN statement to see if the age (in seconds) is greater than or less than the inactive threshold (in seconds):

CASE
    WHEN (DATEDIFF(second, asset.MsgDatetime, SYSUTCDATETIME()) > (config.ReportInterval * config.Threshold)) 
  THEN 'Inactive'
  ELSE 'Active'
  END
AS AssetStatus

In order to join the two tables together, we use a LEFT OUTER JOIN. This type of join selects all the records of the first table, and joins a matching record from the second table.  We perform this join on the device's type.

FROM dbo.DEVICECURRENT AS device LEFT OUTER JOIN
 dbo.DEVICECONFIG AS config ON device.Type = config.DeviceType

The final view SQL is as follows and the result of the view is below that:

SELECT 
  device.OBJECTID, 
   device.Name, 
   device.DeviceType, 
   device.MsgDatetime, 
   config.ReportInterval, 
   config.Threshold,
   config.ReportInterval * config.Threshold AS MaxAgeSeconds, 
   DATEDIFF(second, device.MsgDatetime, SYSUTCDATETIME()) AS CurrentAge, 
   CASE
     WHEN (DATEDIFF(second, device.MsgDatetime, SYSUTCDATETIME()) > (config.ReportInterval * config.Threshold)) 
    THEN 'Inactive' 
    ELSE 'Active' 
    END 
  AS DeviceStatus, 
  device.SHAPE
FROM dbo.DEVICECURRENT AS device 
LEFT OUTER JOIN dbo.DEVICECONFIG AS config ON device.DeviceType = config.DeviceType

Group Status

Now we need to aggregate these individual status records into a group status.  To do that, we want to group above status view by the DeviceType and count how many records are in each type. 

GROUP BY DeviceType

But we don't just want a count, we need to know how many are active and how many are inactive.  In order to conditionally count records we can use the COALESCE function in SQL Server:

COALESCE (SUM(CASE WHEN AssetStatus = 'Active' THEN 1 ELSE 0 END), 'n/a') AS Active
COALESCE (SUM(CASE WHEN AssetStatus = 'Inactive' THEN 1 ELSE 0 END), 'n/a') AS Inactive

In order to aggregate the geometry of the group, I've chosen the convex hull operation provided by SQL Server:

geometry::ConvexHullAggregate(SHAPE) AS shape

So the final SQL that defines this view is as follows:

SELECT
  MIN(OBJECTID) AS OBJECTID,
  DeviceType,
  COALESCE (SUM(CASE WHEN DeviceStatus = 'Active' THEN 1 ELSE 0 END), 'n/a') AS Active,
  COALESCE (SUM(CASE WHEN DeviceStatus = 'Inactive' THEN 1 ELSE 0 END), 'n/a') AS Inactive,
  geometry::ConvexHullAggregate(SHAPE) AS SHAPE
FROM dbo.DeviceStatus
GROUP BY DeviceType

Which results in the following view table:

Publish the Views

Once I had the views working, I added them to a map service, created a web map and a dashboard to display each device's current location and status as well as the group status. All of that information updates in real-time based on each device's last event age.