API / Data Extensions / SFMC / SSJS / WSProxy December 6, 2019

I have created a more modernized version of this in a different article. I left this article here as a reference should anyone want to take other aspects of it that may not be in the new version. I would highly recommend using the new version I created instead of this one though.

A common request I hear is the need for a quick reference to the data extensions inside of an account and details pertaining to them. It can be a pain to get this information in bulk and then display it.

I wanted to share a solution I created that allows you to store most of the relevant information on this inside of another Salesforce Marketing Cloud Data Extension for easy reference – whether via API, AMPscript, SSJS, or SDK.

First step of the solution is to create a data extension to house this information. For this example I am going to name it ‘DE_Inventory’. See below for my fields:

This data extension will hold all of your data extensions inside of it along with the relevant information. Keep the field names and DE name handy as we will use them often in the next steps.

You will notice I added a field named ‘LU’. This is so that if we want to do a lookup to this Data Extension (via AMPscript or SSJS), we can use this field with a value of ‘1’ to gather all the rows via the lookup.

The next step would be to create a Script Activity that will be utilizing Server-Side JavaScript. Inside this activity we will have 5 functions to use to gather the final upsert to the DE.

Function 1 (generateToken)

This first function is going to be used to gather an authorization token for the future REST API calls we will be doing. It is pretty straight-forward and can utilize both v1 (legacy) and current v2 packages.

Before I go into the function, I am going to share dependencies required for this function to work. You will need:

  1. MID
  2. clientId
  3. clientSecret
  4. Authorization url
  5. The subdomain for your REST calls
  6. the ‘version’ number (1 for legacy, 2 for current)

See below for sample declaration of this information:

/* Sample ENV */
var mid = 123456

// Info for REST API
var clientId = 'myClientId'
var clientSecret = 'myClientSecret'
var authURL = 'https://mySubDomain.auth.marketingcloudapis.com/'
var subDomain = 'https://mySubDomain.rest.marketingcloudapis.com'
var version = 2

Once you have this information, you will then be able to utilize the function to generate your oAuth token for future REST calls. See below for the generateToken function:

function generateToken(clientId, clientSecret, mid, authURL, version) {
  
  if (version == 2) {
    var versionEndpoint = '/v2/token'

    var authJSON = {
  "grant_type": "client_credentials",
  "client_id": clientId,
  "client_secret": clientSecret,
  "account_id": mid
  }
  } else {
    var versionEndpoint = '/v1/requestToken'

      var authJSON = {
    "clientId": clientId,
    "clientSecret": clientSecret
  }
  }

  var authUrl = authURL + versionEndpoint;
  var contentType = 'application/json';
  var authPayload = Platform.Function.Stringify(authJSON);

  var accessTokenResult = HTTP.Post(authUrl, contentType, authPayload);

  var statusCode = accessTokenResult["StatusCode"];
  var response = accessTokenResult["Response"][0];

  if(version == 2) {
      var accessToken = Platform.Function.ParseJSON(response).access_token;
  } else {
      var accessToken = Platform.Function.ParseJSON(response).accessToken;
  }

  return accessToken;
}

Do note that this will return just the token, you will need to add on ‘Bearer ‘ to the front of it to utilize in your API calls. You can do this with something like: 

var oAuth = 'Bearer ' + generateToken(clientId,clientSecret,mid,authURL,version)

For this use case, I added the above into Function 4 (getDERowCount) as that is the only one that utilizes the token, but you can set it universally outside of the function if you want.

Function 2 (getAllDeInfo)

This will return the majority of the information you need to collect for the data extension inventory – including name, customerkey, sendable information, retention policy, etc.

If you did not need items like the DEfields or rowcounts, you could stop at function 2 and be done. You technically would not even need to utilize Function 1 as this is all done via WSProxy and does not require an oAuth token.

The only dependency for this function is the MID you wish to pull from, which should already be declared from the dependencies in Function 1. See below for the function code:

