How to consolidate data into a sum within timeseries

[Hillman]

Currently I am use the following DQL to list out the web request count for our API calls:

timeseries { sum(webrequest) }, by: { http.response.status_code, server.address, TenantID, Tenant, url.path }
| fieldsAdd Tenant = if (tolong(TenantID) == 6, "Singapore")
| fieldsAdd Tenant = if (tolong(TenantID) == 7, "Australia", else:Tenant)
| fieldsAdd Tenant = if (tolong(TenantID) == 8, "Philippine", else:Tenant)
| fieldsAdd Tenant = if (tolong(TenantID) == 9, "Malaysia", else:Tenant)
| fieldsAdd Tenant = if (tolong(TenantID) == 10, "Thailand", else:Tenant)
| fieldsAdd Tenant = if (tolong(TenantID) == 11, "Hong Kong", else:Tenant)
| fieldsAdd Tenant = if (tolong(TenantID) == 13, "Indonesia", else:Tenant)
| fieldsAdd Tenant = if (tolong(TenantID) == 14, "Vietnam", else:Tenant)
| fieldsAdd Tenant = if (tolong(TenantID) == 15, "New Zealand", else:Tenant)
| fieldsAdd Tenant = if (tolong(TenantID) == 22, "CDM", else:Tenant)
| filterOut isNull(Tenant)
| filter http.response.status_code >= 404 and http.response.status_code <= 599
| filter matchesValue(server.address, "abc.com")
| fieldsRemove server.address

However, I have found there are quite a few of API calls which involve individual membership numbers:

I try to use DQL to remove the membership number from the URL:

| fieldsAdd URL = url.path
| fieldsAdd URL = if(
      matchesValue(url.path, "*/memberships/*"),
      concat(
         splitString(url.path, "/memberships/")[0],
         "/memberships"
      ),
      else: URL
  )

However, the resultant data end up look this:

Is there any way I can sum up the URL request count and consolidate /memberships into a single entry?

[marina_pollehn · Accepted solution]

Can I ask how you are currently monitoring the API? Are you monitoring it as a regular service? Is it showing up when you open the Services app?

Then I would suggest to use URL cleaning rules. Go to your Services app, select your service, go to it's settings via the three dots [...] and then select Request Naming Rules. You could write  the cleaning rules also globally but here I only do it for the specific service.

Start with the clean URL rules where you can also use regex to get the numbers/ids out. After, if you want, you can also use the new (shortened without IDs/numbers) URL for your request naming by writing request naming rules below.

A RegEx like: ^.*?\/memberships    could capture everything before and including /memberships and remove everything after.

But I think you might be following a different approach to monitor it? I can't fully reproduce the data in my playground quickly, but I think the issue is that you never remove the old url.path variable. If you remove it, DQL can no longer split the sum up even more by using url.path, which will give you more aggregated values.

So I would include this as a last step, after the last line of DQL you have shared. 

| fieldsRemove url.path

If this is not working, let me know, then I will put some effort into reproducing your data.

[dannemca · Accepted solution]

Try to remove the url.path field at the end of your DQL.

[Hillman]

@marina_pollehn, @dannemca  I have created an Open Pipeline and retrieving the data from spans. I have tried with removing the field url.path and it works as expected. Thank you for your help!