[TargetIT]

So, I've been playing around with these policies, searching for resources and I figured I'd compile what I've extracted from other sources as well as what I've figured out on my own.

Disclaimer: I'm in no way an expert on the subject, so please take this information with a grain of salt.

First, my specs just so there's no confusion over how I managed to get something to work - a lot of this was done by trial and error. I'm currently running BES for Exchange 4.1.5 using SQL2005 Express, and my test phone is an 8310 with 4.5.0.55. To ensure you have the possibility of this working, check to see if you have a SyncLBS table in your database.

Next, you have to set up an IT Policy with the tracking on. You can find this under Location Based Services Policy Group. There's 4 items:

Disable Blackberry Maps
Enable Enterprise Location Tracking
Enterprise Location User Tracking Prompt Message
Enterprise Location Tracking Interval

Here are my settings:

Disable Blackberry Maps:
Enable Enterprise Location Tracking: True
Enterprise Location User Tracking Prompt Message: "Location is being tracked"
Enterprise Location Tracking Interval: 15

And the results are: mixed. Like I said, I played around with this for some time. The message itself did not send to the blackberry, although the user is prompted with a canned message and the choice of whether or not to turn it on as soon as the policy is sent and processed. I also don't know if you need the quotes - neither message showed up either way so it's moot.

The first time I sent the policy, I got one update and never again. However, I did do this at work where I don't get a GPS signal indoors. At home, it's not a problem and I tried it again. I sent a non-LBS policy to the phone and then resent the LBS enable policy to it. This time is seemed to work. I should mention that I also deleted out the record in the SQL database - I have NO idea whether or not it was being able access the GPS at home, or whether deleting the record and forcing it to create another one solved the problem. The phone also sends the data every 10 minutes instead of 15, and sometimes sends it 4 times in a few seconds. It will also skip periods of time and I'm not sure why. I ran an errand the other day and I know the GPS most likely couldn't see the satellites, but it didn't start reporting again until over an hour had pass (long after I came back outside).

Speaking of the database and this SyncLBS table, here is the table structure:

Id
UserConfigId
RecordType
RecordTimestamp
ServerTime
Latitude
Longitude
Altitude
DeviceStatus
Data
Lurnum

ID: Autunumber field - nuff said
UserConfigID: References the UserConfig Table
RecordType: Unknown, although every record type I've seen is 1
RecordTimeStamp: Clearly a Timestamp but I haven't deciphered it yet
ServerTime: A time stamp. This is the number of seconds that have passed since 1/1/1970 12:00:00 AM
Latitude: Divide by 100000 to get the proper Latitude
Longitude: Divide by 100000 to get the proper Longitude
Altitude: I'm assuming this is in meters, although it's horribly inaccurate and not even displayed on the phone anywhere. If someone wants to climb a mountain to check this, go right ahead! :D
DeviceStatus: 0 or 1. Mostly 1's. Still trying to figure out what this is referring to
Data: ?
Lurnum: ?

So one of the things that is clear as that it only shows the last result - there is no record of previous entries - it simply updates the one and only record per UserConfigID. So I decided to correct that with a trigger. First I made a table called SyncLBSLog with the following columns, identical to the SyncLBS table:

UserConfigId
RecordType
RecordTimestamp
ServerTime
Latitude
Longitude
Altitude
DeviceStatus

and set the Primary Key to both the UserConfigID and the RecordTimestamp

Then I added a trigger to the SyncLBS table:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE TRIGGER [CopyToLog]
   ON  [dbo].[SyncLBS]
   AFTER INSERT,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

INSERT INTO SyncLBSLog
                      (RecordType, RecordTimestamp, ServerTime, Latitude, Longitude, Altitude, DeviceStatus, UserConfigId)
SELECT     RecordType, RecordTimestamp, ServerTime, Latitude, Longitude, Altitude, DeviceStatus, UserConfigId
FROM         SyncLBS s
Where not exists (select * from dbo.SyncLBSLog d
Where d.RecordTimestamp = s.RecordTimestamp and d.UserConfigID = s.UserConfigID) and RecordTimestamp <> 0

END

This basically appends each SyncLBS record if it changes into the SyncLBSLog table. And voila, I can now trace back to where a BB was a certain time. Big brother no?

Wondering how you can actually use this information? I know access very well so I linked the tables to access and ran some queries on it:

