Data Export for Auditing

Account administrators and users with the Export data role permission can export OpenAir data for auditing purpose.

You can export all of your account data or selected tables in MySQL or delimited text format. See Exporting OpenAir data.

You can then process the exported data to extract the desired audit information. See Using the Exported OpenAir Data for Auditing.

Note:

If the Automatic Backup Service feature is enabled on your account, you can use the regular backup instead of exporting your account data manually. See Automatic Backup Service.

Tip:

Consult the Database Guide and OpenAir Data Dictionary for reference when selecting tables for data export and processing the exported data.

Note:

To view the OpenAir Data Dictionary, use the following URL: https://<account-domain>/database/single_user.html.

The URL includes the domain name for your OpenAir account <account-domain>. For more information about your account-specific domain name, see Your Account URLs.
To view the details of a specific table, append a hash symbol # followed by the table name to the end of the data dictionary URL. For example, use https://<account-domain>/database/single_user.html#project to view the details of the Project table.
You can access the data dictionary from the OpenAir Help Center using the link in the navigation bar if you have the View Help Center role permission.

Exporting OpenAir data

To export OpenAir data:

Go to Administration Global Settings > Account and click Integration: Import/Export.
In the Import/Export screen, click the All data in text format or All data in MySQL format link.
Choose which tables to exclude — select any tables to exclude from the export.

Tip:
If you only want to include a small number of tables, first click Select all to exclude all tables, and then select the tables you want to include.
If exporting the data as delimited text:
- Text delimiter — Select comma-delimited, tab-delimited, or a regional setting CSV list separator.
- Data layout — Select whether to include column headings in the exported data.
- New line format — Specify the new line format corresponding to the Operating System on your machine.
- Suppress the audit field data — Check this box if applicable.
  
  Important:
  Do not check the Suppress the audit field data box when exporting data for auditing purposes.
Click Export. A progress bar displays while OpenAir generates a ZIP file containing the generated data.
Click the Click here link to download the ZIP file with your data.
Extract and review the content of the ZIP archive you downloaded. Depending on the format you chose for exporting your data, the ZIP archive will contain:
- A MySQL file — Use this file to import the data into a MySQL database and run queries and reports.
- A series of CSV files (one for every table selected for export) — Use these files to import selected data into a spreadsheet or run scripts to analyze the data.

Using the Exported OpenAir Data for Auditing

You can use the OpenAir data exported in MySQL or delimited text format to extract auditing information. Review the User access log and Deleted records log examples below as well as the Quick reference for fields/columns commonly use for auditing purposes.

See the Database Guide and OpenAir Data Dictionary for more detailed information about the data structure, tables and fields available in the OpenAir database.

Note:

To view the OpenAir Data Dictionary, use the following URL: https://<account-domain>/database/single_user.html.

The URL includes the domain name for your OpenAir account <account-domain>. For more information about your account-specific domain name, see Your Account URLs.
To view the details of a specific table, append a hash symbol # followed by the table name to the end of the data dictionary URL. For example, use https://<account-domain>/database/single_user.html#project to view the details of the Project table.
You can access the data dictionary from the OpenAir Help Center using the link in the navigation bar if you have the View Help Center role permission.

User access log

You can audit sign-in attempts using the exported OpenAir data.

To audit user sign-in attempts using export data:

Export the user_login and user tables as delimited text. See Exporting OpenAir data.
Extract the content of the ZIP archive containing the export data and open user_login.csv in a spreadsheet.
Review the following fields/columns:
- user_id — The unique ID of the user attempting to sign in. Cross-references user data in the user table.
- logintime — The date and time of the sign-in attempt.
- source — The location the sign-in originated from, typically an IP address.
- status — The status or outcome of the sign-in attempt.
- api — the sign-in mechanism used.
Note:
See Quick reference for fields/columns commonly use for auditing purposes for a detailed description of the status and api fields, including how to read the value for these fields

Deleted records log

When a user deletes a record in OpenAir, the record is flagged as deleted and continues to be stored in the database for some time before it is permanently deleted. You can export OpenAir data and query all or selected tables for records with a deleted flag.

