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.
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).
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.
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.
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):
WHEN (DATEDIFF(second, asset.MsgDatetime, SYSUTCDATETIME()) > (config.ReportInterval * config.Threshold))
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:
config.ReportInterval * config.Threshold AS MaxAgeSeconds,
DATEDIFF(second, device.MsgDatetime, SYSUTCDATETIME()) AS CurrentAge,
WHEN (DATEDIFF(second, device.MsgDatetime, SYSUTCDATETIME()) > (config.ReportInterval * config.Threshold))
FROM dbo.DEVICECURRENT AS device
LEFT OUTER JOIN dbo.DEVICECONFIG AS config ON device.DeviceType = config.DeviceType
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:
MIN(OBJECTID) AS OBJECTID,
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
GROUP BY DeviceType
Which results in the following view table:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.