Audit Log query

Master Data Changes report example using the Audit query

Contents:

Filters

Filter

Description

Mandatory

Filter

Description

Mandatory

  • Audit date time (UTC) on or after / Audit date time (UTC) before

  • Audit date time (LT) on or after / Audit date time (LT) before

Filter by date and time in UTC or LT of the audit time for the change, use both on or after and before to create a data range. Note: ‘Audit date time’ UTC and LT filters cannot be used together.

YES

User

Filter to show changes by single or multiple users.

NO

Table

Filter by database table.

NO

Record

Filter by audit log recordID

NO

Data provider settings

The following ‘Data provider settings’ can be set in Reports (Administration) for the query of a report. All settings must be separated by a semi-colon ';'. If not specified the default settings apply.

Setting

Type

Default

Description

Setting

Type

Default

Description

ExcludeSystem

Boolean

False

When used this setting excludes all records created by the System user - for example Order calculation records.

AuditType

String

BLANK

When used the audit record type specified shows.

  • AuditType=I (Inserts)

  • AuditType=U (Updates)

  • AuditType=D (Deletes)

MasterDataTables

String

BLANK

Comma separated list of database tables.
This setting can hold a list of master tables that should be returned. The names are not case sensitive. If the option is not used, all types are returned.

MasterDataTables=Contact,AircraftRegistration,AircraftType,Station,Product,User,Manufacturer,Contract,IPWhitelist,PriceAgreement,Role; 

Columns

Column

Description

Example

Column

Description

Example

AuditID

Audit unique ID, the same value may be shown in multiple rows if the audit change made multiple changes.

23593175

Time stamp UTC

The date and time in UTC that the audit log change was processed.

07-Dec-21 09:29

PrimaryUserName

Shows the primary user name that carried out the audit change.

FBO One Support

PrimaryUserRemarks

Remarks for the primary user, see ‘Remarks' field in Users (Administration).

External user

SecondaryUserName

Shows the secondary user name if a group member user, this will be blank if the user is not a member of a group.

Adam Marriott

SecondaryUserRemarks

Remarks for the secondary group member user, see ‘Remarks' field in Users (Administration).

External user

Organization

Shows the contact organization used for the audit change. See 'Organization for audit log' in the Users page.

FBO One users

Description

Gives a description of the audit change for the record.

Grand Total changed from '0.0000' to '-75.4700'

Record Key

Record display name. For example, the Invoice or Contact that had an audit log change.

2849 (Sky Prime A.S)

Record link

A direct link to the audit log FBO One record.

https://testsystem.test.fbo.one/EntityRedirect.aspx?id=b92d690e-0d14-4c0a-9462-2e7a34ef2244

Master table

The master top level database table for the record.

Invoice

Table

The table for the record. Note this is often the same as the ‘Master table’, but may be different for table references. For example:

  • Master table = User

  • Table = User role

Invoice

Column

The database column in the table where the audit change for the record occured.

Grand Total

Action

The type of action for this record possible options:

  • Update

  • Insert

  • Delete

  • Remark

  • Review

  • Trace

Update

Previous value

The previous value for the record before the change. Note: For ‘Inserts’ this column will be blank.

0.0000

New value

The new value for the record after the change.

-75.4700

Master RecordID

Master record unique ID in GUID format.

b92d690e-0d14-4c0a-9462-2e7a34ef2244

Previous value in db

The previous value for the record before the change. Note: For ‘Inserts’ this column will be blank. Note: This is often the same value as ‘Previous value’.

0.0000

New value in db

The new value for the record after the change. Note: This is often the same value as ‘New value’.

-75.4700

Workstation

The device ID used and the IP address for where the action was performed.

10.51.1.5 10.50.196.11

RecordID

Record unique ID in GUID format.

b92d690e-0d14-4c0a-9462-2e7a34ef2244

Table in db

The table for the record. Note this is often the same as the ‘Master table’, but may be different for table references. Note: This is often the same value as ‘Table’. For example:

  • Master table = User

  • Table = User role

Invoice

Column in db

The database column in the table where the audit change for the record occured. Note: This is often the same value as ‘Column’.

GrandTotal

Master table in db

The master top level database table for the record. Note: This is often the same value as ‘Master table’.

Invoice

TransactionID

The transaction ID and may be displayed in multiple rows if the change was done within the same database transaction.

3818035638

SecondaryUserID

Secondary user unique ID in GUID format.

2as6f302-fasc-ggs6-fas6-as5c2asd2sde

PrimaryUserID

Primary user unique ID in GUID format.

1aa6f902-f95c-4636-a816-d25c81d0efde

QRCode

Allows generating a QR code using data from other columns, see How to add a QR code to a report.

Example shown:

  • Settings: ShowBarCodeOnEveryRow=true; MultipleValuesSeparator=" | "

  • Columns: Record link

Additional info

  • FBO One support only: With C# expression you can exclude the changes on the related tables by using this expression

    1 (string)row["Table in db"] == (string)row["Master table in db"] && (Guid)row["Master RecordID"] == (Guid)row["RecordID"]