Duplication in security log table

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Duplication in security log table

Post by ajhalls » Mon Jun 11, 2018 7:31 am

I have a query that joins on the securitylog table, but there is some duplication, in particular the Permtype 25 which is the type for "Appointment Creation". How can there be multiple appointment creation records mere seconds apart from each other?

Code: Select all

"758434"	"25"	"17"	"2018-03-19 08:11:37"	"03/21/2018 2:00:00 PM, PeriodicX, PA1st, PAadd, PAadd, 4BWX, TestMucAb, ProphyAd, TopFlApp, OralHygIn, HHX"	"9609"	"01PFDREC02"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"758435"	"25"	"17"	"2018-03-19 08:11:40"	"03/21/2018 2:00:00 PM, PeriodicX, PA1st, PAadd, PAadd, 4BWX, TestMucAb, ProphyAd, TopFlApp, OralHygIn, HHX"	"9609"	"01PFDREC02"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"758436"	"27"	"17"	"2018-03-19 08:12:10"	"03/21/2018, PeriodicX, PA1st, PAadd, PAadd, 4BWX, TestMucAb, ProphyAd, TopFlApp, OralHygIn, HHX"	"9609"	"01PFDREC02"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"758437"	"27"	"17"	"2018-03-19 08:12:14"	"03/21/2018, PeriodicX, PA1st, PAadd, PAadd, 4BWX, TestMucAb, ProphyAd, TopFlApp, OralHygIn, HHX"	"9609"	"01PFDREC02"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"758479"	"26"	"5"	"2018-03-19 08:34:31"	"PeriodicX, PA1st, PAadd, PAadd, 4BWX, TestMucAb, ProphyAd, TopFlApp, OralHygIn, HHX, from 03/21/2018 2:00:00 PM, to 03/21/2018 9:00:00 AM"	"9609"	"01PFDREC01"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"760016"	"27"	"23"	"2018-03-20 15:58:17"	"03/21/2018, PeriodicX, PA1st, TestMucAb, ProphyAd, TopFlApp, OralHygIn, HHX"	"9609"	"01PFDOPS03"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"760036"	"120"	"17"	"2018-03-20 16:02:10"	"Appointment confirmation status changed from Unconfirmed to Left Msg on Ans Mach from the appointment module."	"9609"	"01PFDREC02"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"760158"	"120"	"22"	"2018-03-21 08:55:45"	"Appointment confirmation status changed from Appointment Confirmed to Ready to go Back from the appointment module."	"9609"	"01PFDREC02"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"760161"	"26"	"23"	"2018-03-21 09:02:38"	"PeriodicX, PA1st, TestMucAb, ProphyAd, TopFlApp, OralHygIn, HHX from 03/21/2018 9:00:00 AM, to 03/21/2018 9:00:00 AM"	"9609"	"01PFDOPS03"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"760168"	"120"	"22"	"2018-03-21 09:08:11"	"Appointment confirmation status changed from Ready to go Back to In Treatment Room from the appointment module."	"9609"	"01PFDREC02"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"760206"	"27"	"23"	"2018-03-21 09:39:42"	"PeriodicX, PA1st, TestMucAb, ProphyAd, TopFlApp, OralHygIn, HHX, 03/21/2018 9:00:00 AM, Set Complete"	"9609"	"01PFDOPS03"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Duplication in security log table

Post by cmcgehee » Mon Jun 11, 2018 7:56 am

Can you show me the query that you're running?
Chris McGehee
Open Dental Software
http://www.opendental.com

ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Re: Duplication in security log table

Post by ajhalls » Mon Jun 11, 2018 8:01 am

It is a pretty basic query, here you go:

Code: Select all

                       SELECT aptnum                                             AS reference_id, 
                        appointment.patnum                                  AS patient_reference_id,
                            aptstatus                                       AS reference_state,
                            pattern                                         AS end_at,
                            confirmed                                       AS reference_status,
                        CASE WHEN (IsHygiene = 1 and ProvHyg != 0)
                            THEN ProvHyg 
                        ELSE  provnum 
                        END                                                 AS provider_reference_id,
                            Date_format(aptdatetime, '%Y-%m-%d %T')         AS start_at, 
                        nextaptnum                                          AS next_apt_id,
                            unschedstatus                                   AS unscheduled_status_id,
                            op                                              AS location_reference_id,
                            Date_format( slog.LogDateTime, '%Y-%m-%d %T')   AS scheduled_at

                        FROM   appointment
                            LEFT JOIN securitylog AS slog 
                                ON slog.FKey = appointment.AptNum
                                AND slog.PermType = 25
                        WHERE  Date_format(aptdatetime, '%Y-%m-%d %T') >= 
                        Date_format('" + Config.MinimumRecordDate + @"', '%Y-%m-%d %T')
