Open Dental Forum

Hosted By Open Dental
http://opendentalsoft.com/forum/

Duplication in security log table

http://opendentalsoft.com/forum/viewtopic.php?t=6833

Page 1 of 1

Duplication in security log table

Posted: Mon Jun 11, 2018 7:31 am
by ajhalls
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"

Re: Duplication in security log table

Posted: Mon Jun 11, 2018 7:56 am
by cmcgehee
Can you show me the query that you're running?

Re: Duplication in security log table

Posted: Mon Jun 11, 2018 8:01 am
by ajhalls
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.

Re: Duplication in security log table

Posted: Mon Jun 11, 2018 9:53 am
by tgriswold
"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.

Re: Duplication in security log table

Posted: Mon Jun 11, 2018 10:15 am
by ajhalls
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

Re: Duplication in security log table

Posted: Mon Jun 11, 2018 11:09 am
by tgriswold
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.

Re: Duplication in security log table

Posted: Mon Jun 11, 2018 3:22 pm
by ajhalls
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"

Re: Duplication in security log table

Posted: Wed Jun 27, 2018 11:29 am
by tgriswold
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.
All times are UTC-07:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited