Audit Trail Updates

For users or potential users.
Post Reply
Nate
Posts: 164
Joined: Wed Jun 27, 2007 1:36 pm
Location: Kansas City, MO

Audit Trail Updates

Post by Nate » Thu May 18, 2017 11:28 am

I here there are several updates to the audit trail in progress. It may be a great time to implement a few of the feature request:
1) I would suggest the ability to search the 'log text' would be very helpful. For example, if you notice a big gap in your schedule next week and wonder who was either moved or broke their appointment you could search the specific day in the log text to find out. I don't think there is another way to identify changes in the audit trail that happen on a future date when you don't know what patient you are looking for.
2) Another option is to allow audit trail to be exported to excel file and you could search the 'log text' that way.
3) It would be nice to select multiple permissions status in the audit trail rather than just 1 at a time.
4) Audit trail should show changes to a patients phone number and address
5) Audit trail should show details about the change of status. For example changed from patient to inactive.
I hope some of these can be integrated in the next version if the audit trail is really getting some updates.

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

Re: Audit Trail Updates

Post by cmcgehee » Sat May 20, 2017 12:23 pm

It turns out we put the updates to audit trail events on hold for the time being in order to work on other features. If you or other readers of this forum think that these features will add significant value, by all means let us know by voting on these feature requests: 4108, 5018, 3748, 4566. Alternatively, some of your requests could be fulfilled by a query. You can request a query to be written for you at http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx.
Chris McGehee
Open Dental Software
http://www.opendental.com

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

Re: Audit Trail Updates

Post by tgriswold » Mon May 22, 2017 1:07 pm

As Chris mentioned, your #1, #2 and #3 can be accomplished by a custom query. #4 would have to be an enhancement. #5, some of our logs do record this information and some do not. This is partially due to programming time required to make complex logs, and is sometimes due to when we first added the log including that detailed of information wasn't in the scope of what we were trying to do. While it would be possible to enhance all of the audit trail logs, in order to show information changed from X to X for all logs would take significant coding to each place we make an audit log and in some cases the text of the logs would get very very long. In other cases it might mean nothing to a user because we don't always have a definite way in just a text message to convey enough information to the user what was actually changed (think complex examples like insurance plans, or payment splits that are not distinctly identifiable by just a short name/text).

We are constantly adding new audit trail entries as we add new features, but as Chris mentioned a large overhaul is on hold due to the complexity of such a project and we only have so many programming hours each day to work on bugs/enhancements/new features. The first step is always to check out our feature request system and vote or pledge on any that sound like something you would like to see added to the program, or submitting a new request if no matching request exists for your proposed functionality. In the shorter term, I highly recommend checking out our custom query requests process Chris linked above to help you solve #1, #2 and #3 if that is important to you and your workflow. The audit logs are just like any table so we can get you something made to search for any key words or multiple types at a time, and any results could be exported to excel to use as you please.
Travis Griswold
Open Dental Software
http://www.opendental.com

Nate
Posts: 164
Joined: Wed Jun 27, 2007 1:36 pm
Location: Kansas City, MO

Re: Audit Trail Updates

Post by Nate » Thu May 25, 2017 10:31 am

So I searched the existing query list to find something that would search the "log text". I would like to know if an appointment was moved or broken on a future date. So say the morning of June 8th has a large opening and I wanted to know if a patient cancelled or moved the appointment, and who the patient was. Basically I would want to search the "log text" for the future date. Anyway I tried the query listed below and it does not seem to do what I hoped for. Can anyone modify it or let me know another way to get that information?
Thanks

/*856 Audit trail deleted adjustment entries in date range*/
SET @FromDate='2017-06-08', @ToDate='2017-06-08';
SELECT DATE_FORMAT(sl.LogDateTime,'%m/%d/%Y %h:%i %p') AS 'Log Recorded',
CONCAT(p.LName,', ',p.FName) AS 'Patient',
sl.LogText,
sl.CompName AS 'Computer Deleted From',
userod.UserName AS 'User that Deleted'
FROM securitylog sl
INNER JOIN userod ON userod.UserNum=sl.UserNum
INNER JOIN patient p ON p.PatNum=sl.PatNum
WHERE sl.PermType=18
AND sl.LogText LIKE 'Delete%'
AND DATE(sl.LogDateTime) BETWEEN @FromDate AND @ToDate
ORDER BY sl.LogDateTime;

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

