Retrieve a Query Activity verified

Learn how to retrieve a Query Activity in Salesforce Marketing Cloud (SFMC) with SSJS (server-side JavaScript). Code snippets include Core, WSProxy and REST API methods.

Core

var customerKey = "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

var filter = {
    Property: "CustomerKey",
    SimpleOperator: "equals",
    Value: customerKey
}

var result = QueryDefinition.Retrieve(filter);
<script runat="server">

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

	try {

        var customerKey = "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

        var filter = {
            Property: "CustomerKey",
            SimpleOperator: "equals",
            Value: customerKey
        }

        var result = QueryDefinition.Retrieve(filter);

        Write(Stringify(result));
		
	} catch(error) {

        Write(Stringify(error));
        
    }	

</script>
[
    {
        "Name": "MyNewQuery",
        "DataExtensionTarget": {
            "Name": "MyDataExtension",
            "Description": "",
            "Keyword": null,
            "Client": null,
            "PartnerKey": null,
            "PartnerProperties": null,
            "CreatedDate": "0001-01-01T00:00:00.000",
            "CreatedDateSpecified": false,
            "ModifiedDate": null,
            "ModifiedDateSpecified": false,
            "ID": 0,
            "IDSpecified": false,
            "ObjectID": null,
            "CustomerKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
            "Owner": null,
            "CorrelationID": null,
            "ObjectState": null,
            "IsPlatformObject": false,
            "IsPlatformObjectSpecified": false
        },
        "ObjectID": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
        "QueryText": "SELECT TOP 50 SubscriberKey, EmailAddress FROM _Subscribers",
        "TargetType": "DE",
        "TargetUpdateType": "Overwrite",
        "FileSpec": "",
        "FileType": "",
        "Status": "Active",
        "CategoryID": 0,
        "CategoryIDSpecified": false,
        "InteractionObjectID": null,
        "Description": "My updated query",
        "Keyword": null,
        "Client": null,
        "PartnerKey": null,
        "PartnerProperties": null,
        "CreatedDate": "2022-12-07T13:05:21.770",
        "CreatedDateSpecified": true,
        "ModifiedDate": "2022-12-08T10:04:58.837",
        "ModifiedDateSpecified": true,
        "ID": 0,
        "IDSpecified": false,
        "CustomerKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
        "Owner": null,
        "CorrelationID": null,
        "ObjectState": null,
        "IsPlatformObject": false,
        "IsPlatformObjectSpecified": false
    }
]

WSProxy

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

var name = "MyNewQuery";

var cols = [
    "CategoryID",
    "CreatedDate",
    "CustomerKey",
    "Description",
    "DataExtensionTarget.Name",
    "DataExtensionTarget.CustomerKey",
    "FileSpec",
    "FileType",
    "ModifiedDate",
    "Name",
    "ObjectID",
    "QueryText",
    "Status",
    "TargetType",
    "TargetUpdateType"
];

var filter = {
    Property: "Name", 
    SimpleOperator: "equals",
    Value: name 
};

var result = api.retrieve("QueryDefinition", cols, filter);
<script runat="server">

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

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

    try {

        var name = "MyNewQuery";

        var cols = [
            "CategoryID",
            "CreatedDate",
            "CustomerKey",
            "Description",
            "DataExtensionTarget.Name",
            "DataExtensionTarget.CustomerKey",
            "FileSpec",
            "FileType",
            "ModifiedDate",
            "Name",
            "ObjectID",
            "QueryText",
            "Status",
            "TargetType",
            "TargetUpdateType"
        ];

        var filter = {
            Property: "Name", 
            SimpleOperator: "equals",
            Value: name 
        };

        var result = api.retrieve("QueryDefinition", cols, filter);

        Write(Stringify(result));

    } catch (error) {

        Write(Stringify(error));

    }

