text: #standardSQL
    -- Copyright 2017 The Nomulus Authors. All Rights Reserved.
    --
    -- Licensed under the Apache License, Version 2.0 (the "License");
    -- you may not use this file except in compliance with the License.
    -- You may obtain a copy of the License at
    --
    --     http://www.apache.org/licenses/LICENSE-2.0
    --
    -- Unless required by applicable law or agreed to in writing, software
    -- distributed under the License is distributed on an "AS IS" BASIS,
    -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    -- See the License for the specific language governing permissions and
    -- limitations under the License.
  
    -- Determine the number of attempted adds each registrar made.
  
    -- Since the specification requests all 'attempted' adds, we regex the
    -- monthly App Engine logs, searching for all create commands and associating
    -- them with their corresponding registrars.
  
    -- Example log generated by FlowReporter in App Engine logs:
    --google.registry.flows.FlowReporter
    -- recordToLogs: FLOW-LOG-SIGNATURE-METADATA:
    --{"serverTrid":"oNwL2J2eRya7bh7c9oHIzg==-2360a","clientId":"ipmirror"
    -- ,"commandType":"hello", "resourceType":"","flowClassName":"HelloFlow"
    -- ,"targetId":"","targetIds":[],"tld":"",
    -- "tlds":[],"icannActivityReportField":""}
  
  -- This outer select just converts the registrar's clientId to their name.
  SELECT
    tld,
    registrar_table.registrarName AS registrar_name,
    'ATTEMPTED_ADDS' AS metricName,
    count AS metricValue
  FROM (
    SELECT
      JSON_EXTRACT_SCALAR(json, '$.tld') AS tld,
      JSON_EXTRACT_SCALAR(json, '$.clientId') AS clientId,
      COUNT(json) AS count
    FROM (
        -- Extract JSON metadata package from monthly logs
      SELECT
        REGEXP_EXTRACT(logMessages, r'FLOW-LOG-SIGNATURE-METADATA: (.*)\n?$')
            AS json
      FROM (
        SELECT
          protoPayload.resource AS requestPath,
          ARRAY(
            SELECT logMessage
            FROM UNNEST(protoPayload.line)) AS logMessage
        FROM
          `%PROJECT_ID%.%APPENGINE_LOGS_DATA_SET%.%REQUEST_TABLE%*`
        WHERE _TABLE_SUFFIX
        BETWEEN '%FIRST_DAY_OF_MONTH%'
        AND '%LAST_DAY_OF_MONTH%')
      JOIN UNNEST(logMessage) AS logMessages
      -- Look for metadata logs from epp and registrar console requests
      WHERE requestPath IN ('/_dr/epp', '/_dr/epptool', '/registrar-xhr')
      AND STARTS_WITH(logMessages, "%METADATA_LOG_PREFIX%")
      -- Look for domain creates
      AND REGEXP_CONTAINS(
         logMessages, r'"commandType":"create","resourceType":"domain"')
      -- Filter prober data
      AND NOT REGEXP_CONTAINS(
          logMessages, r'"prober-[a-z]{2}-((any)|(canary))"') )
    GROUP BY tld, clientId ) AS logs_table
  JOIN
    `%PROJECT_ID%.%DATASTORE_EXPORT_DATA_SET%.%REGISTRAR_TABLE%`
        AS registrar_table
  ON logs_table.clientId = registrar_table.__key__.name
  ORDER BY tld, registrar_name
  
  