function getAllDeInfo(mid) {
  var prox = new Script.Util.WSProxy();

  prox.setClientId({ "ID": mid }); //Impersonates the BU

  var cols = ["Name","CustomerKey","CategoryID","Description","IsSendable","SendableDataExtensionField.Name","SendableSubscriberField.Name","DataRetentionPeriodLength","DataRetentionPeriodUnitOfMeasure","RowBasedRetention","ResetRetentionPeriodOnImport","DeleteAtEndOfRetentionPeriod","RetainUntil","DataRetentionPeriod"];
  var reqID = null
  var moreData = true;
  var desc = [];

  while(moreData) {
    moreData = false;
    var data = reqID == null ?
      prox.retrieve("DataExtension", cols, filter):
      prox.getNextBatch("DataExtension", reqID);

    if(data != null) {
      moreData = data.HasMoreRows;
      reqID = data.RequestID;
      if(data && data.Results) {
        desc.push(data);
      }
    }
  }

  return desc;
}

This will give a huge array of all of your data extensions – up to 2500 of them, which is the max per Retrieve call. If you need to gather more than this, you will need to implement a loop around this function to gather all the results. This may require multiple Script Activities to accomplish depending on the volume of DEs you have in the account to prevent timeouts.

After you retrieve this array, you will then create a for loop to iterate through it and get each individual DE’s information


Function 3 (getDEFields)

This function is going to be used to gather all the fields and corresponding information associated with the fields into a stringified array to reference in your Inventory DE. The only dependency for this function is the CustomerKey of the Data Extension you are retrieving from. This can be gathered inside the for loop you use on the getAllDEInfo function return.

You will then collect the data by making a call to the ‘DataExtensionField’ object using WSProxy. It will filter based off of the property ‘DataExtension.CustomerKey’ which will be the customerkey dependency mentioned above.

You will note that I have a section that deletes a ton of fields/properties from the returned array. This is because most of those fields are irrelevant or potentially problematic, but are always included in the response. By removing them, it reduces the size of the array, reduces potentials for errors and makes it more readable.

function getDEFields(customerKey) {
  var proxy = new Script.Util.WSProxy();
  var cols2 = ["CustomerKey","Name","FieldType","IsPrimaryKey","MaxLength","Ordinal","DefaultValue","IsRequired"];
  var filter2 = {
      Property: "DataExtension.CustomerKey",
      SimpleOperator: "equals",
      Value: customerKey
  };
  try {
    var deFields = proxy.retrieve("DataExtensionField", cols2, filter2);
    var fieldLength = deFields.Results.length

    var deFieldArray = deFields.Results

    var fieldArray = []

    for (var a = 0; a < deFieldArray.length; a++) {

      var fieldObj = deFields.Results[a]

      //Fields that need to be removed prior to creation of new DE
      delete fieldObj.AttributeMaps;
      delete fieldObj.CustomerKey;
      delete fieldObj.ObjectID;
      if (fieldObj.MaxLength == "" || fieldObj.MaxLength == 0) {
        delete fieldObj.MaxLength;
      }
      delete fieldObj.StorageType;
      delete fieldObj.DataExtension;
      delete fieldObj.DataType;
      delete fieldObj.IsCreatable;
      delete fieldObj.IsUpdatable;
      delete fieldObj.IsRetrievable;
      delete fieldObj.IsQueryable;
      delete fieldObj.IsFilterable;
      delete fieldObj.IsPartnerProperty;
      delete fieldObj.IsAccountProperty;
      delete fieldObj.PartnerMap;
      delete fieldObj.Markups;
      delete fieldObj.Precision;
      delete fieldObj.Scale;
      delete fieldObj.Label;
      if (fieldObj.MinLength == "" || fieldObj.MinLength == 0) {
        delete fieldObj.MinLength;
      }
      delete fieldObj.CreatedDate;
      delete fieldObj.ModifiedDate;
      delete fieldObj.ID;
      delete fieldObj.IsRestrictedPicklist;
      delete fieldObj.PicklistItems;
      delete fieldObj.IsSendTime;
      delete fieldObj.DisplayOrder;
      delete fieldObj.References;
      delete fieldObj.RelationshipName;
      delete fieldObj.Status;
      delete fieldObj.IsContextSpecific;
      delete fieldObj.Client;
      delete fieldObj.PartnerProperties;
 
      fieldArray.push(fieldObj); 
      fieldObj = '';
    }
    
    return fieldArray;

  } catch(e) {
    var fieldObj = 'Error';
    return fieldObj;
  }
  return fieldArray;
}


