Google BigQuery

Configuration of audit providers is performed via modification of the application.conf file:

b2audit {
    gce {
        projectId: "development-1104"
        clientId: "426847580550-q4p9kgtrc6i61b708mmp9e36b3jt0p8h@developer.gserviceaccount.com"
        clientKey: "-----BEGIN PRIVATE KEY-----\n{b64 encode cert in here}\n-----END PRIVATE KEY-----\n"
        datasetId: "b2audit"
    }
}

The above is an example configuration for the Google Compute Engine audit client.

A Google account and subscription are required for BigQuery and storage above 1 TB a month is chargeable.

Specifying audit trail structure via JSON

Although Google’s BigQuery allows the structure of audit records to be specified via it’s WEB interface it is also possible to create them via the Platform 6 DSL method: audit.open(id URI).

The URI points to a JSON schema file:

[
    {
        "name": "company",
        "type": "STRING",
        "mode": "REQUIRED",
        "nested": []
    },
    {
        "name": "amount",
        "type": "FLOAT",
        "mode": "NULLABLE",
        "nested": []
    }, 
    {
        "name": "taxes",
        "type": "RECORD",
        "mode": "REPEATED",
        "nested": [
            {
                "name": "salestax",
                "type": "FLOAT",
                "mode": "NULLABLE",
                "nested": []
            },
            {
                "name": "taxrate",
                "type": "FLOAT",
                "mode": "NULLABLE",
                "nested": []
            }
        ]
    }
]

The structure is an array of field schemas. Each field has a unique name, a type of one of the following:

• STRING
• INTEGER
• FLOAT
• BOOLEAN
• TIMESTAMP
• RECORD

and a mode of one of the following values:

• NULLABLE
• REQUIRED
• REPEATED

The type: RECORD allows for nested child records if required.

This is an example Groovy script which created the define structure:

def success = audit.open("simonttest", "file://${B2BOX_DATA}/resources/my-audit-schema.json")
println success

This creates a table in Google BigQuery that looks like:

Note

The Platform 6 audit trail client auto creates a timestamp field.

Writing to an audit trail

Writing (posting) to an audit trail is simple in Groovy:

def auditValues = [:]
auditValues["company"] = 'Amalto'
auditValues["amount"] = 101.12

audit.post 'simonttest', auditValues

Note

Once an audit trail table has been created there is no need to use the open method again… unless you need to validate it’s existence.

Writing to a nested RECORD is more tricky:

def auditValues = [:]
def taxValues = []

def taxValue1 = [:]
taxValue1["salestax"] = 12.35
taxValue1["taxrate"] = 10
taxValues.push(taxValue1)

def taxValue2 = [:]
taxValue2["salestax"] = 123.45
taxValue2["taxrate"] = 100
taxValues.push(taxValue2)

auditValues["taxes"] = taxValues
auditValues["amount"] = 123.45

audit.post "simonttest", auditValues

Running a Google BigQuery after running the above scripts will produce results as follows: