GeoEvent: Monitoring Asset’s Lively/Inactive Standing

Complicated Monitor Hole Necessities

I used to be just lately requested to supply an answer that may establish the standing of a set of gadgets.  Every machine could possibly be ‘Lively’ or ‘Inactive’ primarily based on the period of time that had elapsed for the reason that final message acquired.  On first look this appeared like resolution for the Track Gap Detector, nonetheless there was a further element within the necessities that threw that resolution out the window: Every class of machine had a unique time window for energetic/inactive standing (as proven beneath).

So for “Pink” gadgets, they had been sending occasions each 15 seconds and wanted to be marked as inactive if greater than 8 occasions (or 120 seconds) had been missed. However for “Pink” gadgets, they had been sending occasions each 10 minutes and wanted to be marked as inactive if greater than 2 occasions (or 20 minutes) had been missed.

Complicated Reporting Necessities

To make issues even more durable, a report was wanted that may point out the variety of every machine that was energetic/inactive. This report wanted to be correct and up to date in real-time.  GeoEvent can deal with the real-time updates of singular occasions, however aggregation and caching of state is NOT one thing GeoEvent does (excluding just a few operations like enter/exit).  

‘Preserve It Easy’ Answer

I thought-about having a unique Monitor Hole Detector for every group, however that wasn’t sustainable if the listing of gadgets was to develop.  I additionally thought-about writing some kind of customized processor to cache the state of every TRACK_ID and supply some kind of mixture depend of group standing.  However then I got here to my senses and realized there’s one instrument in our GIS arsenal that’s skilled at on the fly standing calculations and aggregations: Database Views (or Question Layers if you happen to desire). 

A database view is created in a relational database (SQL Server Specific on this occasion) by ArcCatalog. You utilize SQL to outline the view. Every view is predicated on an underlying database desk (characteristic class) and is refreshed for every request. So you should use database views to do all kinds of fantastic issues to your information which you could’t do elsewhere.

Actual-time Machine Updates

To begin, I used GeoEvent to jot down the most recent updates from every of the gadgets to a characteristic class within the database. These data contained a whole lot of data from the machine, however an important fields for this weblog are Identify (TRACK_ID), Sort (Group), and MsgDatetime (final time the machine reported in). This characteristic class was saved in the identical relational database because the configuration desk above (I overlooked the brink in seconds as a result of it may be calculated).

   

This configuration desk can simply be maintained and even expanded as extra gadgets and/or new varieties of gadgets are added to the system. 

Actual-time Machine Standing

Now for our first view.  To find out the standing of any machine, we have to examine the age of the final occasion file to the inactive threshold. In SQL Server, we will use the DATEDIFF operate to subtract the message time from the present time. The consequence (in seconds) tells us how previous every file is.

Age of Final Message = DATEDIFF( seconds, machine.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 standing, we’d like an CASE WHEN assertion to see if the age (in seconds) is larger than or lower than the inactive threshold (in seconds):

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

With a purpose to be part of the 2 tables collectively, we use a LEFT OUTER JOIN. One of these be part of selects all of the data of the primary desk, and joins an identical file from the second desk.  We carry out this be part of on the machine’s kind.

FROM dbo.DEVICECURRENT AS machine LEFT OUTER JOIN
 dbo.DEVICECONFIG AS config ON machine.Sort = config.DeviceType

The ultimate view SQL is as follows and the results of the view is beneath that:

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

Group Standing

Now we have to mixture these particular person standing data into a gaggle standing.  To try this, we need to group above standing view by the DeviceType and depend what number of data are in every kind. 

GROUP BY DeviceType

However we do not simply need a depend, we have to know what number of are energetic and what number of are inactive.  With a purpose to conditionally depend data we will use the COALESCE operate in SQL Server:

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

With a purpose to mixture the geometry of the group, I’ve chosen the convex hull operation offered by SQL Server:

geometry::ConvexHullAggregate(SHAPE) AS form

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

SELECT
  MIN(OBJECTID) AS OBJECTID,
  DeviceType,
  COALESCE (SUM(CASE WHEN DeviceStatus=”Lively” THEN 1 ELSE 0 END), ‘n/a’) AS Lively,
  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 leads to the next view desk:

Publish the Views

As soon as I had the views working, I added them to a map service, created an online map and a dashboard to show every machine’s present location and standing in addition to the group standing. All of that data updates in real-time primarily based on every machine’s final occasion age.