Querying your dataset

 

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.

 

Was this article helpful?
0 out of 0 found this helpful