We have a similar requirement on our instance. The approach we follow is as below:
- Use a script in conjunction with WS Proxy to create a query activity in all Business Units
- Create a shared DE(or a single DE in each BU - with same name and external key), that will store the result of this query activity.
Below is a sample script, that generates a query activity which queries the sent DV for each market and stores in a DE. The downside of this approach is that you cannot debug a lot, since an automation is not created, just a query activity which runs independently.
<script language=javascript runat=server>
Platform.Load("core", "1.1.1");
// Configurations START
var debug = true;
var TARGET_DE_NAME = "ypur target DE";
var TARGET_DE_CUSTOMER_KEY = "yur target DE etxernal key";
function getControlGroupsQuery(marketCode) {
//WRite the query here
return "select * from _Sent where eventdate >= FORMAT(DATEADD(dd,-1,getdate()),'yyyy-MM-dd 00:00:00')";
}
// Configurations END
var FOLDER_NAME = "Generated";
var BU = [];
//All your MIDs fall here
//BU = [ { marketCode: 'FI', mid: 123, marketName: 'Finland' }, { marketCode: 'BE', mid: 4657, marketName: 'Belgium' } ];
var prox = new Script.Util.WSProxy();
function getQueryName(queryName){
return "Generated_" + queryName;// Dynamic queries can be named like this || guid;
}
function createQuery(queryName, queryText, categoryID){
var guid = Platform.Function.GUID();
var name = getQueryName(queryName);
// var options = {SaveOptions: [{'PropertyName': '*', SaveAction: 'UpdateAdd'}]};
var query = {
Name: name
, CustomerKey: guid
, Description: "Generated queries for analysis"
, QueryText: queryText
, TargetType: "DE"
, DataExtensionTarget: {
Name: TARGET_DE_NAME
, CustomerKey: TARGET_DE_CUSTOMER_KEY
}
, CategoryID: categoryID
, TargetUpdateType: "Overwrite" //THe query overwrites
}
dump(query, "Query: ");
try{
var res = prox.createItem("QueryDefinition", query);
}
catch(e)
{
Write(Stringify(e));
}
return res;
Write(Stringify(res));
}
function updateQuery(queryName, queryText, categoryID){
var cols = ["ObjectID", "Name", "CustomerKey", "Description", "QueryText", "TargetType", "DataExtensionTarget.Name", "DataExtensionTarget.CustomerKey", "CategoryID", "TargetUpdateType"];
var filter = {
Property: "Name"
, SimpleOperator: "equals"
, Value: getQueryName(queryName)
};
var opts = null;
var props = null;
if(categoryID){
filter = {
LeftOperand: {
Property: "CategoryID"
, SimpleOperator: "equals"
, Value: categoryID
}
, LogicalOperator:"AND"
, RightOperand: filter
};
}
filter = {
LeftOperand: {
Property: "Status"
, SimpleOperator: "equals"
, Value: "Active"
}
, LogicalOperator:"AND"
, RightOperand: filter
};
try{
var queryRetrieve = prox.retrieve("QueryDefinition", cols, filter, opts, props) ;
if(queryRetrieve.Status == "OK" && queryRetrieve.Results.length > 0){
var query = queryRetrieve.Results[0];
}
if(query){
query = {
ObjectID: query.ObjectID
, Name: query.Name
, CustomerKey: query.CustomerKey
, Description: query.Description
, QueryText: queryText
, TargetType: "DE"
, DataExtensionTarget: {
Name: TARGET_DE_NAME
, CustomerKey: TARGET_DE_CUSTOMER_KEY
}
, CategoryID: categoryID
, TargetUpdateType: query.TargetUpdateType
}
var res = prox.updateItem("QueryDefinition", query);
dump(res,"Update Query: ");
return res;
}
} catch(ex){
dump(ex, "Exception: ");
}
}
function createOrUpdateQuery(queryName, queryText, categoryID){
var res = updateQuery(queryName, queryText, categoryID);
if(res.Status == "OK" && res.Results.length > 0){
return res.Results[0].Object.ObjectID;
} else {
res = createQuery(queryName, queryText, categoryID);
if(res.Results && res.Results[0]){
var objectID = res.Results[0].Object.ObjectID;
var statusMessage = res.Results[0].StatusMessage;
var statusCode = res.Results[0].StatusCode;
if(statusCode == "OK" && objectID){
return objectID;
}
dump(result, "Result: ");
};
}
}
function performQuery(objectID){
var action = "Start";
var props = { ObjectID: objectID };
var opts = {};
var res = prox.performItem("QueryDefinition", props, action, opts);
Write("Test");
dump(res, "Performing! ");
}
function createAndPerform(queryName, queryText, categoryID){
var objectID = createOrUpdateQuery(queryName, queryText, categoryID);
if(objectID){
performQuery(objectID);
}
}
function dump(variable, label) {
if(debug){
var label = (label == undefined) ? '' : '<b>' + label + ':</b><br>';
Write("<br><pre>" + label + Stringify(variable) + "</pre><br>");
}
}
function runQueriesOnBU(bu){
prox.setClientId({ "ID": bu.mid });
dump(bu, "BU: ");
// Generated CategoryID
var categoryID = getFolderID(FOLDER_NAME, "queryactivity");
dump(categoryID, "CategoryID: ")
if(categoryID){
createAndPerform("YOUR_QUERY_NAME", getControlGroupsQuery(bu.marketCode), categoryID); //Enter your query name here
}
prox.resetClientIds();
}
//Execution begins
for(var b in BU){
runQueriesOnBU(BU[b]);
}
function createFolderID(folderName, contentType){
dump(folderName, "Creating: ");
var complexfilter = {
LeftOperand:{
Property: "ContentType"
, SimpleOperator: "equals"
, Value: contentType
}
, LogicalOperator:"AND"
, RightOperand:{
Property: "Name"
, SimpleOperator: "equals"
, Value: "Query"
}
};
var myQueriesFolder = prox.retrieve("DataFolder", ["Name", "ID", "ContentType", "ParentFolder.ID"], complexfilter);
var myParentFolder = myQueriesFolder.Results[0].ID;
var guid = Platform.Function.GUID();
var newFolder = {
"Name" : folderName,
"CustomerKey" : guid,
"Description" : folderName + " auto generated folder",
"ContentType" : contentType,
"IsActive" : "true",
"IsEditable" : "true",
"AllowChildren" : "false",
"ParentFolder" : {
ID: myParentFolder
}
};
try{
var res = prox.createItem("DataFolder", newFolder);
if(res.Status == "OK"){
return res.Results[0].NewID;
}
} catch (ex){
Write("Error: " + Stringify(ex));
}
}
function getFolderID(folderName, contentType) {
var cols = ["Name", "ID", "ContentType"];
var complexfilter = {
LeftOperand:{
Property: "ContentType"
, SimpleOperator: "equals"
, Value: contentType
}
, LogicalOperator:"AND"
, RightOperand:{
Property: "Name"
, SimpleOperator: "equals"
, Value: folderName
}
};
var data = prox.retrieve("DataFolder", cols, complexfilter);
if(data.Results.length === 1){
return data.Results[0].ID;
} else {
return createFolderID(folderName, contentType);
}
}
</script>