Function 4 (getDERowCount)

This will be where we utilize the REST API call. This call will be aimed at the undocumented REST endpoint for customobject data (data extension): data/v1/customobjectdata/key/{deKey}/rowset.

Basically the endpoint will return data rowsets from a data extension, which is not very unique honestly. Just this ability is not super helpful for the need to gather the count (or at least it would be a long and arduous process to count every returned dataset). BUT the unique thing about this endpoint is that one of the data points returned in the object is a rowcount of the DE! See below sample return of the API call:

{
    "links": {
        "self": "/v1/customobjectdata/token/XXXXXXXXXXXXXXXXXXXXX/rowset?$page=1",
        "next": "/v1/customobjectdata/token/XXXXXXXXXXXXXXXXXXXX/rowset?$page=2"
    },
    "requestToken": "XXXXXXXXXXXXXXXXXXXXXXXXXXX",
    "tokenExpireDateUtc": "2019-12-07T12:03:01.453",
    "customObjectId": "XXXXXXXXXXXXXXXXXXXXXXXXXXX",
    "customObjectKey": "DE_Inventory",
    "pageSize": 1,
    "page": 1,
    "count": 330,
    "items": [ /* Row Data */ ]
}

From here we would grab the ‘count’ property and we then have the rowcount of the data extension. To make this even more efficient, we can add ?$pageSize=1 to only return one row and greatly reduce the processing and time required to make the call.

For this call, we will have the following dependencies:

  1. Your BU specific endpoint (subdomain)
  2. the auth token you gathered from Function 1
  3. the customerkey of the targeted DE (gathered in for loop)

See below for the function:

function getDERowCount(subDomain,authToken,customerKey) {
  var deUrl = subDomain + '/data/v1/customobjectdata/key/' + customerKey + '/rowset?$pageSize=1';
  var oAuth = 'Bearer ' + authToken;

  try {
    var deResult = HTTP.Get(deUrl, ["Authorization"], [oAuth]);

    var rowCount = Platform.Function.ParseJSON(deResult.Content).count;

    return rowCount;
  } catch(e) {
    var rowCount = 'Error';
    
    return rowCount;
  }
  return rowCount;
}

Now there is an alternative if you want to mix in AMPscript by utilizing the DataExtensionRowCount() function instead, but that then introduces a need to switch between languages and requires this code to be stored in a separate Content Block and referenced in the Script Activity as they can only hold SSJS. It also limits this to only work on those DEs in the current BU.


Function 5 (clearDE)

The final function is actually a pretty simple one. It is designed to clear out the current DE_Inventory Data Extension prior to inserting the new records.

This is included to ensure that only those that currently exist are in the DE (kind of mimicing an Overwrite capability in SQL or Import). As we are doing an upsert of this information, if a DE is deleted and we don’t have the clear function, it would still be listed in your Inventory Data Extension. Which then reduces the accuracy of your Inventory.

The dependencies are fairly light, you will only need the MID and the customerkey of the data extension. Which can be set at the top with a simple var custKey = 'DE_Inventory'. The other thing of note, is to make sure to exclude this from being in the for loop (or it will remove all other records as your script is run) and to make sure it is above the upsert, to ensure nothing gets removed that should be included.

function clearDE(mid,custKey) {
  var prox = new Script.Util.WSProxy();

  prox.setClientId({ "ID": mid }); //Impersonates the BU

  var action = "ClearData";
  var props = {
          CustomerKey: custKey
  };
  var data = prox.performItem("DataExtension", props, action);

  return data;
}