All tables storing records that can be deleted manually by users in OpenAir include a deleted field. This field is blank by default. When a user deletes the record, the value of the deleted field is set to 1. Use the audit field to identify who deleted the record and when it was deleted.

Important:

Records flagged as deleted which have not been updated for more than 180 days are removed permanently from the database according to a routine schedule. See Data Deletion.

Quick reference for fields/columns commonly use for auditing purposes

Field / Column	Table / CSV File	Description
status	user_login	The status of the sign-in. The value is a status code: S — Successful P — Password incorrect I — Inactive user L — Locked user R — Restricted IP address
api	user_login	The login mechanism. Possible values: [empty value] — interactive (non-api) sign-in 1 — XML API sign-in 2 — SOAP API sign-in Note: A `user_login` entry with `api` values `1` and `2` is added when the client application uses the `Auth` (XML API) or `login()` (SOAP API) for authentication. No entry is recorded when using a OAuth 2.0 access token or client session ID for authentication. A `user_login` entry with an empty value for `api` is added when the user authorizes the client application as part of the OAuth2.0 authorization code flow.
mfa_status	user	The user's two-factor authentication (2FA) status. Possible values: `0` – 2FA is not required. `1` – 2FA is required and the user must set up 2FA. `2` – 2FA is required and the user has paired an authenticator app with OpenAir. `7` – 2FA is required and the user has completed the 2FA setup.
audit	Most tables — refer to OpenAir Data Dictionary.	Each audit trail entry is recorded on a separate line in the audit trail field. Each audit trail entry follows a specific format of information delimited by commas. For example: `C,2019-07-12 17:25:48,1,1,db_id:146 U,2019-07-13 12:40:40,1,1,db_id:146,name,'Collins, Marc',last,'Collins'` The audit trail entry information can be interpreted as follows: 1st value — Record Created / Updated: C — Created U — Updated 2nd value — Timestamp. The timestamp may include a unique epoch reference if the modified fields include a custom field with extended audit. See audit_custom_[custom field ID] below. 3rd value — ID of the user creating/updating the record. This can be either: user_id user_id:proxy_id — ID of the signed in user followed by ID of the user for whom the signed in user is proxying in. 4th value — Sign-in mechanism. Possible values: 0 — User interface 1 — XML API 2 — SOAP API 3 — NetSuite integration 5 — Workday integration 6 — SOAP API via User Scripting 8 — REST API 5th Value — db_id followed by your OpenAir account ID. Subsequent values — [field name],[previous value] pairs delimited by commas. In the above example, name and last_name were modified when the record was updated, to ‘Collins, Marc’ and ‘Collins’ respectively.
custom_ [custom field ID]	Table for the entity to which the custom field is associated	Custom fields appear in the database under generic names formed of the prefix ‘custom_’ followed by the unique ID of the custom field (its ID in the cust_field table).
audit_custom_[custom field ID]	Table for the entity to which the custom field is associated	Audit extension fields for custom field of types Text Area, Pick List, Multiple Selection and Allocation Grid, appear in the database under generic names formed of the prefix ‘audit_custom_’ followed by the unique ID of the custom field (its ID in the cust_field table). For these custom field types, values will not be recorded in the audit trail entry unless the audit is extended. See Quick Audit Trail for Custom Fields. If the audit is not extended, the audit trail entry in the audit field will show ‘CHANGED’ instead of the previous value for the modified field. For example: `U,2019-07-31 07:15:57,2,0,db_id:146,custom_120,CHANGED` If the audit is extended for the custom field, a unique reference is added to the timestamp for the audit trail entry in the audit field. For example: `U,2019-07-31 07:21:13 iBICToWz6RG93KWt9zCm/A==,2,0,db_id:146,custom_120,CHANGED` A similar entry is added to the audit extension field for the custom field and includes the value for the modified custom field. For example: `U,2019-07-31 07:21:13 iBICToWz6RG93KWt9zCm/A==,2,0,db_id:146,custom_120,'192.0.2.0/24'`

Field / Column

Table / CSV File

Description

status

user_login

The status of the sign-in. The value is a status code:

