DF_ATmods — Trace database modifications over a time period
DF_ATmods
{study}
{-t }
[-w time1-time2]
[-u user1,user2]
[-f #]
[-v #]
[-I #, #-#]
[-S #, #-#]
[-P #, #-#]
[-din #, #-#]
[-dex #, #-#]
[-qin #, #-#]
[-qex #, #-#]
[-qprob #, #-#]
[-d
new,mod,del,none,all
]
[-q
new,mod,del,in,ex,none,all
]
[-N
data,qcs
]
[
[-by case]
| [-by time]
]
[-test]
DF_ATmods creates an audit trail report showing modifications (additions, changes, and deletions) made to data fields, queries and reasons over time.
A full trace of all changes made to a study database can generate thousands of pages of output. Using the available options to limit the report to show only those changes made during a short time period, or by specified users, or to data for specified subjects, visits and plates, is highly recommended.
The report's format is illustrated in the following example output:
DF_ATmods: Database Changes For Study 253 Jul 25,2016 16:56 PAGE 1 (1) -din 8-22 -N data -t 20160725 -I 1003 -P 2 (2) -------------------------------------------------------------------------------- ID=1003 SEQ=1 PLT=2 (3) 2016/07/25 13:52:55 jack DATA: NEW RECORD at level 1, incomplete (4) 8. Patient Initials = CCC REASON new at level 1: Set by edit check SetInitials 9. Entry Date = 03/07/06 10. Does pt. meet eligibility = 2 (Yes) 11. Eligibility: Explain = blank 12. Medication Code # = 2543 13. Date of Birth = 15/06/44 14. Weight in kg = 075.3 REASON new at level 1: Set by edit check CalcWeight 15. Weight in lbs = 166.0 16. Height in cm = blank 17. Height in in = blank Query new at level 1: external, missing, status = new 18. Pulse beats/minute = 062 19. Smoking Status = 1 (Never smoked) 20. Exercise = 2 (1-2 times/week) 21. Date of First Follow-up = blank REASON new at level 1: a date was not set, patient will call back to make an appointment 22. DFdiscover Screen Status = 2 (incomplete) 2016/07/25 14:53:21 susan DATA: MODIFIED at level 1, final (5) 16. Height in cm: blank -> 180 REASON new at level 1: Set by edit check CalcHeight 17. Height in in: blank -> 071.0 Query modified at level 1: external, missing, status = resolved corrected Query Status: 1 (new) -> 5 (resolved corrected) 21. Date of First Follow-up: blank -> 03/08/06 REASON modified at level 1: a date was not set, patient will call back to make an appointment -> appointment date has been set. 2016/07/25 16:43:51 bill DATA: NO CHANGE (6) 21. Date of First Follow-up: REASON approved at level 1: a date was not set, patient will call back to make an appointment
|
The report title, repeated at the top of each page, identifies the study number, and the date and time the report was executed. | |
|
The options used are identified below the report title. | |
|
The database record to which modifications were made is identified by its 3 primary keys: the subject number (ID), the visit or sequence number (SEQ), and the plate number (PLT). | |
|
Each transaction for this case is identified by the date, time and user who made the modifications, followed by the transaction type, which indicates whether the data record was new, modified, deleted or unchanged. This is followed by a listing of the data fields, identified by number and descriptive label, for which modifications were made to the data value, queries and/or reasons. For the transaction shown in this example all new data values entered for fields 8 to 22 are shown, due to options: -din 8-22 -N data. Without these options, only changes made after the initial data entry would have been shown. New and/or modified metadata (queries and reasons) are shown directly below the data field with which they are associated. The output for each transaction does not show metadata that existed but did not change during the transaction. | |
|
This transaction shows modifications made by susan about an hour after the new data record was created by jack. Susan entered the missing value for height that resulted in the resolution of the missing value query on that field. She also entered the date of the first follow-up appointment along with a reason for the change to that field. | |
|
The last transaction for this case shows bill's approval of the reason entered by susan for the change to the date of first follow-up. For each transaction, the workflow (or validation) level at which modifications are saved is shown separately for the DATA, QUERY and REASON records. |
-t | Include only changes made between the specified dates
(required).
Only one date or date range may be specified.
The keyword |
-w time1-time2 | Include only changes made between the specified times
of the day.
Only one time or time range may be specified.
Times must be specified in military (24 hour) notation
( |
-u user1,user2 | Include only changes made by the specified user(s) |
-f | Include only changes that occurred after record
reached the specified validation level.
Only one validation level may be specified.
This option restricts the output to modifications made after a
data record or query has been committed at or beyond the
specified validation level.
For example, The first commit is not reported but all subsequent modifications including those made at the specified validation level will be reported. This option will show records and queries which were deleted after reaching the specified validation level, but the delete action ends the trace and the report will not show any subsequent changes for that case; i.e. if another case is created with the same keys it too would have to reach the fence before any subsequent changes to it would be reported. |
-v | Include only changes that occurred at the specified validation level. This option restricts the output to include just those data records, queries and reasons that were created, modified or deleted at the specified validation level(s). Remember that the validation level of data records, queries and reasons may be different. Thus restricting the output to changes made at a specified validation level will show changes to data fields without showing changes that were made to queries or reasons in the same transaction if the changes to queries and reasons occurred at different validation levels. |
-I | Include only the specified subject IDs |
-S | Include only the specified sequence or visit numbers |
-P | Include only the specified plates |
-din | Show modifications made only to the specified data field numbers. It is not required that the listed fields exist on all plates. A description of data field numbers can be obtained by running DF_SSschema or DF_SSvars. |
-dex | Exclude modifications made to
the specified data field numbers.
Fields listed are not included in the report.
Exclusion has priority over inclusion; thus if
a field is specified in both the |
-qin | Show modifications made only to
the specified query field numbers.
A description of query fields can be obtained by running
DF_SSschema with the |
-qex | Exclude modifications made to
the specified query field numbers.
Fields listed are not included in the report.
Exclusion has priority over inclusion so that if
a field is specified in both the |
-qprob | Show modifications made only to
the specified query categories from the list:
|
-d new,mod,del,none,all | Include only specified types of data changes:
|
-q new,mod,del,in,ex,none,all | Include only specified types of query changes:
|
-N data,qcs | When new data records and/or queries are created,
display the fields specified with the
|
-by case, -by time | Sort the output by time (default) or, first group by case (i.e. by each unique combination of the key fields id, visit, and plate) and then sort by time within case. |
-test | Test mode. Display the chosen options and exit. |
The examples demonstrate how different combinations of options can be used to filter the audit trail information for particular purposes.
Example 2.4. Show new data records, modifications to existing records, and all internal and external queries added or modified, between Dec 1,2017 and today's date
-d new,mod -q new,mod -t 20171201~today
Example 2.5. Show modifications made to existing external queries by jane between Dec 1 and Dec 24, 2015
-u jane -t 20151201~20151224 -d none -q mod,ex
To ensure that the output includes only query modifications, and does not
also include data modifications, -d none is required.
Example 2.6. Show all modifications, excluding queries, made after a data record reached validation level 6
-v 6 -d mod -q none
Note that changes would include those that resulted from a re-submit that was processed at lower levels after the data record reached validation level 6.
Example 2.7. Show all modifications made to the database between 8am and 9am this morning
-t today -w 080000~090000
Example 2.8. Show modifications made during the year 2015 to fields 15 through 18 and 22 of external queries
-t 20150101~20151231 -d none -q mod,ex -qin 15~18,22
To also show these fields in new external queries created in the year
2015, add -q new and -N qcs
giving:
-t 20150101~20151231 -d none -q new,mod,ex -qin 15~18,22 -N qcs
Example 2.9. Show modifications made at validation levels 4 through 7, between July 1, 2015 and today's date
-v 4~7 -t 20150701~today
Example 2.10. Show modifications made after validation level 6, by jane, between Jan 1, 2015 and today's date
-f 6 -u jane -t 20150101~today
This includes changes to data records and metadata records after they were committed at levels 6 or 7. Remember that data, queries and reasons all have separate validation levels. Thus the changes displayed for a given data field may show changes to the data value but not changes that occurred during that transaction to queries and/or reasons, and vice versa, if the data and metadata records have reached different validation levels.