When I run this I get duplicated appointment rows on several appointments that have this same duplication. I could select MAX() from securitylog, but that shouldn't be needed since you can only have one appointment creation event.
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Duplication in security log table

Post by tgriswold » Mon Jun 11, 2018 9:53 am

"but that shouldn't be needed since you can only have one appointment creation event."
While this theoretically and logically should be true, its not specifically enforced. The audit trail coding is so individualized that the calls to create them are not handled in a centralized manner, and there is probably some just some way to create an appointment that is creating two different ApptCreate entries. If you can identify specifically how the appointments are being created, and/or provide one of our engineers with the exact text of both audit trail entries, we'd be happy to look into it and remove the duplicate audit trail if we can find it. Additionally we'd need to know what version of OD you are on. My first guess would be something like planned appointments.
Travis Griswold
Open Dental Software
http://www.opendental.com

ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Re: Duplication in security log table

Post by ajhalls » Mon Jun 11, 2018 10:15 am

Thank you Travis, I don't have a lot of info on how and where the appointments were created, it was just an interesting issue that was breaking my application because it was creating duplicate appointments, which broke foreign key constraints. I can adjust my query, but this is the first time I had seen that issue out of about 10 databases I worked with.

The OD installation is 17.4.74.0 with MyISAM tables running on a Windows 2008 Server with E5 2407 @ 2.4GHz and 8GB RAM
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Duplication in security log table

Post by tgriswold » Mon Jun 11, 2018 11:09 am

If you can pass along the securitylog.LogDateTime and securitylog.LogText for one of the duplicate pairs I can see if I can track it down. Most times the logtext generation has slightly different formatting/wording that can point engineers in the right direction to try and stop them from being created in the future.
Travis Griswold
Open Dental Software
http://www.opendental.com

ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Re: Duplication in security log table

Post by ajhalls » Mon Jun 11, 2018 3:22 pm

Here are 2 such records:

Code: Select all

SecurityLogNum, PermType, UserNum, LogDateTime, LogText, PatNum, CompName, FKey, LogSource, DefNum, DefNumError, DateTPrevious

"758434"	"25"	"17"	"2018-03-19 08:11:37"	"03/21/2018 2:00:00 PM, PeriodicX, PA1st, PAadd, PAadd, 4BWX, TestMucAb, ProphyAd, TopFlApp, OralHygIn, HHX"	"9609"	"01PFDREC02"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"758435"	"25"	"17"	"2018-03-19 08:11:40"	"03/21/2018 2:00:00 PM, PeriodicX, PA1st, PAadd, PAadd, 4BWX, TestMucAb, ProphyAd, TopFlApp, OralHygIn, HHX"	"9609"	"01PFDREC02"	"50107"	"0"	"0"	"0"	"0001-01-01 00:00:00"

"780854"	"25"	"18"	"2018-04-23 10:49:21"	"05/14/2018 11:30:00 AM, PeriodicX, PA1st, PAadd, PAadd, 4BWX, TestMucAb, ProphyAd, TopFlApp, OralHygIn, HHX"	"6512"	"01PFDREC02"	"50634"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"780855"	"25"	"18"	"2018-04-23 10:49:43"	"05/14/2018 11:30:00 AM, PeriodicX, PeriodicX, PA1st, PAadd, PAadd, 4BWX, TestMucAb, ProphyAd, ProphyAd, TopFlApp, TopFlApp, OralHygIn, OralHygIn, HHX, HHX"	"6512"	"01PFDREC02"	"50634"	"0"	"0"	"0"	"0001-01-01 00:00:00"
"780856"	"25"	"18"	"2018-04-23 10:50:06"	"05/14/2018 11:30:00 AM, PeriodicX, PeriodicX, PA1st, PAadd, PAadd, 4BWX, TestMucAb, ProphyAd, ProphyAd, TopFlApp, TopFlApp, OralHygIn, OralHygIn, HHX, HHX"	"6512"	"01PFDREC02"	"50634"	"0"	"0"	"0"	"0001-01-01 00:00:00"
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Duplication in security log table

Post by tgriswold » Wed Jun 27, 2018 11:29 am

Thank you for the information. Unfortunately I can't seem to find where these duplicate entries are being created in the latest beta version, so I will have to leave this alone for now. It might have been fixed, or there might be some specific series of events that is falling through the cracks. If you find out you can reliably recreate these duplicate entries, please let us know the specific steps and an engineer can fix it.
Travis Griswold
Open Dental Software
http://www.opendental.com

Post Reply