Re: Audit Trail Updates

Post by cmcgehee » Thu May 25, 2017 1:33 pm

I think something like this should work. It searches the log text for the date of the appointment, so it should find any appointment that was moved to or from that date or any appointments for that date that were deleted.

Code: Select all

/*Search for appointment moved or deleted for a certain date.*/
SET @DateOrigAppt='2017-06-08';
SELECT DATE_FORMAT(sl.LogDateTime,'%m/%d/%Y %h:%i %p') AS 'Log Recorded',
CONCAT(p.LName,', ',p.FName) AS 'Patient',
sl.LogText,
sl.CompName AS 'Computer',
userod.UserName AS 'User'
FROM securitylog sl
INNER JOIN userod ON userod.UserNum=sl.UserNum
INNER JOIN patient p ON p.PatNum=sl.PatNum
WHERE sl.PermType IN(26,27) #AppointmentMove,AppointmentEdit
AND sl.LogText LIKE CONCAT('%',DATE_FORMAT(@DateOrigAppt,'%m/%d/%Y'),'%')
AND DATE(sl.LogDateTime) BETWEEN @DateOrigAppt-INTERVAL 3 MONTH AND @DateOrigAppt+INTERVAL 3 MONTH
ORDER BY sl.LogDateTime;
Chris McGehee
Open Dental Software
http://www.opendental.com

Nate
Posts: 164
Joined: Wed Jun 27, 2007 1:36 pm
Location: Kansas City, MO

Re: Audit Trail Updates

Post by Nate » Thu May 25, 2017 2:09 pm

Thanks! I think that did the trick. I did not realize how much can be pulled from a query. This will work great until the feature request get implemented.

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

Re: Audit Trail Updates

Post by cmcgehee » Thu May 25, 2017 3:25 pm

Nate wrote:I did not realize how much can be pulled from a query.
Let your imagination run wild! Chances are whatever you think up can be accomplished from a query.
Chris McGehee
Open Dental Software
http://www.opendental.com

Nate
Posts: 164
Joined: Wed Jun 27, 2007 1:36 pm
Location: Kansas City, MO

Re: Audit Trail Updates

Post by Nate » Tue May 30, 2017 7:23 am

Well I thought it worked just how I hoped but today we had a message on the recorder from someone that was sick. So the front desk broke the appointment and made a note. Then sent the appointment to the unscheduled list. I tried running the query but the patient did not show up for todays broken appointment. Is there a reason for that?

This patient had been moved from another day to today a while back. So she did show up on the list being moved from last month to today. But as far as today it did not show broken or removed from the schedule. In fact, no log entry on the list for her today at all. Is it because she had been moved before? or is it because she was sent to the unscheduled list and not rescheduled to another date?

We just had another appointment that called to reschedule. I ran the query again and this patient showed up on the list with the new appointment date. However, the first did not show still. I'm guessing its because it was broken and not moved or rescheduled.

So is there a way to also show appointments that were broken for a specific date as well as moved? I would like the first patient to show up on the list as well. Can anyone help? Is there a modification to the query that would fix it.

Thanks again,
Nathan

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

Re: Audit Trail Updates

Post by cmcgehee » Tue May 30, 2017 8:27 am

If you are able to break an appointment or send it to the Unscheduled list with no audit trail entry being made, then we would consider that a bug and get that fixed. If you are on the latest stable version (16.4.X) and you can tell us the exact steps you take to make this happen, we will look into this potential bug.
Chris McGehee
Open Dental Software
http://www.opendental.com

Nate
Posts: 164
Joined: Wed Jun 27, 2007 1:36 pm
Location: Kansas City, MO

Re: Audit Trail Updates

Post by Nate » Tue May 30, 2017 10:23 am

There is an audit trail entry made when the appointment is broke, so no bug. It was just the Query that did not list a patient or appointment when it was broken and not rescheduled. Any idea on how to modify the query to also include any appointment that was simply broken and possibly moved to the unscheduled list?

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

Re: Audit Trail Updates

Post by cmcgehee » Tue May 30, 2017 1:14 pm

