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.

CM360 User Audit Sample Screenshot


lock_openGet Access directions_bikeSample listGit Hub sourcePython menu_bookColab airAirflow thumb_upTest



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 (2 years, 8 months)
Authors kenjora@google.com
ceh@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.

Deployment Stepslaunch Developer Guidebuild UI How Tolaptop