Retrieve all Query Activities verified

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

WSProxy

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

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

var filter = {
    Property: "CustomerKey",
    SimpleOperator: "isNotNull",
    Value: " "
};

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

<script runat="server">

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

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

    try {

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

        var filter = {
            Property: "CustomerKey",
            SimpleOperator: "isNotNull",
            Value: " "
        };

        var opts = {
            BatchSize: 300
        };

        var props = {
            QueryAllAccounts: false
        };

        var result = [],
            moreData = true,
            reqID = data = null;

        while(moreData) {

            moreData = false;

            if(reqID) props.ContinueRequest = reqID;

            var req = api.retrieve("QueryDefinition", cols, filter, opts, props);

            if (req) {

                moreData = req.HasMoreRows;
                reqID = req.RequestID;

                var results = req.Results;

                for (var k in results) {
                    result.push(results[k]);
                }

            }
        }

        Write(Stringify(result));

    } catch (error) {

        Write(Stringify(error));

    }

</script>

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

REST API

Retrieve all Queries

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

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 endpoint = restInstanceUrl + "automation/v1/queries/";

        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>

{
    "count": 1,
    "page": 1,
    "pageSize": 25,
    "items": [
        {
            "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
        }
    ]
}

Retrieve all Queries from a Category (folder)

var categoryId =  12345;

var endpoint = restInstanceUrl + "automation/v1/queries/category/" + categoryId;

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 categoryId =  12345;

        var endpoint = restInstanceUrl + "automation/v1/queries/category/" + categoryId;

        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>

{
    "count": 1,
    "page": 1,
    "pageSize": 25,
    "items": [
        {
            "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
        }
    ]
}

Reference

Ressources and references related to the current methods.

SOAP object