1 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Making Audit Trails Accessible 

for the CE Quarterly Interview Survey

Brandon Kopp, Yezzi Angi Lee, Lucilla Tan

CE Methods Symposium

July 17, 2018
2 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Making Audit Trails Accessible 

 Record of all navigation and data transactions within the instrument

 Built into the Blaise programming language

 Raw audit trail files are provided for each case for a given month

"6/12/2018 7:46:31 AM","Enter Field:Sect09.TableA.Row[1].Amount","Status:Normal","Value:"

"6/12/2018 7:46:41 AM","Action:Store Field Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave Field:Sect09.TableA.Row[1].Amount","Cause:Next Field", 

"Status:Normal", "Value:32"
*Does not contain actual respondent data

Example Audit Trail
4 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov
5 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

"6/12/2018 7:24:12 AM", "Enter Field: 

Sect03.Properties[1].Sect3E.MORTGAGES[1].ORGMRTX", 

"Status:Normal","Value:“

What Happened

Datetime Stamp

Which Field

Status

Value

$250,000
6 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

"6/12/2018 7:24:12 AM", "Enter Field: 

Sect03.Properties[1].Sect3E.MORTGAGES[1].ORGMRTX", 

"Status:Normal","Value:“

"6/12/2018 7:24:47 AM","Action:Store Field Data", 

"Field:Sect03.Properties[1].Sect3E.MORTGAGES[1].ORGMR

TX“

"6/12/2018 7:24:47 AM","Leave Field: 

Sect03.Properties[1].Sect3E.MORTGAGES[1].ORGMRTX","

Cause:Next Field","Status:Normal","Value:250000“

Entered Field

Exited Field

Data Were Recorded
7 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Making Audit Trails Accessible 

 How long did it take to 

answer the question?

 Was the answer changed?

 Were any errors triggered?

 Did the interviewer access 

any help materials?

 Did the interviewer come 

back to this question later?

"6/12/2018 7:24:12 AM", "Enter Field: 

Sect03.Properties[1].Sect3E.MORTGAGES[1].ORGMRTX", 

"Status:Normal","Value:“

"6/12/2018 7:24:47 AM","Action:Store Field Data", 

"Field:Sect03.Properties[1].Sect3E.MORTGAGES[1].ORGMR

TX“

"6/12/2018 7:24:47 AM","Leave Field: 

Sect03.Properties[1].Sect3E.MORTGAGES[1].ORGMRTX","

Cause:Next Field","Status:Normal","Value:250000“

35 s Yes.

Value was added
9 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Making Audit Trails Accessible 

 Chan, Ho, Kang, Smith, & Tan (2004)

– Used SAS to parse audit trail files into four tables

– Prepared documentation for the audit trail tables

– Conducted several analyses

– Explored the idea of creating a long-term storage for future months of audit 

trail files

 Priorities changed and no further work was completed using the 

parsing program or the created datasets
10 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Making Audit Trails Accessible 

 This project looked to reprise the Chan et al. (2004) work with some modern updates

 The goal is to encourage researchers in the CE program to make use of the audit trail 

data to answer questions related to their branch

 Wrote a script that converts raw audit trail files into five rectangular datasets for 

easier analysis

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:No

rmal", "Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Norm

al", "Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Normal

", "Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave

Field:Sect09.TableA.Row[1].Amount","Cause:Next

Field", "Status:Normal", "Value:32"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Normal

", "Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave

Field:Sect09.TableA.Row[1].Amount","Cause:Next

Field", "Status:Normal", "Value:32"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Normal", 

"Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave

Field:Sect09.TableA.Row[1].Amount","Cause:Next

Field", "Status:Normal", "Value:32"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Normal", 

"Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave

Field:Sect09.TableA.Row[1].Amount","Cause:Next Field", 

"Status:Normal", "Value:32"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Normal", 

"Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave

Field:Sect09.TableA.Row[1].Amount","Cause:Next Field", 

"Status:Normal", "Value:32"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Normal", "Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave

Field:Sect09.TableA.Row[1].Amount","Cause:Next Field", 

"Status:Normal", "Value:32"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Normal", "Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave

Field:Sect09.TableA.Row[1].Amount","Cause:Next Field", 

"Status:Normal", "Value:32"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Normal", "Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave

Field:Sect09.TableA.Row[1].Amount","Cause:Next Field", 

"Status:Normal", "Value:32"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Normal", "Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave

Field:Sect09.TableA.Row[1].Amount","Cause:Next Field", 

"Status:Normal", "Value:32"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Normal", "Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave

Field:Sect09.TableA.Row[1].Amount","Cause:Next Field", "Status:Normal", 

"Value:32"

"6/12/2018 7:46:31 AM","Enter

Field:Sect09.TableA.Row[1].Amount","Status:Norm

al", "Value:"

"6/12/2018 7:46:41 AM","Action:Store Field 

Data","Field:Sect09.TableA.Row[1].Amount"

"6/12/2018 7:46:41 AM","Leave