SELECT dbo_SyncLBSLog.UserConfigId, dbo_SyncLBSLog.RecordType, dbo_SyncLBSLog.RecordTimestamp, dbo_SyncLBSLog.ServerTime, dbo_SyncLBSLog.Latitude, dbo_SyncLBSLog.Longitude, dbo_SyncLBSLog.Altitude, dbo_SyncLBSLog.DeviceStatus, [ServerTime]-(7*3600) AS CorrectedTime, [Latitude]/100000 AS CorrectedLatitude, [Longitude]/100000 AS CorrectedLongitude, DateSerial(1970,1,1+Int([CorrectedTime]/86400)) & " " & TimeSerial(Int(([Correctedtime] Mod 86400)/3600),0,[CorrectedTime] Mod 86400-Int(([CorrectedTime] Mod 86400)/3600)*3600) AS CorrectedDateStamp, DateSerial(1970,1,1+Int([Correctedtime]/86400)) AS CorrectedDate, Int(([CorrectedTime] Mod 86400)/3600) AS CorrectedHour
FROM dbo_SyncLBSLog
ORDER BY dbo_SyncLBSLog.ServerTime;

Please note the CorrectedTime - this converts it into my local time zone - correct as needed.

After this has run for a few days, the massive amount of data is pretty enormous, so I ran another query to grab only one piece of data per hour per person (the last record per hour):

SELECT GPSLogCorrected.UserConfigId, GPSLogCorrected.CorrectedDate, GPSLogCorrected.CorrectedHour, Max(GPSLogCorrected.CorrectedTime) AS MaxOfCorrectedTime
FROM GPSLogCorrected
GROUP BY GPSLogCorrected.UserConfigId, GPSLogCorrected.CorrectedDate, GPSLogCorrected.CorrectedHour;

And then used this to create KML tags:

SELECT "<Placemark><name>" & [DisplayName] & " (" & [CorrectedDateStamp] & ")</name><Point><coordinates>" & [Longitude]/100000 & "," & [Latitude]/100000 & "</coordinates></Point></Placemark>" AS XML
FROM (dbo_UserConfig INNER JOIN LastHour ON dbo_UserConfig.Id = LastHour.UserConfigId) INNER JOIN GPSLogCorrected ON (LastHour.MaxOfCorrectedTime = GPSLogCorrected.CorrectedTime) AND (LastHour.UserConfigId = GPSLogCorrected.UserConfigId)
ORDER BY dbo_UserConfig.Id, LastHour.MaxOfCorrectedTime;

Sandwich the output of that between this:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http:/ /earth.google.com/kml/2.2">
<Folder>

(Remove the space between the two /'s)

and

</Folder>
</kml>

and you have a KML file suitable for Google Earth.

1984 wat?

[bertiebassett]

Fantastic First Post!! Welcome to the forums..

Can you write this up into BlackberryFAQ.com as it would be really useful to have as a 'sticky topic' there

[TargetIT]

Thanks, but it's still a work in progress right now. Still working on nailing down what the parameters are for making it populate consistently. For example, if I go into the BB and turn off tracking, it stops working, which is normal. If I turn it back on from the BB, nada. If I send a policy to turn it off, it comes up with a message saying that it has been turned off - and the option disappears. I resend the policy and it doesn't start to work. I delete the entry in the SyncLBS table and still nada. Resend the policy to turn it off, then another one to turn it on and it starts to work again.

In other words, it's buggy. Seems like you can't have a current record in the SyncLBS table when you send the initial policy, and the user can't touch the setting in their BB, otherwise it doesn't update. The interval setting doesn't work, and neither does the message to warn the user.

However, unless the data format changes, the "modifications" I posted will/do work

[qc_metal]

This is nice! I hope this will be enhanced in the next version.

Would be great to have this available via BerryStats ;)

[TargetIT]

Yeah, I've played around with it a bunch and once it starts working, it's completely fine but it can be a real bear to get it to start working in the first place. The real problem with how they've set it up as is, is simply that as soon as you go where the GPS doesn't work, it still updates the table but with 0's. So if someone for example loses their phone at a trade show, phones IT and says, "Where did the phone last report its whereabouts?", IT would have no idea since the table has most likely been populated with 0's. For it to work properly, they really need to only update the table if the data is valid.

[sddroog]

Great info, works excellent. However even with the latest devices (Bold and Storm) with latest OS software the logging to the BES database is still very unreliable. Hopefully RIM will improve that in the near future.