0

I'm trying to display the results in the form instead of storing the details in data extension which makes it accessible for everyone with the cloudpage URL. Here the search is not happening as expected, please suggest changes. Thanks in advance.

<!DOCTYPE html>
<html>
<head>
<title>Search Query Activities by Data Extension</title>
</head>
<body>
<h1>Search Query Activities by Data Extension</h1>
<form method="post">
<label for="searchTerm">Enter Data Extension Name:</label>
<input type="text" name="searchTerm" id="searchTerm">
<input type="submit" value="Search">
</form>
<br>
<div id="resultsDiv"></div>

<script runat="server">
Platform.Load("core","1");

var searchTerm = Request.GetFormField("searchTerm");
var matchingActivities = []; // An array to hold the matching SQL query activities

if (searchTerm) {
var queryActivities = DataExtension.Init("QueryDefinition").Rows.Retrieve();

for (var i=0; i<queryActivities.length; i++) {
var queryText = queryActivities[i].QueryText;
if (queryText.indexOf(searchTerm) > -1) {
matchingActivities.push(queryActivities[i]);
}
}
}

if (matchingActivities.length > 0) {
Platform.Response.Write("<h2>Matching Query Activities:</h2>");
Platform.Response.Write("<ul>");
for (var j=0; j<matchingActivities.length; j++) {
Platform.Response.Write("<li>" + matchingActivities[j].Name + "</li>");
}
Platform.Response.Write("</ul>");
}
else if (searchTerm) {
Platform.Response.Write("<p>No matching query activities found.</p>");
}
</script>
</body>
</html>
Improve this question
2
  • 3
    Here the search is not happening as expected - can you be more specific? Is the script throwing error 500? Is it creating a wormhole?
    – Lukas Lunow
    Commented May 9, 2023 at 14:49
  • @Lukas Lunow I’m not getting 500 error or any error but when I submit the form with target data extension name which should yield some result but returning else part with “no matching queries found”
    – vis_1019
    Commented May 10, 2023 at 22:21

1 Answer 1

Reset to default
1
  1. You mention that you do not want the cloudpage to be accessible. Your page is insecure even if you enter a search function.

Suggestion - secure it: https://sf-marketing.com/marketing-cloud-user-validation-on-cloudpages/

  1. you mention you do not want to store data in a data extension but retrieve the queries directly. However, you actually construct your dataset by retrieving rows from a data extension called "QueryDefinition":
var queryActivities = DataExtension.Init("QueryDefinition").Rows.Retrieve();

Assumption: you want to query the API for QueryDefinitions, not search a DE. https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/querydefinition.html

This error can be isolated by three debug messages placed before

if (matchingActivities.length > 0) {

..namely:

Platform.Response.Write("matchingA:" + Stringify(matchingActivities) + "<br>");
Platform.Response.Write("searchTerm:" + searchTerm  + "<br>");
Platform.Response.Write("queryText:" + searchTerm  + "<br>");
 Platform.Response.Write("queryActivities:" + Stringify(queryActivities) + "<br>");

which shows that already your first array queryActivities is empty.

  1. here is your solution, but actually retrieving queryDefinitions using WSProxy, not trying to search a DE called "QueryDefinition" that probably does not exist in the first place.

limitations:

  • This is still insecure
  • the search is case sensitive
  • it searches querytexts as a whole, so might bring up false positives, not just DE names. E.g if you search "subscriberKey" you'll find a lot of queries.
  • I tested it once.

<!DOCTYPE html>
<html>
<head>
<title>Search Query Activities by Data Extension</title>
</head>
<body>
<h1>Search Query Activities by Data Extension</h1>
<form method="post">
<label for="searchTerm">Enter Data Extension Name:</label>
<input type="text" name="searchTerm" id="searchTerm">
<input type="submit" value="Search">
</form>
<br>
<div id="resultsDiv"></div>

<script runat="server">
Platform.Load("core","1");
var api = new Script.Util.WSProxy();

var searchTerm = Request.GetFormField("searchTerm");
var matchingActivities = []; // An array to hold the matching SQL query activities

var queryActivities = {};
if (searchTerm) {

    var cols = [
        "Name"
        ,"QueryText"
    ];

    var filter = {
        Property: "QueryText", 
        SimpleOperator: "like",
        Value: searchTerm 
    };

    request = api.retrieve("QueryDefinition", cols, filter);
//var queryActivities = DataExtension.Init("QueryDefinition").Rows.Retrieve();
   // Write(Stringify(request));
    
    var results = request.Results
    //Write("<br> results: " + Stringify(results));
    
    for (var i=0; i<results.length; i++) {
        Write(i)
        var queryText = results[i].QueryText
        var queryName = results[i].Name
        Write("queryName" + queryName)
        if (queryText.indexOf(searchTerm) > -1) {
            matchingActivities.push(queryName);
       }
    }
    
}
Platform.Response.Write("matchingA: " + Stringify(matchingActivities) + "<br>");
Platform.Response.Write("searchTerm: " + searchTerm  + "<br>");
Platform.Response.Write("queryText: " + searchTerm  + "<br>");
Platform.Response.Write("queryActivities: " + Stringify(queryActivities) + "<br>");
  
if (matchingActivities.length > 0) {
    Platform.Response.Write("<h2>Matching Query Activities:</h2>");
    Platform.Response.Write("<ul>");
    
    for (var j=0; j<matchingActivities.length; j++) {
        Platform.Response.Write("<li>" + matchingActivities[j] + "</li>");
    }
    Platform.Response.Write("</ul>");
    }
else if (searchTerm) {
Platform.Response.Write("<p>No matching query activities found.</p>");
}
</script>
</body>
</html>

reference: https://www.ssjsdocs.xyz/automation-studio/activities/queries/retrieve/

Improve this answer
8
  • Great! Thank you Jonas Laberty. As it searches querytexts as a whole, can we just limit it for identifying target Data Extensions and respective activity name similar to [Data Extension Inventory]:(sfmarketing.cloud/2020/05/20/…) (gortonington.com/sfmc-data-extension-inventory-take-2).
    – vis_1019
    Commented May 13, 2023 at 7:35
  • if you are looking for FROM which DE it pulls, then you need to look at the querytext. If you are looking at which DEs it writes into, in fact you have to look at dataExtensionTarget and can also use exact matching, as it is a genuine field and not free text.
    – Jonas Lamberty
    Commented May 15, 2023 at 7:10
  • Thank you Jonas! This really helped a lot.
    – vis_1019
    Commented May 16, 2023 at 12:04
  • A follow up question: Is it possible to pull the folder path of searching Data Extension within the same solution. In the sense can we combine the solution for searching the list of query activities using DE name and display the folder path of specified DE within same script. I am new to SSJS and could use some help. Thanks in advance.
    – vis_1019
    Commented Jun 28, 2023 at 16:53
  • recommend to open a follow up question. I am probably the only person on stackexchange reading it here, vs. the whole community will see a new question ;)
    – Jonas Lamberty
    Commented Jun 28, 2023 at 17:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .