All
security
CM360 User Audit
Gives CM clients ability to see which profiles have access to which parts of an account. Loads CM user profile mappings using the API into BigQuery and connects to a DataStudio dashboard.
Impact Level
| Spend Optimization |
|
| Spend Growth |
|
| Time Savings |
|
| Account Health |
|
| Csat Improvement |
|
Value Proposition
| Meet contractual access reporting information. |
| Reduce unauthorized use of CM accounts and assets. |
| Audit user access within CM. |
| Prevent malicious user access / behavior. |
Instructions
| Wait for BigQuery->->->CM_... to be created. |
| Wait for BigQuery->->->Barnacle_... to be created, then copy and connect the following data sources. |
| Join the StarThinker Assets Group to access the following assets |
| Copy Barnacle Profile Advertiser Map and connect. |
| Copy Barnacle Profile Campaign Map and connect. |
| Copy Barnacle Profile Site Map and connect. |
| Copy Barnacle Profiles Connections and connect. |
| Copy Barnacle Report Delivery Profiles and connect. |
| Copy Barnacle Roles Duplicates and connect. |
| Copy Barnacle Roles Not Used and connect. |
| Copy Barnacle Site Contacts Profiles and connect. |
| If reports checked, copy Barnacle Profile Report Map and connect. |
| Copy Barnacle Report. |
| When prompted choose the new data sources you just created. |
| Or give these intructions to the client. |
Details
| Open Source | YES |
| Age | Feb. 27, 2019 (3 years, 10 months) |
| Authors | ceh@google.com kenjora@google.com |
| Shedule Days | Configured by user. |
| Shedule Hours | 1, 3, 8 |
[
{
"dataset": {
"description": "The dataset will hold multiple tables, make sure it exists.",
"hour": [
1
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 4,
"default": "",
"description": "Name of Google BigQuery dataset to create."
}
}
}
},
{
"barnacle": {
"description": "Will create tables with format CM_* to hold each endpoint via a call to the API list function. Exclude reports for its own task.",
"hour": [
1
],
"auth": {
"field": {
"name": "auth_read",
"kind": "authentication",
"order": 0,
"default": "user",
"description": "Credentials used for reading data."
}
},
"reports": {
"field": {
"name": "reports",
"kind": "boolean",
"order": 3,
"default": false,
"description": "Include report audit, consumes significant API and data."
}
},
"accounts": {
"single_cell": true,
"values": {
"field": {
"name": "accounts",
"kind": "integer_list",
"order": 2,
"default": [],
"description": "Comma separated CM account ids."
}
}
},
"out": {
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"order": 4,
"default": "",
"description": "Google BigQuery dataset to create tables in."
}
}
}
}
},
{
"bigquery": {
"hour": [
8
],
"description": "Combine profile, account, subaccount, and roles into one view, used by other views in this workflow.",
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"from": {
"legacy": false,
"query": " SELECT P.profileId AS profileId, P.accountId AS accountId, P.subaccountId AS subaccountId, P.name AS Profile_Name, P.email AS Profile_Email, REGEXP_EXTRACT(P.email, r'@(.+)') AS Profile_Domain, P.userAccessType AS Profile_userAccessType, P.active AS Profie_active, P.traffickerType AS Profile_traffickerType, P.comments AS Profile_comments, P.userRoleId AS Profile_userRoleId, R.role_name AS Role_role_name, R.role_defaultUserRole AS Role_role_defaultUserRole, R.permission_name AS Role_permission_name, R.permission_availability AS Role_permission_availability, A.name AS Account_name, A.active AS Account_active, A.description AS Account_description, A.locale AS Account_locale, S.name AS SubAccount_name FROM `{dataset}.CM_Profiles` AS P LEFT JOIN `{dataset}.CM_Roles` AS R ON P.userRoleId=R.roleId LEFT JOIN `{dataset}.CM_Accounts` AS A ON P.accountId=A.accountId LEFT JOIN `{dataset}.CM_SubAccounts` AS S ON P.accountId=S.accountId AND P.subaccountId=S.subaccountId ; ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
}
}
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "Barnacle_Profile_Role_Account_SubAccount_Map"
}
}
},
{
"bigquery": {
"description": "Combine profiles and advertisers.",
"hour": [
8
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"from": {
"legacy": false,
"query": " SELECT APRASM.*, A.advertiserId AS advertiserId, A.name AS Advertiser_name, A.status AS Advertiser_status, A.defaultEmail AS Advertiser_defaultEmail, A.suspended AS Advertiser_suspended FROM `{dataset}.CM_Profile_Advertisers` As PA LEFT JOIN `{dataset}.Barnacle_Profile_Role_Account_SubAccount_Map` AS APRASM ON PA.profileID=APRASM.profileID LEFT JOIN `{dataset}.CM_Advertisers` AS A ON PA.advertiserId=A.advertiserId ; ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
}
}
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "Barnacle_Profile_Advertiser_Map"
}
}
},
{
"bigquery": {
"description": "Profile to campaign mapping.",
"hour": [
8
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"from": {
"legacy": false,
"query": " SELECT APRASM.*, C.campaignId AS campaignId, C.name AS Campaign_name, C.archived AS Campaign_archived, IF(C.startDate <= CURRENT_DATE() AND C.endDate >= CURRENT_DATE(), True, False) AS Campaign_running, ROUND(TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), C.lastModifiedInfo_time, DAY) / 7) AS Campaign_Modified_Weeks_Ago FROM `.{dataset}.CM_Profile_Campaigns` As PC LEFT JOIN `{dataset}.Barnacle_Profile_Role_Account_SubAccount_Map` AS APRASM ON PC.profileID=APRASM.profileID LEFT JOIN `{dataset}.CM_Campaigns` AS C ON PC.campaignId=C.campaignId ; ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
}
}
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "Barnacle_Profile_Campaign_Map"
}
}
},
{
"bigquery": {
"description": "The logic query for Deal Finder, transforms report into view used by datastudio.",
"hour": [
8
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"from": {
"legacy": false,
"query": " SELECT APRASM.*, R.reportId AS reportId, R.name AS Report_name, R.type AS Report_type, R.format AS Report_format, R.schedule_active AS Report_schedule_active, R.schedule_repeats AS Report_schedule_repeats, ROUND(TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), R.lastModifiedTime, DAY) / 7) AS Report_Modified_Weeks_Ago, DATE_DIFF(R.schedule_expirationDate, CURRENT_DATE(), MONTH) AS Report_Schedule_Weeks_To_Go FROM `{dataset}.CM_Reports` As R LEFT JOIN `{dataset}.Barnacle_Profile_Role_Account_SubAccount_Map` AS APRASM ON R.profileID=APRASM.profileID ; ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
}
}
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "Barnacle_Profile_Report_Map"
}
}
},
{
"bigquery": {
"description": "The logic query for Deal Finder, transforms report into view used by datastudio.",
"hour": [
8
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"from": {
"legacy": false,
"query": " SELECT APRASM.*, S.siteId AS siteId, S.name AS Site_Name, S.keyName AS Site_keyName, S.approved AS Site_approved FROM `{dataset}.CM_Profile_Sites` As PS LEFT JOIN `{dataset}.Barnacle_Profile_Role_Account_SubAccount_Map` AS APRASM ON PS.profileID=APRASM.profileID LEFT JOIN `{dataset}.CM_Sites` AS S ON PS.siteId=S.siteId ; ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
}
}
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "Barnacle_Profile_Site_Map"
}
}
},
{
"bigquery": {
"description": "The logic query for Deal Finder, transforms report into view used by datastudio.",
"hour": [
8
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"from": {
"legacy": false,
"query": " SELECT APRASM.* FROM `{dataset}.Barnacle_Profile_Role_Account_SubAccount_Map` AS APRASM LEFT JOIN `{dataset}.CM_Profile_Advertisers` AS PA ON APRASM.profileId=PA.profileId LEFT JOIN `{dataset}.CM_Profile_Campaigns` AS PC ON APRASM.profileId=PC.profileId LEFT JOIN `{dataset}.CM_Profile_Sites` AS PS ON APRASM.profileId=PS.profileId WHERE PA.advertiserId IS NULL AND PC.campaignId IS NULL AND PS.siteId IS NULL ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
}
}
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "Barnacle_Profiles_Connections"
}
}
},
{
"bigquery": {
"description": "",
"hour": [
8
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"from": {
"legacy": false,
"query": " SELECT RD.accountId AS accountId, RD.subaccountId AS subaccountId, RD.reportId AS reportId, A.name AS Account_name, A.active AS Account_active, SA.name as SubAccount_name, R.name as Report_name, R.schedule_active AS Report_schedule_active, RD.emailOwnerDeliveryType AS Delivery_emailOwnerDeliveryType, RD.deliveryType AS Delivery_deliveryType, RD.email AS Delivery_email, RD.message AS Delivery_message, IF(RD.email in (SELECT email from `{dataset}.CM_Profiles`), True, False) AS Profile_Match_Exists FROM `{dataset}.CM_Report_Deliveries` AS RD LEFT JOIN `{dataset}.CM_Accounts` AS A ON RD.accountId=A.accountId LEFT JOIN `{dataset}.CM_SubAccounts` AS SA ON RD.subaccountId=SA.subaccountId LEFT JOIN `{dataset}.CM_Reports` AS R ON RD.reportId=R.reportId ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
}
}
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "Barnacle_Report_Delivery_Profiles"
}
}
},
{
"bigquery": {
"description": "",
"hour": [
8
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"from": {
"legacy": false,
"query": " SELECT R.accountId AS accountId, R.subaccountId AS subaccountId, R.roleId AS roleId, A.name AS Account_name, A.active AS Account_active, SA.name AS SubAccount_name, R.role_name as Role_role_name, R.role_defaultUserRole AS Role_role_defaultUserRole, R.permission_name AS Role_permission_name, R.permission_availability AS Role_permission_availability FROM `{dataset}.CM_Roles` AS R LEFT JOIN `{dataset}.CM_Accounts` AS A on R.accountId=A.accountId LEFT JOIN `{dataset}.CM_SubAccounts` AS SA on R.subaccountId=SA.subaccountId WHERE roleId NOT IN ( SELECT roleId FROM `{dataset}.CM_Profile_Roles` ) ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
}
}
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "Barnacle_Roles_Not_Used"
}
}
},
{
"bigquery": {
"description": "",
"hour": [
8
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"from": {
"legacy": false,
"query": " SELECT SC.accountId AS accountId, SC.subaccountId AS subaccountId, SC.siteId AS siteId, SC.contactId AS contactId, A.name AS Account_name, A.active AS Account_active, SA.name as SubAccount_name, S.name as Site_name, S.approved AS Site_approved, SC.email AS Site_Contact_email, CONCAT(SC.firstName, ' ', sc.lastname) AS Site_Contact_Name, SC.phone AS Site_Contact_phone, SC.contactType AS Site_Contact_contactType, IF(sc.email in (SELECT email from `{dataset}.CM_Profiles`), True, False) AS Profile_Match_Exists FROM `{dataset}.CM_Site_Contacts` AS SC LEFT JOIN `{dataset}.CM_Accounts` AS A ON SC.accountId=A.accountId LEFT JOIN `{dataset}.CM_SubAccounts` AS SA ON SC.accountId=SA.accountId AND SC.subaccountId=SA.subaccountId LEFT JOIN `{dataset}.CM_Sites` AS S ON SC.siteId=S.siteId ; ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
}
}
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "Barnacle_Site_Contacts_Profiles"
}
}
},
{
"bigquery": {
"description": "",
"hour": [
8
],
"auth": {
"field": {
"name": "auth_write",
"kind": "authentication",
"order": 1,
"default": "service",
"description": "Credentials used for writing data."
}
},
"from": {
"legacy": false,
"query": " WITH profile_counts AS ( SELECT userRoleId, COUNT(profileId) as profile_count FROM `{dataset}.CM_Profiles` GROUP BY 1 ), permission_fingerprints AS ( SELECT accountId, subaccountId, roleId, role_name, role_defaultUserRole, SUM(profile_count) AS profile_count, FARM_FINGERPRINT( ARRAY_TO_STRING( ARRAY_AGG( DISTINCT permission_name ORDER BY permission_name ASC ), ',', '-' ) ) AS permissions_fingerprint FROM `{dataset}.CM_Roles` AS R LEFT JOIN profile_counts AS P ON R.roleId = P.userRoleId GROUP BY accountId, subaccountId, roleId, role_name, role_defaultUserRole ) SELECT PFL.accountId AS accountId, A.name AS Account_name, A.active AS Account_active, PFL.subaccountId AS subaccountId, SA.name AS SubAccount_name, PFL.roleId AS roleId, PFL.role_name AS role_name, PFL.role_defaultUserRole AS role_defaultUserRole, COALESCE(PFL.profile_count, 0) AS profile_count, PFR.roleId AS duplicate_roleId, PFR.role_name AS duplicate_role_name, PFR.role_defaultUserRole AS duplicate_role_defaultUserRole, COALESCE(PFR.profile_count, 0) AS duplicate_profile_count FROM permission_fingerprints AS PFL LEFT JOIN `{dataset}.CM_Accounts` AS A on PFL.accountId=A.accountId LEFT JOIN `{dataset}.CM_SubAccounts` AS SA on PFL.subaccountId=SA.subaccountId LEFT JOIN permission_fingerprints AS PFR ON PFL.permissions_fingerprint=PFR.permissions_fingerprint AND PFL.accountId=PFR.accountId AND COALESCE(PFL.subaccountId, 0)=COALESCE(PFR.subaccountId, 0) WHERE PFL.roleId != PFR.roleId ; ",
"parameters": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
}
}
},
"to": {
"dataset": {
"field": {
"name": "recipe_slug",
"kind": "string",
"description": "Place where tables will be written in BigQuery."
}
},
"view": "Barnacle_Roles_Duplicates"
}
}
}
]Run This Workflow In Minutes On Google Cloud
Everything from a quick Google Cloud UI to reference developer code for your team in one GitHub repository.