S — Successful
P — Password incorrect
I — Inactive user
L — Locked user
R — Restricted IP address

api

user_login

The login mechanism. Possible values:

[empty value] — interactive (non-api) sign-in
1 — XML API sign-in
2 — SOAP API sign-in

Note:

A user_login entry with api values 1 and 2 is added when the client application uses the Auth (XML API) or login() (SOAP API) for authentication. No entry is recorded when using a OAuth 2.0 access token or client session ID for authentication.

A user_login entry with an empty value for api is added when the user authorizes the client application as part of the OAuth2.0 authorization code flow.

mfa_status

user

The user's two-factor authentication (2FA) status. Possible values:

0 – 2FA is not required.
1 – 2FA is required and the user must set up 2FA.
2 – 2FA is required and the user has paired an authenticator app with OpenAir.
7 – 2FA is required and the user has completed the 2FA setup.

audit

Most tables — refer to OpenAir Data Dictionary.

Each audit trail entry is recorded on a separate line in the audit trail field. Each audit trail entry follows a specific format of information delimited by commas. For example:

                        C,2019-07-12 17:25:48,1,1,db_id:146
U,2019-07-13 12:40:40,1,1,db_id:146,name,'Collins, Marc',last,'Collins'

The audit trail entry information can be interpreted as follows:

1st value — Record Created / Updated:
- C — Created
- U — Updated
2nd value — Timestamp. The timestamp may include a unique epoch reference if the modified fields include a custom field with extended audit. See audit_custom_[custom field ID] below.
3rd value — ID of the user creating/updating the record. This can be either:
- user_id
- user_id:proxy_id — ID of the signed in user followed by ID of the user for whom the signed in user is proxying in.
4th value — Sign-in mechanism. Possible values:
- 0 — User interface
- 1 — XML API
- 2 — SOAP API
- 3 — NetSuite integration
- 5 — Workday integration
- 6 — SOAP API via User Scripting
- 8 — REST API
5th Value — db_id followed by your OpenAir account ID.
Subsequent values — [field name],[previous value] pairs delimited by commas.

In the above example, name and last_name were modified when the record was updated, to ‘Collins, Marc’ and ‘Collins’ respectively.

custom_ [custom field ID]

Table for the entity to which the custom field is associated

Custom fields appear in the database under generic names formed of the prefix ‘custom_’ followed by the unique ID of the custom field (its ID in the cust_field table).

audit_custom_[custom field ID]

Table for the entity to which the custom field is associated

Audit extension fields for custom field of types Text Area, Pick List, Multiple Selection and Allocation Grid, appear in the database under generic names formed of the prefix ‘audit_custom_’ followed by the unique ID of the custom field (its ID in the cust_field table).

For these custom field types, values will not be recorded in the audit trail entry unless the audit is extended. See Quick Audit Trail for Custom Fields.

If the audit is not extended, the audit trail entry in the audit field will show ‘CHANGED’ instead of the previous value for the modified field. For example:

                        U,2019-07-31 07:15:57,2,0,db_id:146,custom_120,CHANGED

If the audit is extended for the custom field, a unique reference is added to the timestamp for the audit trail entry in the audit field. For example:

                        U,2019-07-31 07:21:13 iBICToWz6RG93KWt9zCm/A==,2,0,db_id:146,custom_120,CHANGED

A similar entry is added to the audit extension field for the custom field and includes the value for the modified custom field. For example:

                        U,2019-07-31 07:21:13 iBICToWz6RG93KWt9zCm/A==,2,0,db_id:146,custom_120,'192.0.2.0/24'

Note:

To view the OpenAir Data Dictionary, use the following URL: https://<account-domain>/database/single_user.html.

The URL includes the domain name for your OpenAir account <account-domain>. For more information about your account-specific domain name, see Your Account URLs.
To view the details of a specific table, append a hash symbol # followed by the table name to the end of the data dictionary URL. For example, use https://<account-domain>/database/single_user.html#project to view the details of the Project table.
You can access the data dictionary from the OpenAir Help Center using the link in the navigation bar if you have the View Help Center role permission.