Identify Queries & Automations for Your Data Extensions

Unlock the Power of Marketing Automation: Identify Queries & Automations for Your Data Extensions

Do you struggle to track down which queries and automations are modifying your crucial data extensions? Our code snippet simplifies this process for you! By providing the name of your target data extension, you can swiftly identify the associated queries and automations.

<script runat="server">

  Platform.Load("Core", "1");

  //=============== CONFIG ===================

  var targetDataExtensionName = "YourDataExtensionName"; // Name of the target Data Extension

  var authEndpoint = 'https://{Subdomain}.auth.marketingcloudapis.com/'; // Authentication Base URI from Marketing Cloud

  var clientId = "XXXXXXXX"; // Your Client Id from Marketing Cloud

  var clientSecret = "XXXXXXXX"; // Your Client Secret from Marketing Cloud

  //============== END CONFIG =================

  try {

    var proxy = new Script.Util.WSProxy();

    // Requesting access token

    var accessTokenRequest = HTTP.Post(authEndpoint + 'v2/token', 'application/json', Stringify({

      client_id: clientId,

      client_secret: clientSecret,

      grant_type: "client_credentials"

    }));

    if (accessTokenRequest.StatusCode == 200) {

      var tokenResponse = Platform.Function.ParseJSON(accessTokenRequest.Response[0]);

      var accessToken = tokenResponse.access_token;

      var restUrl = tokenResponse.rest_instance_url;

      // Retrieving query definitions related to the target Data Extension

      var targetQueryDefinitions = proxy.retrieve("QueryDefinition", ["Name", "ObjectID", "DataExtensionTarget.Name"], {

        Property: "DataExtensionTarget.Name",

        SimpleOperator: "equals",

        Value: targetDataExtensionName

      });

      document.write("<h2>Related Queries & Automations for " + targetDataExtensionName + " Data Extension</h2>");

      document.write("<ul>");

      for (var q = 0; q < targetQueryDefinitions.Results.length; q++) {

        var relatedActivity = proxy.retrieve("Activity", ["Name", "Definition.ObjectID", "ObjectID", "Program.ObjectID"], {

          Property: "Name",

          SimpleOperator: "Equals",

          Value: targetQueryDefinitions.Results[q].Name

        });

        for (var a = 0; a < relatedActivity.Results.length; a++) {

          var content = Platform.Function.HTTPGet(restUrl + 'automation/v1/automations/' + relatedActivity.Results[a].Program.ObjectID, false, 0, ['Authorization'], ['Bearer ' + accessToken], status);

          var json = Platform.Function.ParseJSON(content);

          for (var s = 0; s < json.steps.length; s++) {

            for (var ac = 0; ac < json.steps[s].activities.length; ac++) {

              if (json.steps[s].activities[ac].activityObjectId == targetQueryDefinitions.Results[q].ObjectID) {

                document.write("<li>The SQL Query <b>" + json.steps[s].activities[ac].name + "</b> is used in step " + json.steps[s].step + " of Automation <b>" + json.name + "</b></li>");

              }

            }

          }

        }

      }

      document.write("</ul>");

    }

  } catch (error) {

    document.write("<p>Error Message: " + error + "</p>");

  }

</script>