</script>
{
    "Status": "OK",
    "RequestID": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "Results": [
        {
            "Name": "MyNewQuery",
            "ObjectID": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
            "QueryText": "SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers",
            "TargetType": "DE",
            "DataExtensionTarget": {
                "Name": "MyDataExtension",
                "Description": null,
                "Keyword": null,
                "Client": null,
                "PartnerKey": null,
                "PartnerProperties": null,
                "CreatedDate": "0001-01-01T00:00:00.000",
                "ModifiedDate": null,
                "ID": 0,
                "ObjectID": null,
                "CustomerKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
                "Owner": null,
                "CorrelationID": null,
                "ObjectState": null,
                "IsPlatformObject": false
            },
            "TargetUpdateType": "Overwrite",
            "FileSpec": "",
            "FileType": "",
            "Status": "Active",
            "CategoryID": 12345,
            "InteractionObjectID": null,
            "Description": "My updated queery",
            "Keyword": null,
            "Client": null,
            "PartnerKey": null,
            "PartnerProperties": null,
            "CreatedDate": "2022-12-07T13:05:21.769",
            "ModifiedDate": "2022-12-07T13:51:13.500",
            "ID": 0,
            "CustomerKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
            "Owner": null,
            "CorrelationID": null,
            "ObjectState": null,
            "IsPlatformObject": false
        }
    ],
    "HasMoreRows": false
}

REST API

var queryDefinitionId =  "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

var endpoint = restInstanceUrl + "automation/v1/queries/" + queryDefinitionId;

var request = new Script.Util.HttpRequest(endpoint);
    request.emptyContentHandling = 0;
    request.retries = 2;
    request.continueOnError = true;
    request.setHeader("Authorization", "Bearer " + accessToken);
    request.method = "GET";
    request.contentType = "application/json";
    request.encoding = "UTF-8";

var results = request.send();

var result = Platform.Function.ParseJSON(String(results.content));
<script runat="server">

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

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

    var restInstanceUrl = "https://YOUR_SUBDOMAIN.rest.marketingcloudapis.com/",
        accessToken     = "YOUR_REST_API_TOKEN";
	
	try {

        var queryDefinitionId =  "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

        var endpoint = restInstanceUrl + "automation/v1/queries/" + queryDefinitionId;

        var request = new Script.Util.HttpRequest(endpoint);
            request.emptyContentHandling = 0;
            request.retries = 2;
            request.continueOnError = true;
            request.setHeader("Authorization", "Bearer " + accessToken);
            request.method = "GET";
            request.contentType = "application/json";
            request.encoding = "UTF-8";

        var results = request.send();

        var result = Platform.Function.ParseJSON(String(results.content));

        Write(Stringify(result));
		
	} catch(error) {
        Write(Stringify(error));
    }	

</script>
{
    "queryDefinitionId": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "name": "MyNewQuery",
    "key": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "description": "My updated queery",
    "queryText": "SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers",
    "targetName": "MyDataExtension",
    "targetKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "targetId": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "targetDescription": "",
    "createdDate": "2022-12-07T13:05:21.77",
    "modifiedDate": "2022-12-07T13:51:13.5",
    "targetUpdateTypeId": 0,
    "targetUpdateTypeName": "Overwrite",
    "validatedQueryText": "SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\n\r\nINSERT INTO C100000000.[MyDataExtension] ([SubscriberKey], [EmailAddress])\r\nSELECT querydef.[SubscriberKey], querydef.[EmailAddress]\r\nFROM (SELECT TOP 5 SubscriberKey, EmailAddress FROM C100000000._Subscribers) AS querydef \r\nSELECT @rcInsert = @@ROWCOUNT;;\r\n",
    "categoryId": 12345,
    "isFrozen": false
}

TIP

queryDefinitionId can be found in the URL when accessing the Query Activity.

Reference

Ressources and references related to the current methods.

SOAP object

Help me turn coffee into code

This website is provided to you free of charge. However, a lot of time and effort are spent to write, test and mainain the code. Please consider supporting my work by buying me a cup of coffee.

Last Updated: