Get Latest StatusData for one Asset (e.g. Odometer, Engine Hours, Fuel usage)

HomeCommunitySDK/APIOther/GeneralGet Latest StatusData for one Asset (e.g. Odometer, Engine Hours, Fuel usage)

Get Latest StatusData for one Asset (e.g. Odometer, Engine Hours, Fuel usage)

Original Poster

Hi All,

It might sound trivial (and I have been looking at other samples on the community group), but it seems to be a challenge to get a single LATEST (no date filtering) value for the StatusData elements. I have tried to use the resultsLimit and sort options, but I get the error:

"Failed:Only sorting by date in ascending order is supported when interpolating."

Has anyone been successful in getting the latest / last value for a measurement metric (using the StatusData) on demand via the sdk?

My failed attempt:

var deviceId = 'b1'
 
api.call("Get", {
    "typeName": "StatusData",
    "resultsLimit": 1,
    "search":  {
        "deviceSearch": { id: deviceId },
        "diagnosticSearch": {
            "id": "DiagnosticEngineOnTimeId"
        }
    },
    "sort": {
        "sortBy": "date",
        "sortDirection": "desc",
        "offset": null,
        "lastId": null
    },
}, function(result) {
    console.log("Done: ", result);
}, function(e) {
    console.error("Failed:", e);
});

Join the conversation

You need to be logged in to reply to this post and participate in the Geotab Community discussions.

10 Replies

DarenPorter-1148

Edited by DarenPorter-1148

You should be able to get an interpolated value by setting the search toDate and fromDate to the current time:

api.call("Get", {
    "typeName": "StatusData",
    "resultsLimit": 1,
    "search":  {
        "deviceSearch": { id: deviceId },
        "diagnosticSearch": {
            "id": "DiagnosticEngineOnTimeId"
        }
        "toDate": new Date().toISOString(),
        "fromDate": new Date().toISOString()
    }
}, function(result) {
    console.log("Done: ", result);
}, function(e) {
    console.error("Failed:", e);
});

I wasn't able to find any DiagnosticEngineOnTimeIds in the test databases I checked for this, but I know it works with other interpolated diagnostics.

pcoetzerhanab-2133

Original Poster

Hi @DarenPorter-1148 ,

I tried this as well, but because the search date is then limited to a defined time, I get no results.

The aim is to get the latest or last recorded value. With some vehicles (machines that only run certain days), I do not know when they last ran, and it would be a process to have to loop back in time until they last reported.

The reason why I have the Sort option in, is that the data is returned chronologically in the array (old to new), and I want the latest last value, thus sorting descending.

