Admin is modifyuser for many Contacts after syncs (v7.2.2)

[LEsposito]

Last year I noticed a pattern of unusually large numbers of records with Contact modifyuser being the Admin. This was odd because we never use Admin to login to the Client. We also don't use any automation that might leverage Admin. And while we do import records, these updates are not one time events but typically occurring with our Sync Server runs (every 2 hours) during a standard work day. So our admin ran profiler on the Sync Server and found many of the following: UPDATE CONTACT SET LASTHISTORYDATE = '20110209 12:17:27', LASTHISTORYBY = 'UMKRVA00003W',MODIFYDATE='20110209 19:11:03',MODIFYUSER='ADMIN' WHERE CONTACTID = 'CWMMOA500000' Apparently, when new History is added by a user, there's some hidden automation which updates Last History tracking. Unfortunately, this system logging was placed into the main Contact Table and overwrites the actual modifyuser and modifydate. Why wouldn't this logging be done in an extension table so as not to compromise the actual user update info? Is there any documentation addressing this? We had called Sage on 2 separate occasions but they weren't familiar with the issue either time, not even to say the notorious FAD! My hunch is that this problem started after 6.2.6. Perhaps Sage tweaked their LASTHISTORY Tracking process. Thanks, Larry Esposito

[LEsposito]

Sorry! Not sure why I lost the paragraph formatting! I used the Rich Text tab but maybe it was the spell check.

[JohnHedges]

So is this SQL Update statement a direct copy? If the statement were being run in the client as soon as a new History record was created, you'd think the LASTHISTORYDATE and the MODIFYDATE would be almost exactly the same, but in this example there's a 7-hour delay.

I don't believe SyncServer.exe has ever maintained LASTHISTORYBY/LASTHISTORYDATE fields on its own, has it? Are there any Agents running, or something like that? I don't think this is standard SLX behavior, though I could always be wrong I suppose.

[LEsposito]

Yes, it is an exact copy. The problem seems to be that it is not happening in the Client, at least not the LAN Client. It seems to be something inexplicably confined to sync server processing, so Sage could argue that technically the admin modify timestamp for the server is correct (i.e. when the Sync Server runs). But, since their sync testing environment is understandably weak, their support is limited in this area. And the real question is why isn't it happening in the Client, if at all?

Which is why I was reaching out to y'all! Surely others (on 7.2 with Sync Servers and don't use the Admin user in the Client or any automation) have run into this or can run the following query to see it for themselves: select modifydate from Contact where modifyuser='admin' and createuser<>'admin'.

Thanks,

Larry

[LEsposito]

We expect to hear back from Sage by tomorrow but I was hoping someone would have been familiar with this or interested enough to run the following simple query to check if they (or their Clients) have the issue. select modifydate from Contact where modifyuser='admin' and createuser<>'admin' If lots of records, are they consistent with Sync Server cycle times? As mentioned, we have no such records prior to our upgrade from 6.2.6 to 7.2.2 and Sage doesn't seem to have any documentation discussing this so I suspect this problem started in 7.x and persists to the most recent release. Why is it important? Modifyuser is one of the first places an admin can check when a user asks who may have been updating their Contact(s). Unexpected changes to Title, Status, Phone numbers, E-mail addresses, Solicitation flags and numerous other fields can all impact their relationship with their Contacts. It is not uncommon for a new user, or any user, to accidentally update the wrong field, wrong contact or even make a change they didn't even realize they made. Such changes can be disconcerting and even moreso when an Admin is helpless in tracking them down. History updates (Calls, Meetings, E-mails and Notes) are far more frequent than data changes, so Admin is quickly becoming our most prolific modifyuser. Thanks, Larry Esposito

[LEsposito]

Sage support was able to replicate the behavior using the Sync Server, but unlike our DBA, it appears they were unable to trap the error with the Profiler on the Sync Server. They are investigating if a defect has been reported or the reasoning behind the existing functionality. Thanks, Larry Esposito P.S. Not sure why theRich Text formatting on my posts is intermittently losing the carriage return/line feeds.

[LEsposito]

The defect # is 1-68150

Thanks,

Larry Esposito

[LEsposito]

My reply to Sage regarding the identified defect:

The only information I was able to find online for Defect 1-68150 is “Modifyuser does not match between remote and host database”.

I don’t think the issue identified addresses all our concerns: While the Admin Modifyuser and Sync Server cycle time Modifydate are clearly misleading and need to be corrected, updating Account and Contact Modifyuser and Modifydate for updates actually being made to the History Table is also problematic since we are quickly losing our auditing ability to identify who changed actual data in those tables and when.

In any case, I’d like to know more about the defect and defect process. Could you please send any details currently available on Defect 1-68150 including:

1. When opened
2. How replicated
3. Versions affected
4. Estimated resolution date

And do Business Partners or Clients have any input in the actual scope of the defect or setting urgency?

Thanks,

Larry Esposito

[LEsposito]

Sage found another Defect 1-67270, Modify User updating records as ADMIN instead of the correct user, which was resolved in 7.5.1. I've asked them to confirm that the Modifydate is corrected too (when History created rather than when Sync Server runs).

I'm diappointed that Account and Contact Modifydate and Modifyuser is overwritten when History is created but I doubt whether I'll get much support from Sage or anyone else for that matter.

Thanks,

Larry Esposito

[LEsposito]

I didn't want my previous post to be accepted as fact. It turns out that my assumption that the modifydate was corrected along with the modifyuser=admin problem, was incorrect ! I was later told that the modifydate issue had been raised as a separate defect (1-49907) but here was Sage's reply to that defect:

---

This is functioning as designed. The modifydate is determined when the transaction is applied to the database. So if the host or user do not sync for two days the modify dates will be off by two days.

---

I can't argue the point and even expected it with default 1-67270. But the fact that Sage corrected one defect and found the other as FAD is inconsistent. Rather than rant about it, like I used to, I'll stick to my pledge in my Arizona/Vitriol post recognizing that Sage is only trying to do their best and send an e-mail to Escalations to see if I can help their cause in a positive way.

If they want to stick to their guns and say that the actual update to the server took place at 4 am in the morning then they should identify the proper agent of that update, with Admin being the most appropriate representative for the Sync Server code. If, on the other hand, they want to make sure that it's known which user was actually responsible for the changed data, they should also make it known at what precise time that user made the change, just as they've done within the remote client's database.

I think the reason they find themselves in this quandary is because they are trying to document an administrative update (an audit of an audit, if you will) which can understandably get a little confusing. If you've been following my thinking, you know that I don't even want the Contact (or Account) table modifydate or modifyuser updated at all since the update was really to the History table. Instead I want that Contact (or Account) modifydate and modifyuser to reflect when the user made an update to the Contact (or Account) Table. In other words, just like 2 negatives, 2 audits, give me a false positive as to when something took place and I'm left with less, not more! As for the last touch, that information remains available in the Summary Tab no matter how they choose to deal with this conundrum!

Thanks,

Larry Esposito