The Full Monty

So now that we have all the functions and know how they work, we can put it all together and create the live script. In the below, I removed the actual script inside the functions (as we have it listed above) to help reduce the size of the script block.

<script runat=server>
Platform.Load("Core","1.1.1");

/* Sample ENV */
var mid = 123456

// Info for REST API
var clientId = 'myClientId'
var clientSecret = 'myClientSecret'
var authURL = 'https://mySubDomain.auth.marketingcloudapis.com/'
var subDomain = 'https://mySubDomain.rest.marketingcloudapis.com'
var version = 2

//CustomerKey for Inventory DE
var custKey = 'DE_Inventory'
//gather authentication token
var authToken = generateToken(clientId,clientSecret,mid,authURL,version);
//get bulk return of Data Extension in MID
var desc = getAllDeInfo(mid);

//verify a valid return
if (desc) {

  //clear existing data in Inventory DE
  var clear = clearDE(mid,custKey);

  //iterate through DE return for individual results
  for(var e=0; e < desc.length; e++) {

    var deData = desc[e];
    //iterate through each object to collect the properties of the DE
    for(var i=0; i < deData.Results.length; i++) {
      var name                = deData.Results[i].Name
      var customerKey         = deData.Results[i].CustomerKey
      var deDescription       = deData.Results[i].descDescription
      var catId               = deData.Results[i].CategoryID
      var deIsSendable        = deData.Results[i].IsSendable
      var sendableDEName      = deData.Results[i].SendableDataExtensionField.Name
      var sendableSubName     = deData.Results[i].SendableSubscriberField.Name
      var deRetentionLength   = deData.Results[i].DataRetentionPeriodLength
      var deRetentionUnit     = deData.Results[i].DataRetentionPeriodUnitOfMeasure
      var deRowRetention      = deData.Results[i].RowBasedRetention
      var deResetRetention    = deData.Results[i].ResetRetentionPeriodOnImport
      var deDeleteRetention   = deData.Results[i].DeleteAtEndOfRetentionPeriod
      var deRetainUntil       = deData.Results[i].RetainUntil
      var deRetentionPeriod   = deData.Results[i].DataRetentionPeriod

      //Call the function to return string of DE fields
      var fieldObj = getDEFields(customerKey);
      //Call the function to return DE rowcounts
      var rowCount = getDERowCount(subDomain,authToken,customerKey);
      //Upserts the corresponding info into the Inventory DE
      var upsertDeInfo = Platform.Function.UpsertData(custKey, ["Name", "CustomerKey"], [name, customerKey], ["RowCount","Fields","Description", "CategoryID", "IsSendable", "SendableDEField", "SendableSubField", "RetentionLength", "RetentionUnit", "RowRetention", "ResetRetention", "DeleteRetention", "RetainUntil", "RetentionPeriod"], [rowCount,Stringify(fieldObj),deDescription, catId, deIsSendable, sendableDEName, sendableSubName, deRetentionLength, deRetentionUnit, deRowRetention, deResetRetention, deDeleteRetention, deRetainUntil, deRetentionPeriod])

    }
  }
}

function getAllDeInfo(mid) {
}
function generateToken(clientId, clientSecret, mid, authURL, version) {
}
function getDERowCount(subDomain,authToken,customerKey) {
}
function getDEFields(customerKey) {
}
</script>

Now we have a data extension with a full list of all our data extensions and their relevant information! Super cool! But….

What do we do with it now?

A couple popular options are to use this as a reference point for an external dashboard (via API call to the DE) or as a daily email report or Cloudpage.

What I am going to present is a quick example of creating a simple Cloudpage for this. Now this can also be used inside an email as it is all based in AMPscript – but there may need some adjustment to certain aspects to allow for email specific development and styling.

I did limit the fields being pulled and displayed to make it easier to read, but you can easily add/remove fields inside the code to suit your needs.

