DFaudittrace — Used by the DF_ATmods report to read study journal files. DF_ATmods produces an audit trail report showing database modifications for the specified study.
DFaudittrace
{-s #}
[-d date1[-date2]]
[-q]
[-r]
[-N]
[-A]
[-I subjectID]
[-P #]
[-V #]
[-f fieldlist]
[-v vfence]
[-x output.xlsx]
DFaudittrace reads a record from the study journal files, calculates any changes from the previous record for those keys and then outputs the differences to DF_ATmods.
The output from DFaudittrace consists of one or more ASCII records, each having 20 fields. Each field is separated by a pipe-delimiter (|). Field contents depend on:
whether DFaudittrace is describing a new record (type N), a changed field (type C), or a deleted record (type D). This information is found in the first field of each record.
whether DFaudittrace is describing a data record, query record or a reason record. This information is found in the 8th field of each record.
The tables describe each of the 20 fields for each record type (data, query, reason) for each type of change (new record, changed field, deleted record). Fields 1 to 7 are consistent across all records output by DFaudittrace and have been described in the first table. Fields 8 to 20 depend on the record type and the type of change, and are described in tables 2 to 4.
Table 3.3. DFaudittrace Output: Fields 1 to 7
| Field | Description |
|---|---|
| 1 | Record type (N=new, C=changed field, D=deleted record) |
| 2 | Date (yyyymmdd) |
| 3 | Time (hhmmss) |
| 4 | User |
| 5 | Subject ID |
| 6 | Visit |
| 7 | Plate |
Table 3.4. New Records
| Field | Data Records | Query Records | Reason Records |
|---|---|---|---|
| 8 | 0 | >0: unique field ID of field on which query is located | <0: unique field ID of field on which reason is located |
| 9 | 0 (summary), or unique field ID | field number of Query record | field number of Reason record |
| 10 | status 1-6, 0=new missed record | status 1-6 | status 1-6 |
| 11 | validation level | validation level | validation level |
| 12 | maximum validation level reached | maximum validation level reached | maximum validation level reached |
| 13 | missed record reason code or blank | Query category code 1-6, 21-23 + DFqcproblem_map codes | reason code |
| 14 | missed record reason text or blank | Query usage code 1=internal, 2=external | reason text |
| 15 | blank | blank | blank |
| 16 | blank | blank | blank |
| 17 | blank (summary), ordinal field position | ordinal field position of data field | ordinal field position of data field |
| 18 | blank (summary), field name | field name of data field | field name of data field |
| 19 | blank (summary), old decoded label for choice/check | blank | blank |
| 20 | blank (summary), new decoded label for choice/check | blank | blank |
Table 3.5. Changed Field
| Field | Data Records | Query Records | Reason Records |
|---|---|---|---|
| 8 | 0 | >0: unique field ID of field on which query is located | <0: unique field ID of field on which reason is located |
| 9 | 0 (summary), or unique field ID | field number of Query record | field number of Reason record |
| 10 | status 1-6 | status 1-6 | status 1-6 |
| 11 | validation level | validation level | validation level |
| 12 | maximum validation level reached | maximum validation level reached | maximum validation level reached |
| 13 | blank | Query Category code 1-6, 21-23 + DFqcproblem_map codes | reason code |
| 14 | blank | Query usage code 1=internal, 2=external | reason text |
| 15 | old value | old value | old value |
| 16 | new value | new value | new value |
| 17 | ordinal field position | ordinal field position of data field | ordinal field position of data field |
| 18 | field name | field name of data field | field name of data field |
| 19 | blank, old decoded label for choice/check | blank | blank |
| 20 | blank, new decoded label for choice/check | blank | blank |
Table 3.6. Deleted Record
| Field | Data Records | Query Records | Reason Records |
|---|---|---|---|
| 8 | 0 | >0: unique field ID of field on which query is located | <0: unique field ID of field on which reason is located |
| 9 | 0 (summary), or unique field ID | field number of Query record | field number of Reason record |
| 10 | status 7 | status 7 | status 7 |
| 11 | validation level | validation level | validation level |
| 12 | maximum validation level reached | maximum validation level reached | maximum validation level reached |
| 13 | 1=missed record, 0=data record | Query category code 1-6, 21-23 + DFqcproblem_map codes | reason code |
| 14 | blank | Query usage code 1=internal, 2=external | reason text |
| 15 | blank | blank | blank |
| 16 | blank | blank | blank |
| 17 | blank (summary), ordinal field position | ordinal field position of data field | ordinal field position of data field |
| 18 | blank (summary), field name | field name of data field | field name of data field |
| 19 | blank | blank | blank |
| 20 | blank | blank | blank |
It is also important to note the following about DFaudittrace:
If a record or a query is deleted and then re-created, the re-created record or query is considered to be new record (N), not a change (C) to the previous record.
In queries, the user is sometimes, but not always set when
a query is created by the user. The user is never set when the
query is modified or deleted, or when it is created by the report
DF_QCupdate. Unknown user names appear in the output as unknown
for dates before June 1, 1995 and as ??
thereafter.
In queries, the validation level is set when the record is created and reset when the record is modified. The validation level of a query does not change when only the record's validation level changes.
If the -v vfence option is used when running DFaudittrace,
the effect is different for data records and queries. Data changes are
output for records journaled after the case reached or surpassed the
vfence validation level. Query changes are output for
queries journaled after the query was modified at or beyond the
vfence validation level.
-s | DFdiscover study number. |
-d date1[-date2] |
date of changes. This option restricts the output to changes made at
|
-q | include query details. |
-r | include reason for change details. |
-N | include all field details for type N (new) records. |
-A | output the subject alias, if defined, in place of the subject ID. If there is no subject alias, output the subject ID. |
-I | Subject ID. |
-P | DFdiscover plate number. |
-V | DFdiscover visit or sequence number. |
-f fieldlist | field number(s). This option restricts the output to the field numbers specified in the list. This may include a single field number, or a range or list of numbers. Range and list specifications may include a dash (-), tilde (~), or comma(,). |
-v vfence |
validation level. In order to be considered for output, the keys on this
record must have attained or surpassed the specified validation level at
least once. This is useful if the user wants to output all changes that were
made to records that have existed at or above the specified validation level.
Once the record keys have made it to the |
-x | Excel output file. Write the output, in Excel format, to the named file. The Excel file contains the same output, 20 columns per row, as the standard output. It includes one additional header row, with column names. |
DFaudittrace exits with one of the following statuses:
0 | The command was successful. |
1 | The command failed because the command-line arguments were not present or were incorrectly specified, the database server could not be contacted, or communication with the database server failed. |
Example 3.7. Execute DFaudittrace to output journal information for study 254 between the dates of January 1, 2001 and January 15, 2001.
% DFaudittrace -s 254 -d 20010101~20010115Example 3.8. Execute DFaudittrace to output journal information for study 254 between the dates of January 1, 1998 and today, for field numbers 10-13.
% DFaudittrace -s 254 -d 19980101-today -f 10-13Example 3.9. Execute DFaudittrace to output all journal information for study 254, for records that have existed at or above a validation level of 2 at some point during the study.
% DFaudittrace -s 254 -v 2Example 3.10. Execute DFaudittrace to generate an Excel file containing all changes for data related to subject 44002.
% DFaudittrace -s 254 -I 44002 -o 44002history.xlsx