In the end, I want to build a report that shows (I'm pasting in the diagnostic codes as in the Geotab definition list):

Timestamp
Device Serial / ID
"description": "Odometer", "code": "5", "diagnosticCodeId": "DiagnosticOdometerId", "conversion": 100
"description": "Engine operational time", "code": "9", "diagnosticCodeId": "DiagnosticEngineHoursId", "conversion": 360
"description": "Total fuel used (since telematics device install)", "code": "2187", "diagnosticCodeId": "DiagnosticDeviceTotalFuelId", "conversion": 0.01

Typically it would be the last recorded values you see on the measurements screen:

Measurements

DarenPorter-1148

Edited by DarenPorter-1148

Try running the Get call without the dates, an unrestricted Get for that Diagnostic (DiagnosticEngineOnTimeId) and DeviceID. Do you get any results? I know this toDate/fromDate method works for other interpolated diagnostic values, so I'm wondering if you're in a situation like I was in where my test databases don't seem to have this diagnostic in it at all.

pcoetzerhanab-2133

Original Poster

If I run the request without any limits/filters, I do get results, but typically ALL the data, and that is quite an expensive query if I need to do this per vehicle, per metric only to get the last result of the list....

var deviceId = 'b1EC'
 
api.call("Get", {
    "typeName": "StatusData",
    //"resultsLimit": 1,
    "search":  {
        "deviceSearch": { id: deviceId },
        "diagnosticSearch": {
            //"id": "DiagnosticEngineHoursAdjustmentId"
            //"id": "DiagnosticOdometerId"
            "id": "DiagnosticDeviceTotalFuelId"
        },
        //"toDate": new Date().toISOString(),
        //"fromDate": new Date().toISOString()
    },
    // "sort": {
    //     "sortBy": "date",
    //     "sortDirection": "desc",
    // },
}, function(result) {
    console.log("Done: ", result);
}, function(e) {
    console.error("Failed:", e);
});

Output:

request

DarenPorter-1148

Edited by DarenPorter-1148

You would still want a 'resultsLimit' of 1, but that said, it's entirely possible some of these Diagnostic values don't interpolate in the same way others do.

To give you an example, I know that DiagnosticEngineHoursAdjustmentId gets interpolated. If I Get 1 result for a device that I know is in active use, without any date filtering, I get a default timed result (probably a testing or data initialization result):

If I make that same call but include the toDate and fromDate, I get the vehicle's most recent engine hours adjustment value:

The `dateTime` on the result for this vehicle is a few minutes prior to the actual time I'm making the call.

For what it's worth, I'm unable to find any instances of a few of the Diagnostics you're looking for in several databases I've checked, even a production database with many years worth of real world data. DiagnosticEngineOnTimeId and DiagnosticDeviceTotalFuelId don't return any results.

EDIT: Edited to include data for an actual production-use vehicle.

pcoetzerhanab-2133

Original Poster

Edited by pcoetzerhanab-2133

Hey @DarenPorter-1148 ,

Thanks for spending the time to debug this with me.

So like I showed earlier, there is definitely data dor the metrics. Running the query without the start/end date does return results, the problem is that the results are in chronological order (oldest record first). I want the newest or last recorded record. In your sample, the record is dated 0001-01-01; I'm sure if you remove the resultLimit, it would return a bunch of records, with the dates following up on each other. The other problem is if I add the date, it queries for only today, so I'm not able to get the data if the vehicle was driving sa a week ago.

As soon as I remove the dates and add in the sorting, then the error pops up: "Failed: Only sorting by date in ascending order is supported when interpolating."

When I remove the resultLimit, all the data is returned (well up to 50 000 records), Technically I can then just get the last record of the array (if there is hopefully not more than the API limit), but that is a huge query. To be hones, I don't really understand why interpolation is playing a role on the values, as these are timestamped values collected directly from the CANBus (or I might be missing something).

That said, I do not understand why Geotab does not have a "Get Last" type of call or allow descending sorting to give you the last recorded value of a metric (sounds so simple).

Cheers

DarenPorter-1148

The record returning 0001-01-01 is without toDate/fromDate in the search. I included it to highlight the difference - when I include those dates in the search, I get back a result from just a few minutes before I made the actual call, the "most recent" value I'd be looking for.

I'm not sure why it wouldn't work the same way for you. We may need someone else from Geotab to step in and help explain if and how these diagnostics work differently from others.

pcoetzerhanab-2133

Original Poster

Thanks for the help...

Yeah using the date (from / to startdate) to ge the most recent, is good if the vehicle has recorded the data today, but it does not show up for vehicles that were active last week.

As I'm not ale to use the sort, I can use the array sort mechanism, but not ideal to have 6000+ records returned.

I'm going to try and log a service request for this and see what the engineers come up with.

var deviceId = 'b1EC'
 
api.call("Get", {
    "typeName": "StatusData",
    //"resultsLimit": 1,
    "search":  {
        "deviceSearch": { id: deviceId },
        "diagnosticSearch": {
            "id": "DiagnosticOdometerId"
            //"id": "DiagnosticEngineHoursAdjustmentId"
            //"id": "DiagnosticDeviceTotalFuelId"
        },
        //"toDate": new Date().toISOString(),
        //"fromDate": new Date().toISOString()
    },
    //  "sort": {
    //      "sortBy": "date",
    //      "sortDirection": "desc",
    //  },
}, function(results) {
    const descendingResults = results.sort((a, b) => 
        new Date(b.dateTime) - new Date(a.dateTime)
    );
    console.log("Done: ", descendingResults);
}, function(e) {
    console.error("Failed:", e);
});

pcoetzerhanab-2133

Original Poster

Hi @DarenPorter-1148 ,

I figured it out. It is actually so trivial - I only had to remove the toDate filter.

I'm not sure why it was so finnicky but I'm now able to get the last reported value.

var deviceId = 'b2'
 
api.call("Get", {
    "typeName": "StatusData",
    "search":  {
        "deviceSearch": { id: deviceId },
        "diagnosticSearch": {
            //"id": "DiagnosticOdometerId" 
            //"id": "DiagnosticEngineHoursId" 
            "id": "DiagnosticDeviceTotalFuelId" 
        },
        "fromDate": new Date()
    },
}, function(results) {
    console.log("Done: ", results);
}, function(e) {
    console.error("Failed:", e);
});

DarenPorter-1148

Glad you got it sorted!

Still have questions?

Learn with Academy Browse documents