%%[
/* CustomerKey of Inventory DE */
SET @DE_Inv = 'DE_Inventory'

/* Lookup to Inventory DE */
SET @DERows = LookupOrderedRows(@DE_Inv, DataExtensionRowCount(@DE_Inv), 'Name ASC', 'LU', 1)
/* Notice I used DataExtensionRowCount here to get around the 2500 limit on lookups*/

/* Rowcount of DE */
SET @DE_RowCount = RowCount(@DERows)

/* Build beginning and header row of Table */
OUTPUT(CONCAT('<table cellpadding="1" cellspacing="1" border="1"><tr>'))
OUTPUT(CONCAT('<th>Name</th>'))
OUTPUT(CONCAT('<th>CustomerKey</th>'))
OUTPUT(CONCAT('<th>RowCount</th>'))
OUTPUT(CONCAT('<th>CategoryID</th>'))
OUTPUT(CONCAT('<th>IsSendable</th>'))
OUTPUT(CONCAT('<th>SendableDEField</th>'))
OUTPUT(CONCAT('<th>SendableSubField</th>'))
OUTPUT(CONCAT('</tr>'))

/*Iterate through Rowset for individual Information */
FOR @i=1 TO @DE_RowCount DO

	SET @Row = Row(@DERows,@i)

	SET @Name 		= Field(@Row,'Name',1)
	SET @CustomerKey 	= Field(@Row,'CustomerKey',1)
	SET @Description 	= Field(@Row,'Description',1)
	SET @CategoryID 	= Field(@Row,'CategoryID',1)
	SET @IsSendable 	= Field(@Row,'IsSendable',1)
	SET @SendableDEField 	= Field(@Row,'SendableDEField',1)
	SET @SendableSubField 	= Field(@Row,'SendableSubField',1)
	SET @RetentionLength	= Field(@Row,'RetentionLength',1)
	SET @RetentionUnit	= Field(@Row,'RetentionUnit',1)
	SET @ResetRetention 	= Field(@Row,'ResetRetention',1)
	SET @DeleteRetention	= Field(@Row,'DeleteRetention',1)
	SET @RetainUntil	= Field(@Row,'RetainUntil',1)
	SET @RetentionPeriod	= Field(@Row,'RetentionPeriod',1)
	SET @Fields		= Field(@Row,'Fields',1)
	SET @RowRetention	= Field(@Row,'RowRetention',1)
	SET @RowCount 		= Field(@Row,'RowCount',1)

    /*Removes those with Rowcount of 'error' from being displayed*/
    if @RowCount != "Error" THEN

      /*Outputs the table cells for each data point*/
      OUTPUT(CONCAT('<tr>'))
      OUTPUT(CONCAT('<td>',@Name,'</td>'))
      OUTPUT(CONCAT('<td>',@CustomerKey,'</td>'))
      OUTPUT(CONCAT('<td>',@RowCount,'</td>'))
      OUTPUT(CONCAT('<td>',@CategoryID,'</td>'))
      OUTPUT(CONCAT('<td>',@IsSendable,'</td>'))
      OUTPUT(CONCAT('<td>',@SendableDEField,'</td>'))
      OUTPUT(CONCAT('<td>',@SendableSubField,'</td>'))
      OUTPUT(CONCAT('</tr>'))

    endif

NEXT @i

/* Closes Table*/
OUTPUT(CONCAT('</table>'))
]%%

Which (with a tiny bit of styling) should provide something like:

For reference, below is the styling I used:

