Query Breakdown
SELECT * FROM `BigQuery-Project-Id.Dataset.AuditLog_YYYYMMDD` LIMIT 100
BigQuery-Project-Id - This is your GCP project Id, you can find this in Automate under Settings > BigQuery
Dataset - You will have specified this in Automate.
AuditLog_YYYYMMDD - This is the table you're going to be querying, they are separated by Month and Year. So if you know the year and month, you can specify YYYYMM01 and query that specific table. If you don't know your year & month, you can query all tables by replacing this with an asterisk symbol '*'.
Queryable Columns
Row | Only ideal if you have previously extracted the log and know the exact value. |
Timestamp | Only ideal if you have previously extracted the log and know the exact value. |
Actor | Ideal for filtering on users actions. |
Operation | View list of operations HERE |
Severity | View list of severity types HERE |
Context_Type | View list of context types HERE |
Context_Name | Usually populated with the value of the affected user, group, OU, domain. |
Message | The full information of the log line. |
IP | Populated with the actioning users IP address. |
Geolocation | Populated with the actioning users longitude & latitude |
Country | Populated with the actioning users countries ISO 3166 Alpha-2 code |
Region | Populated with the actioning users region |
City | Populated with the actioning users city |
Organization_Unit | Populated with the actioning users OU path |
Changes | Populated with information on the changes to a OU, Role, Profile, Group, Contact, Smart Contact Groups & integrated apps (i.e. BigQuery) |
Login_type | Populated with the how the user logged in, usually OAuth2. |
Issuer | SSO related. |
SAML_ACS_Url | SSO related. |
SAML_code | SSO related. |
User_Agent | Populated with the actioning users potential browser kit. Any logs generated server-side, will be populated with the Google AppEngine kit. |
Example Query
SELECT * FROM
`BigQuery-Project-Id.Dataset.*`
WHERE
`Context_Type`='Profile'
AND `Operation`='AssetsMoved'
OR `Operation`='TransferCalendarOwnership'
This query will search all audit tables for the context type profile, operation AssetsMoved or TransferCalendarOwner. These two operations are called during an offboarding workflow. AssetsMoved being the document transfer step and the transferCalendarOwnership being, obviously, transfer calendar ownership step.