- 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/
- 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.
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.
- here is your solution, but actually retrieving queryDefinitions using WSProxy, not trying to search a DE that probably does not exists.
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>