<style>
  th {color: #FFFFFF; background-color: #0054A4; padding: 2px;}
  tr:nth-child(even) {background: #CCC}
  tr:nth-child(odd) {background: #FFF}
  td {padding: 2px;}
</style>
16 Comments
Tags: , , , , , , , , , ,
Subscribe
Notify of
guest
16 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Army
Army
4 years ago

Hi, I don’t understand why you are calling these functions at the end when you gather everything

function getAllDeInfo(mid) {
}
function generateToken(clientId, clientSecret, mid, authURL, version) {
}
function getDERowCount(subDomain,authToken,customerKey) {
}
function getDEFields(customerKey) {
}

0
Reply
Gortonington
Gortonington
Author
Reply to  Army
4 years ago

I put the functions at the end so they are all together and outside the actual execution part of the code block. These functions are references that the above code calls in and executes. Essentially allowing for easily referenced reusable code.

0
Reply
James
James
3 years ago

This DE Inventory is great! I have a question though and am dealing with a large number of data extensions, can you clarify “If you need to gather more than this, you will need to implement a loop around this function to gather all the results.” I’ve created a For Loop around the function to no avail. was wondering if you could explain a bit more and or provide an example when you had a moment. Thanks in advance.

0
Reply
Gortonington
Gortonington
Author
Reply to  James
3 years ago

You can see an example of the scripting needed on the WSProxy here: https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/ssjs_WSProxy_advanced_retrieve.htm

0
Reply
Marco Moscatelli
Marco Moscatelli
3 years ago

Hi, i’ve this error:
Error Message: “Object expected: log””Jint.Native.JsException: Object expected: log\r\nException of type ‘Jint.Native.JsException’ was thrown. – from Jint\r\n\r\n”

2
Reply
NTS
NTS
3 years ago

Hi,

This is really useful. In the section where you delete field attributes I am trying to delete these ones but it seems they are not deleted. Do you know why?
MinValue;
MaxValue;
IsViewable;
IsEditable;
PartnerKey;
Owner;
CorrelationID;
ObjectState;
IsPlatformObject;
PropertyType;
Description;

0
Reply
Cail
Cail
3 years ago

Is there any reason why this wouldn’t work as a script in Automation Studio? Trying to get it to work there in our setup but not having much luck.

0
Reply
Gortonington
Gortonington
Author
Reply to  Cail
3 years ago

Only real reason I can see is if the script times out. Other then that, you could explore issues with field names or data points that could potentially cause issue inside your returned DE info.

0
Reply
Lobo
Lobo
3 years ago

Could you please tell me how would i identify a Standard, Filtered or a Random Filtered DE from the information extract? what is the one identifier?
i’ve looked through and through, i couldn’t get the identifier, please help.

9
Reply
Lobo
Lobo
Reply to  Lobo
3 years ago

any update?

3
Reply
Gail
Gail
3 years ago

Hello. Thanks for this post! I’m new to programming in MC. How do I run your code? For example, can I run it in a MC CloudPage Landing page? Do I need to run it outside of MC (if yes, do you know of any tutorial or post that would explain how)?

I’ve gotten some basic SSJS and ampscript to run in a Cloudpage Landing Page, but can’t figure out how to run your code.

Thanks, again!

0
Reply
Gail
Gail
3 years ago

Hi Again. Please ignore my previous comment (asking how to run your code). I just re-read your post and see that I am supposed to create a script acivity, which I just did. I haven’t gotten the code to work yet (it’s not populating the data extension), but I’ll keep looking at it.

Thanks again for this awesome post!

Last edited 3 years ago by Gail
0
Reply
Aditya
Aditya
3 years ago

Hi ,
When calling this functions for all the Data Extensions fields and Rowcount are not being populated.It is giving an error called BadRequest

0
Reply
Gortonington
Gortonington
Author
Reply to  Aditya
3 years ago

I will take a look and revisit this soon to validate it as it has been a while since I posted this, but to my knowledge all should continue to work accurately.

0
Reply
roopa
roopa
Reply to  Gortonington
2 years ago

any update on this?

0
Reply
Gortonington
Gortonington
Author
Reply to  roopa
2 years ago

Apologies, I took a look at this and that led me to rewrite the entire thing and posted the new version in my ‘take 2’ blog post here: https://gortonington.com/sfmc-data-extension-inventory-take-2/.

I completely forgot to come back and respond directly to you after that. Using the new version in this article should be a better solution for your needs.

1
Reply