Is there a way to ensure that the timestamp is the actual time and not the deviceโs time?
We just discovered someone manipulating their timecard by manually setting the device time and then punching in to show they were on time. Sneakyโฆ
I can go into the audit logs which timestamps the actual time of the operation to verify but this would get pretty cumbersome when trying to manage 35+ timecards.
The deviceโs belong to the employees and we install the app on their phone.
Thank you.
Sneaky sneaky. Hmmm I would suggest checking out UTCNOW() - though I think itโs not actually polling the time from some external source, instead only adding or subtracting based on your timezone.
@Phil might have a good idea what to do.
Just tried this and youโre correct. Changed my column to UTCNOW() and I was still able to manipulate the timestamp via phone settings; does not poll an external source.
UTCNOW() is the device time without the timezone adjustment. Itโs just as subject to tampering as NOW() is.
There is no way to poll an external source for time.
If you use a workflow to add a timestamp to a row, the timetamp will reflect the serverโs time rather than that of the userโs device. (Workflows run from AppSheet servers, not on the userโs device.) The downside with workflows is that they run only when the server receives the data from the device when a sync occurs, so the timestamp added by a workflow really reflects when the data was synced, not when it was modified on the device. But this might still help you discover tampering. Or you could even say the clocking-in and -out must be done by syncing.
This worked.
I created a new column [Actual Sync Time] and created the workflow to set this column with a timestamp. The [Date/Timestamp] column captures device time, [Actual Sync Time] shows when workflow was run.
Iโm curious how others have handled this. Particularly with apps that may be subject to compliance or regulatory review where accuracy and validity of something like when a record was created would be critical - apps in the medical field, osha inspections, fleet inspections, etc.
Thanks, again, Steve.
This would have been my way forward. Make sure the server time is with like 10 minutes of the user time. If so, the user time is good is valid.
Hi guys! Coming into this late (have been focused on client acquisition) but I thought Iโd give you my knowledge on how this is handled in the industry. For those who donโt know me, I have over 25 years exp in corporate software dev.
How this is handled in the Industry
This problem is ever present in any software system. Itโs just the way software works. When an app asks for date and time, it has no choice but to get it from the operating system of the device it is making the request of.
When IMPORTANT timestamps are needed, the way this is handled in the industry is exactly the way @Steve suggested, by retrieving the Date/Time from the server. It (or they) is the one constant in the system and ensures everyone is getting that info in a consistent manner. If its needed on the client WITHOUT a save, then a client to server call is made to retrieve it. As a side note: there is always potential the Date/Time could be changed on the server - either by mistake or by some unscrupulous activity. Unlikely, but possible.
Another thing corps do nowadays is lock down the desktop/laptop machines so that users cannot change the date time and it is usually synced with a server. In some circumstances, that is not feasible because QA testers MUST be able to change Date/Time to test software functions that rely on it. Obviously, this is not feasible for our purposes as AppSheet developers.
Related Questions
I do have a related question, based on this comment from @Steve:
Under โnormalโ circumstances, our edits are synced to servers right away, within seconds, of a save. As @MultiTech_Visions, points out this is perfectly acceptable for most regulating organizations. (If its not acceptable then you may be doing work that is HIGHLY scrutinized and maybe AppSheet is not the right tool for the job!)
As I am sure was implied by @Steve 's comment, where problems can occur is when connection is lost. Assuming that capturing an accurate timestamp is critical, one way to prevent the โbad dataโ due to the delay, is to NOT allow the action if there is no connection.
Soโฆ some questions:
To my knowledge, no, not directly.
No.
What could you do with an app table backed by a spreadsheet with two columns (ID & Server Sync) and one row, {1, =NOW()
}?
That would get you Googleโs server timestamp, wouldnโt it? That may be okay, if used consistently throughout the app. Also, to be general, thereโs the database side of things. I think you can do something similar using Views.
In any case, the timestamp would only be as good as the last sync. Iโve read about but never tried, forcing a sync automatically. If that is possible that would help. But then Iโm back to โwhat if connectivity is lost?โ.
Another Thought
In the end, the data source (Google sheet or database) is the system of record. And you have to go through the servers to get the data there. So simply capturing the timestamp through a Workflow is probably 90% of the battle.
For those situations where the save is delayed, maybe itโs handled as a matter of business process with a request from the affected user and Admin overrides.
Yeah @Dialect_Junk you just exposed a major design flaw, one that Iโm afraid I never really considered.
Even the solution of using a workflow to set a timestamp is kind of a Band-Aid.
What if Iโm on the job and create all of my records, but I shut the app before they have a chance to syncโฆ then the next morning when I open the app, it syncs all of those records - now my timestamps are all off.
No, we need a real solution to this; something that will appease any kind of regulatory investigators.
A solution I can think of is to have an internal clock inside the app, one that is separate from the local devices system clock and only get its updates from AppSheetโs servers. Then have some kind of function that we can use so we can call that time. SYSTEMTIME() or something.
Or, just completely switch over all of the time mechanisms to an internal clock thatโs locked into the actual time of the world. This way any current formulas using NOW() or something like that will automatically start keeping track based on the real time.
This only works for an always-online app.
Wouldnโt this require hardware support in the device?
Iโm curious how you caught this?
We noticed an employeeโs timecard timestamp didnโt match the time he actually arrived to work. We knew he arrived at 8:30AM, yet the timestamp showed 7:45AM. We thought he mustโve clocked in on the way to work, but we checked the gps capture and it showed he clocked in at the job-site.
I checked the audit log and the log captured the actual UTC time that the sync was performed, not the timestamp. This verified his timestamp was wrong and that something was amiss.
We did some poking around, asked the right questions, and found out that last year several employees were changing their deviceโs time, and then clocking in. Turns out, it was happening very frequently. We thought weโd devised a fool-proof system with timestamps and gps capture, but, obviously, we were mistaken.
As you can imagine, itโs caused quite a stir around here. The amount of loss via theft of time is incalculable and nightmarish. Quite honestly, it has our leadership group asking whether, absent a tamper-proof solution, this is the right platform for us. Iโm an advocate for the platform so Iโve fallen on the sword and accepted responsibility for not being aware of this. Iโm keeping my fingers crossed that Steveโs โband-aidโ is sufficient but I think itโs something Iโll have to monitor daily.
I can hear the war drums from my clients alreadyโฆ
Iโve also had an app of mine go through OSHA scrutiny, and they were satisfied with everything we had put in place as well. (Creation and Edit timestamps. gps, audit trail, etc.)
Nefarious players are at work everywhere, and while it may be impossible to stop them - at least we can build systems to catch them.
Iโll also add that last year we had a reportable OSHA incident where we had to provide records of our fleet inspections. We were able to provide a table of this information and satisfy the OSHA inspectors. But, now, Iโm not so sure that if our information was more closely examined, it would stand up to additional scrutiny. Especially if we were asked to verify and prove that inspection dates are accurate.
How was fraud/tampering prevented prior to apps?
Old paper and pencil system:
Lots of delays in processing payroll and invoicing if paperwork was missing or turned in late. Wildly inefficient.
Hence the move to Appsheet.
Exactly how I found appsheet; I was working as an inventory manager at a construction company, and this is how they did payroll as well.
But I was privy to both sides of that equation - I was friends with all the workers, but I worked in the office (with the people who processed payroll) - so I was able to see the truth of what was really going on there.
I think a reasonable solution is to use the sync time as recorded by the server. You can even force the app to sync when the clock-in and -out is submitted. Just make sure staff knows that they must be online when they check-in and -out.
This would be 50/50 for us. Many of our jobs are in remote areas where service is spotty or nonexistent. In these instances, employees have to wait until they get back to an area with service or back to the hotel before they can sync.
I donโt know much about much, and this could be spectacularly dumb, but, Iโm wondering if GPS may hold a solution. GPS doesnโt require internet connectivity and GPS timestamps are supposed to be accurate(?)
If the app is forced to allow location services, and the app can be forced to record GPS location upon startup, can the GPSโs timestamp be extracted from the GPS metadata at the time it was captured? This way it wouldnโt be reliant on device time or sync time.
Just an ideaโฆ
GPS can be spoofed just as easily as device time. With dozens of third party applications directly on a phone, or even directly in a chrome browser with chrome devtools:
One solution may be to have a terminal set up at the job sites where users punch in and out at (instead of using their personal devices), although this seems to forego some of the benefits you are trying to gain.
I have a hard time envisioning a foolproof system. Cheaters are going to cheat. You may be able to come up with some algorithm based upon the recorded data and server sync times, and other records being uploaded, which can help detect the cheaters. Or you may have to do spot checks at job sites.
This was an insane read.
Good to know for any future apps I make that need accurate time stamps.
So following on from the above posts, the only definitive way to get around this is use a locked down company tablet or smartphone where the user doesnโt have the permissions to change the time, put it in air plane mode or similar.
Weโve done a job where we did this for a company that was loosing ยฃ50k PA. The staff were recording on their time sheets (and getting paid for) the total hours they did. Yet they where contracted for much less. We used a reception tablet and QR Codes for each employee. Now we all know how easy it would be to copy, or even photograph a QR Code, but this leads me onto my next pointโฆ
You need to take into account the human element here. So firstly, the users only have the vaguest notion of how these Apps work. So in theory changing the phone time will work. But you can catch them out by a manual process as above. But also by some simple data checks like looking for time entries recorded out of sequence e.g.
0900
0846
0903
There a dozens of other options. But admittedly, none can prevent someone stealing who knows how the App works and its limitations. Which leads me onto point number 2. Once you, as the App dev, have detected some anomalies, you need to give the management your findings. Its not your fault they are stealing for the company. Nor it is Appsheets fault for not being โโฆtamper-proofโฆโ in terms of proof - no IT system is completely infallible. For me this is now a HR or management issue. You need to amass as much evidence of tampering as you can get and then they need to have a meeting with him RE the findings. If it were me, Iโd give the guy 2 options. Either he works for free until the money is repaid, or he is dismissed on the spot. Ditto for all the others that you have found evidence for.
People will try to steal from a company if both these two things are true:
1 = They think they can get away with it, i.e. it wonโt be detected
2 = Management wonโt do anything about it
So not only does this guy need hauling in and ideally dismissing on the spot. The management needs to tell the WHOLE COMPANY what happened and the reasons heโs gone. Trust me, you do that, it wonโt happen again.
Wanna know what the company loosing ยฃ50k did with the time sheet system I created for them - NOTHING!. The manager was so concerned she might upset some of the employees by only paying them for the shift they we asked to work that she found various reasons to not use the App. She even went as far as claiming that sheโd entered in all the shift patterns and it wasnโt letting people clock in. In truth I could see from the App sheโd not even created a single row of data since I did a few test items after installing it on her phone. Finally the business owner didnโt want to upset the manager by forcing her to do her job!
As a final example we did a big time sheet job for a construction company who had about 50 employees all working on difference sites all over the country. So the guys could choose their start and end times. But we secretly did a Change_Timestamp and Change_Location on both the start time and end time. Then we used a workflow to convert the job address via Google Maps API to a Lat Long so we could measure the distance between them and the building site. Before we even started I checked with a number of the employees who said that fiddling time sheets was โโฆindustry practiceโฆโ and they all said that everyone was adding at least 30min each day to their time sheet. So in the first App version we found that theyโd just do all their time entries at the end of the week. So we added a rule that said you have to create todayโs time entry today. So now their doing their time entry when they get home or in McDonalds. So we add another rule that says you have to be within 200m of the building site to clock in or out. The complaints started. โThis app is crap and doesnโt workโ. โI canโt clock inโฆโ etc etc. Turns out they have turned the GPS off to try and bypass the location rule. So I added a warning if the Change_Location column is either blank or 0,0 - โGPS not detect, go outside and try againโ. Now to break the App they are trying new stuff like clocking in but not out, or vice versa. Or having the end time before the start time. I add more rules to highlight these issues. This app also calculated the wages, which was important because some people are paid a day rate, others hourly and some on โpriceโ. Which meant once a particular bit of the job was marked as complete they got paid a set price for that bit. Whilst all the above is going on, some people are still submitting their time sheets on paper to the wages woman. Its taking her nearly 2 days per week to collate all the data, call people regarding missing info and chasing up late time sheets. So I convince the boss to force people to use the App by saying that in 2 weeks they will only accept time sheets via the App. He arranges a meeting with all the guys and I spend about 2 hours confirming its working on everyoneโs and getting them all to submit one test time entry. I create a whatapps group with me and all the 50 guys on in case of any issues. They are told to contact me driectly with any issues. 1 week later he calls me in a panic. Heโs getting dozens of calls per day from guys who canโt use the App. Some are threatening to quit. New guys are being told to go back to their old firms as โโฆthis app means you wonโt get paidโ. I call most of these guys and even visit a few on nearby sites. There is nothing wrong with the App. A couple more weeks pass. I can see that each day more and more people are using the App. The boss calls again, same issue. People claiming they canโt use the App. I tell him to hang on as 60% are now using this App daily and I predict that in another week it will be 80-90%. But he chickens out. Drops the App completely even though he knew the App was working fine. It was all purely the staff putting pressure on him to drop the App so they could continue to pick his pockets.
So whilst you can do all you can in terms of App development. The human issue is more powerful and can only truly be handled as HR issue.
Happy to chat on a person level with anyone if this helps
Your experience is hilariously similar to my own.
We rolled out our timecard app last April in a meeting. I tested the app on everyoneโs device and confirmed it was working. I explained and underscored, "IF YOU HAVE ANY ISSUES WITH THE APP - ANY ISSUES AT ALL - YOU MUST CALL ME RIGHT AWAY! Friday rolls around, and I get a frantic call from payroll department saying half of the timecards are missing, and the other half are incomplete. I start the call-around to find out whatโs happening.
โI couldnโt clock in,โ โI tried to sync but it didnโt let me,โ โThe app didnโt start,โ This app s#cksโฆ"
We quickly realized we were fighting human nature; by implementing the app, we were, in their eyes, โtaking moneyโ out of their pockets. We knew if we didnโt do something, weโd have a small scale mutiny on our hands.
Fortunately, the company was dedicated to making this work. We decided to get creative and made the use and accuracy of the timecard app one of the criteria in the employee bonus program. And, we also implemented a policy stating that we would only pay for the hours documented on the timecard app and that we would not chase down individual timecards anymore; if they were short a couple of hours, theyโd have to wait until the following week to get paid. These changes quickly got us to 100% use rate.
We were pretty pleased with ourselves. High fives, pats on the back, sipping champagne. That is, until last week when we found out that timestamp manipulation was fairly rampant. Turns out, they had the last laugh, and weโll never know how much it cost us.
My takeaway from this experience: fully understand and educate yourself on the limitations of the apps youโre building and compare that to what you absolutely need. While we can still use the timecard app (with some oversight/mgmt.), weโre not so sure about some of the other โcomplianceโ apps weโve developed or are in the pipeline (DOT pre-trip inspections, OSHA job site safety audits, accident reports, near-miss reports, etc.) At least, not without some additional checks and balances. Theyโre still a work in progressโฆ
A few thoughts on thisโฆ For what theyโre worthโฆ
My first point of attack is to stick with your original method with supervisors clocking people inโฆ Thatโs what we do. They can just do it slick and easy now, especially when each employee has a badgeโฆ
Second, roll with a kiosk mode tablet at a gateโฆ
If those options arenโt possible then you already wouldnโt have any better controls over paperโฆ Youโre paper would be just as potentially flubbed.
Another point to keep in mindโฆ DOT uses electronic driver logs, and the software must be registered with the DOT, self certified, in order to be utilizedโฆ So you might want to verify that the things you need for DOT donโt fall under that. They have an entire specification about the software itself. https://www.fmcsa.dot.gov/hours-service/elds/electronic-logging-devices
Thanks for input, Grant. Weโve considered both options 1 and 2 but we have too many variables to reliably implement either. And, youโre right - paper option was just as flubbed. Worse, actually.
About e-logs: fortunately for us, we fall outside of e-log requirements. We have a compliance group that ensures weโre good here. Weโre primarily concerned with pre-trip inspections and OSHA.
Thanks, again.
I was just going to chime in on this.
Before I started being an AppSheet consultant, I worked for a construction company as their inventory manager - and eventually all the DOT stuff was dropped in my lap. Worse, we had a DOT audit happening in 2 months time. Yay!
At the time of the audit I was a couple of months into learning AppSheet, and I immediately saw the potential to build a trucking DOT inspection app - so I asked the auditor when she was there about it.
Exactly what Grant was saying, the only trucking logs that will be accepted must be from an approved software source - and one of the requirements (here in Iowa at least) is that the software talk to the engineโs computer (to get information directly from it). Something that is NOT possible with an AppSheet app.
Iโve seen people building trucking inspection apps and have voiced this concern there, as I do now.
Following this closely!
Hi,
Are there any updates on this?
Followingโฆhopefully someone knows more.
This thread morphed from โpreventing timestamp manipulationโ into retrieving GPS coords for addresses. Which issue are you wanting more information on?
No change, nor would I ever expect any.
No, we discussed how we might implement this back in 2020. But since then unfortunately, it has probably been lost in the shuffle along with the many TODO features.
AppSheet has no way to prevent the user from reconfiguring their own device.
What you want is literally impossible.
User | Count |
---|---|
27 | |
25 | |
22 | |
14 | |
6 |