Field:Sect09.TableA.Row[1].Amount","Cause:Next

Field", "Status:Normal", "Value:32"
Five

Linked 

Tables 

Case Table Consumer unit for one month

Form Table Instance where the instrument was 

opened and closed for a case

Field Table Interaction with a data field in the 

instrument

Action Table Audit trail line containing the “Action” 

keyword

Error Table Triggered error and its resolution
12 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Making Audit Trails Accessible 

"1/11/2018 10:06:57 AM","Enter Form:1","Key:00000102"

... Stuff happening

... Stuff happening

"1/11/2018 10:09:56 AM","Leave Form:1", 

"Key:00000102"

"1/13/2018 11:19:56 AM","Enter Form:1","Key:00000102"

... Stuff happening

... Stuff happening

"1/13/2018 12:45:18 AM","Leave

Form:1","Key:00000102"

fprimary month year

order_

enter datetime_enter order_leave datetime_leave

p00000102 1 2018 1 1/11/2018 10:06:57 AM 42 1/11/2018 10:09:56 AM

p00000102 1 2018 43 1/13/2018 11:19:56 AM 1092 1/13/2018 12:45:18 AM

p00000103 1 2018 1 1/9/2018 06:19:56 PM 2832 1/9/2018 08:45:18 PM

"1/09/2018 06:19:56 PM","Enter Form:1","Key:00000103"

... Stuff happening

... Stuff happening

"1/09/2018 08:45:18 PM","Leave Form:1","Key:00000103"

00000102.adt

00000103.adt

Form Table
Making Audit Trails Accessible 

 Some Quick Stats (August 2017)

 Raw Audit Trail Files: 

3,993

 Recorded Events: 

3,756,625

 Mean Events per Case:

 Complete: 1,662

 Incomplete: 163

 Time to Parse: 

8h 39m 24s

Number of Rows File Size

Case Table 3,993 0.7 MB

Form Table 16,950 1.4 MB

Field Table 1,247,006 181.6 MB

Action Table 853,618 73.2 MB

Error Table 11,652 2.1 MB

Total 2,133,219 259.0 MB
14 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Making Audit Trails Accessible 

 In addition to the parsing script, we have produced documentation for 

the newly created data tables

 Five tables will be produced for each month going forward

 Past months/years could be converted upon request or when time 

permits (more than 10 years’ worth)

 Tables will be stored as .csv files on secured share drive

 Establish code repository 

Reading in files

Simple calculations (e.g., section/question timing)
15 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Keeping Audit Trails Going

 We don’t want this project to suffer the same fate as Chan et al.

 We are building this pipeline for parsing audit trail files for CE 

Staff

 We are committed to knocking down barriers to CE Staff 

working with the parsed audit trails tables

Education/orientation

Providing sample analysis code

Generating research ideas 
16 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Making Audit Trails Useful

(Some Examples)
17 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Form Table
18 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Field Table
19

— U.S. BUREAU OFLABORSTATISTICS• bl s.gov

Action Table

Question Count

Sect20.GROCWEKX 12

Sect03.Sect3A1.CurrentProps[1

].Sect3ICurProps.RNTEQVX 10

Sect02.Rent[1].RENTX18

Sect12.vopgasx7

Sect20.DINE_WKX7

Question Count

Sect21.PartA[1].Salaryx 30

Sect20.GROCWEKX 22

Sect21.PartB.RETSURVX 16

Sect20.SECT20B.SALONX 14

Sect21.PartA[2].Salaryx 14

Notes Editor Calculator

Ford-Related Search Terms

fo ford f1502

for ford focus

ford ford free

ford 150 ford freestar

ford 500 ford fs

ford edg ford fusio

ford escape ford mus

ford ex ford pick

ford excurs ford ran

ford excursi ford rang

ford exp ford suber

ford explor ford t

ford f ford ta

ford f-250 ford tau

ford f 250 ford u
Error Table Question Error Text Count

Sect20.DINE_WKX THE VALUE ENTERED IS UNUSUALLY HIGH 

OR LOW PLEASE VERIFY 260

Sect09.TableA.Row[1].Amount THE VALUE ENTERED IS UNUSUALLY HIGH 

OR LOW PLEASE VERIFY 254

Sect12.TableA.Row[1].Amount THE VALUE ENTERED IS UNUSUALLY HIGH 

OR LOW PLEASE VERIFY 247

Sect20.SECT20B.SALONX THE VALUE ENTERED IS UNUSUALLY HIGH 

OR LOW PLEASE VERIFY 162

Sect20.OTHSTUFX THE VALUE ENTERED IS UNUSUALLY HIGH 

OR LOW PLEASE VERIFY 157

Questions That Trigger the Most Errors
21 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Case Table
Contact Information

22 — U.S. BUREAU OF LABOR STATISTICS • bl s.gov

Brandon Kopp

Research Psychologist

Office of Survey Methods Research

www.bls.gov/osmr

202-691-7514

kopp.brandon@bls.gov