If the appointment was already complete, then it wouldn't show up in the original query. I modified it below to include completed appointments. If the appointment was not complete, then it would show in the query if the log text in the audit trail had the date of the appointment in the format mm/dd/yyyy.

Code: Select all

/*Search for appointment moved or deleted for a certain date.*/
SET @DateOrigAppt='2017-06-08';
SELECT DATE_FORMAT(sl.LogDateTime,'%m/%d/%Y %h:%i %p') AS 'Log Recorded',
CONCAT(p.LName,', ',p.FName) AS 'Patient',
sl.LogText,
sl.CompName AS 'Computer',
userod.UserName AS 'User'
FROM securitylog sl
INNER JOIN userod ON userod.UserNum=sl.UserNum
INNER JOIN patient p ON p.PatNum=sl.PatNum
WHERE sl.PermType IN(26,27,96) #AppointmentMove,AppointmentEdit,AppointmentCompleteEdit
AND sl.LogText LIKE CONCAT('%',DATE_FORMAT(@DateOrigAppt,'%m/%d/%Y'),'%')
AND DATE(sl.LogDateTime) BETWEEN @DateOrigAppt-INTERVAL 3 MONTH AND @DateOrigAppt+INTERVAL 3 MONTH
ORDER BY sl.LogDateTime;
Chris McGehee
Open Dental Software
http://www.opendental.com

User avatar
JasonL
Posts: 28
Joined: Wed Jun 10, 2015 1:57 pm

Re: Audit Trail Updates

Post by JasonL » Tue May 30, 2017 1:28 pm

Nate wrote:There is an audit trail entry made when the appointment is broke, so no bug. It was just the Query that did not list a patient or appointment when it was broken and not rescheduled. Any idea on how to modify the query to also include any appointment that was simply broken and possibly moved to the unscheduled list?
I have taken the query and run it on my test database but I am not having anything disappear if I move it to the Unscheduled list or if I break the appointment. Here are the steps I used to test:
1. Created an appointment on 5/30 for Patient A
2. Moved the appointment to 5/31 for Patient A, ran the report using date '2017-05-30' and confirmed that the row displays
3. Create an appointment on 05/30 for Patient B
4. Broke the appointment on 05/30 for Patient B, ran the report using date '2017-05-30' and confirmed that the row displays
5. Create an appointment on 05/30 for Patient C
6. Moved Patient C's appointment to the Unscheduled list, ran the report using date '2017-05-30' and confirmed that the row displays

I should note that I am testing on v17.1.16. In which instances does the query not show the expected results? Also - which version of Open Dental are you using?
Jason Long
Open Dental Web Technology Coordinator
www.opendental.com

Nate
Posts: 164
Joined: Wed Jun 27, 2007 1:36 pm
Location: Kansas City, MO

Re: Audit Trail Updates

Post by Nate » Tue May 30, 2017 2:13 pm

1. Created an appointment on 5/30 for Patient A
2. Moved the appointment to 5/31 for Patient A, ran the report using date '2017-05-30' and confirmed that the row displays
----> This worked correctly and showed on the list
3. Create an appointment on 05/30 for Patient B
4. Broke the appointment on 05/30 for Patient B, ran the report using date '2017-05-30' and confirmed that the row displays
----->This did not show on list
5. Create an appointment on 05/30 for Patient C
6. Moved Patient C's appointment to the Unscheduled list, ran the report using date '2017-05-30' and confirmed that the row displays
---->This did not show on the list

I also tested by creating an appointment for June 1, then break the appointment, then sent to unscheduled list. Ran the query for 6/1/17 and it does not show on the list.

I am using v16.3.46. Is it something to do with my version? What version might I get the results to show in all the above cases?
Thanks

User avatar
JasonL
Posts: 28
Joined: Wed Jun 10, 2015 1:57 pm

Re: Audit Trail Updates

Post by JasonL » Tue May 30, 2017 2:45 pm

I just tested this using different databases and did find, as you have been experiencing, that the broken appointments and moved-to-unscheduled appointments would not populate on this report on 16.3. I then tried my 16.4 database and got the results we would normally expect. I am not certain if there were audit trail changes between these two versions but if you update to the latest stable version (16.4.44) the report should begin working for you. Hope this helps! :-)
Jason Long
Open Dental Web Technology Coordinator
www.opendental.com

Post Reply