[APIC]Administering Developer Organizations: Exporting Data

https://developer.ibm.com/apiconnect/2017/01/03/administering-developer-organizations-exporting-data/

Administering Developer Organizations: Exporting Data

Dan_Whitacre
Created on January 3, 2017 / Modified on January 3, 2017

Exporting Developer Data

Using Analytics in APIConnect, we are able to export data that summarizes the API calling activity usage for all our developer organizations. This aggregation of data can be helpful to the admins of our organizations and can primarily used in billing applications. In this guide, you will be able to import a pre-existing visualization or build your own customized billing visualization within analytics. From there, you will be able to add this to one of your dashboards, or to a new one. Finally, you will be able to export the data from this visualization to a CSV file for your use.

For starters, let’s navigate to the Analytics tab within one of our catalogs in APIConnect.

Creating the visualization

Now, you will want to create a visualization that holds the aggregated data within analytics. Here, we have two options. The first, importing a pre-existing visualization, is the simpler and quicker option. We recommend this for those that do not need to know the intricacies as to how their visualization works and primarily want to use it for just exporting the CSV file. The second, building your own visualization, is the more in-depth option. It is recommended for those that want more access to how exactly their visualization is configured, and those that would like to understand more how their visualization is created.

Option 1: Import Pre-existing

Importing a preexisting visualization is easy! First of all, you will want to save the following content as a JSON file (i.e. import.json).


[
{
"_id": "Billing",
"_type": "visualization",
"_source": {
"title": "Billing",
"visState": "{\"type\":\"table\",\"params\":{\"perPage\":25,\"showPartialRows\":false,\"showMeticsAtAllLevels\":false},\"aggs\": [{\"id\":\"1\",\"type\":\"count\",\"schema\":\"metric\",\"params\":{}},{\"id\":\"8\",\"type\":\"terms\",\"schema\":\"bucket\",\"params\":{\"field\":\"product_name\",\"size\":0,\"order\":\"desc\",\"orderBy\":\"1\"}},{\"id\":\"9\",\"type\":\"terms\",\"schema\":\"bucket\",\"params\":{\"field\":\"plan_name\",\"size\":0,\"order\":\"desc\",\"orderBy\":\"1\"}},{\"id\":\"2\",\"type\":\"terms\",\"schema\":\"bucket\",\"params\":{\"field\":\"app_name\",\"size\":0,\"order\":\"desc\",\"orderBy\":\"1\"}},{\"id\":\"10\",\"type\":\"terms\",\"schema\":\"bucket\",\"params\":{\"field\":\"developer_org_name\",\"size\":0,\"order\":\"desc\",\"orderBy\":\"1\"}},{\"id\":\"11\",\"type\":\"terms\",\"schema\":\"bucket\",\"params\":{\"field\":\"developer_org_id\",\"size\":0,\"order\":\"desc\",\"orderBy\":\"1\"}},{\"id\":\"5\",\"type\":\"terms\",\"schema\":\"bucket\",\"params\":{\"field\":\"api_name\",\"size\":0,\"order\":\"desc\",\"orderBy\":\"1\"}},{\"id\":\"6\",\"type\":\"terms\",\"schema\":\"bucket\",\"params\":{\"field\":\"uri_path\",\"size\":0,\"order\":\"desc\",\"orderBy\":\"1\"}}],\"listeners\":{}}",
"uiStateJSON": "{}",
"description": "",
"savedSearchId": "",
"version": 1,
"kibanaSavedObjectMeta": {
"searchSourceJSON": "{\"filter\":[]}"
}
}
}
]


Feel free to edit the title and _id fields in the content above. The _id field should be the same as at the title field but with the spaces removed / replaced. For example, your title could be ‘API activity for developer organizations’ and then your _id should be ‘API_activity_for_developer_organizations’.

Now, from within Analytics, navigate to the manage visualizations page.

Then, click the import button and select the appropriate import file. From here, you should see the visualization appear in the list below. However if you have a large number of visualizations (>100), you may have to use the filter bar for it to appear.

Nice! You have now imported the pre-existing visualization. Let’s navigate back to the dashboard view using the ‘X’ button in the top right of the Analytics view. Feel free to skip option 2 below and jump down to the Adding to a dashboard section.

Option 2: Building Your Own

So you want to build the visualization yourself? Awesome! From here, you will be able to tweak it to exactly how you want it. First off, let’s head to the create visualization page. From here you are going to want to select the ‘Data table’ option, which will take us over to our visualization editor.

Now, in the visualization editor, we have a lot of options on the left hand side. The ‘Data’ tab controls the meat of our visualization and represents what you will see. The ‘Options’ tab will give you some smaller customization tweaks you can make. I usually wait till the end to play with those. Lastly, the green play button, pushes any changes you made on the left hand side into the visualization preview on the right side, and the gray ‘X’ button is an undo button for any modifications you may have made on the left hand side.

Now, our data is an aggregation on API path call counts. Therefore, the metric we are tracking is ‘Count’. This is the default selected and there is no reason to change it for our guide here. However, below you can see how to change it if there is something else besides ‘Count’ selected, or if you are curious what other metrics you could track in your visualization.

Easy enough right? Let’s get into the meat of our aggregation which is our buckets. Now, there is a lot you can do here so feel free to mess around in this area for fun. For our aggregation though, we are going to need to create seven ‘Split Rows’ buckets. For each bucket, you are going to want to select ‘Terms’ as the ‘Sub Aggregation’. This will create a column for each term in our ‘Data Table’. This will also create a new row every time we have a different term in one of our columns, and in turn a separate call count for each of our API paths. Next, a couple more options will appear. For ‘Field’, you are going to want to select the term that we want to split our rows of our ‘Data Table’ on. In our case, we will want to select the following fields:

product_name, plan_name, app_name, developer_org_name, developer_org_id, api_name, and uri_path.

Each field will have its own bucket. The next option ‘Order By:’ changes the order the term gets sorted by in the data table. I usually choose to just go with my metric, in this case ‘metric: Count’, but you are welcome to sort by anything you wish. The last two options are ‘Order’ and ‘Size’. The ‘Order’ option determines the initial ordering of what data will get displayed in the visualization, while the ‘Size’ option will determine how much of the data gets displayed. I almost always set my ‘Size’ to zero, as this means that all of my data will actually get displayed for that column. The default it 5 which will limit you data to 5 rows for that aggregation. I do not recommend that unless you are really only concerned with very active data with high call counts. Lastly, there is an ‘Advanced’ tab as well, but there is no need to mess with any of the options in there.

As you can see in the following gif, I work my way through setting up all my buckets and configuring their settings exactly how I want them pertaining to what I mentioned above. Lastly, feel free to move the columns around if a different organization of the data suits you better.

Now, to the ‘Options’ tab. From here you can do a couple of things, which differs from visualization type to visualization type. For the ‘Data Table’ we can set how many rows we view per page, show the metrics for every bucket, and so on. If I expect my ‘Data Table’ to be quite large, I like to increase my per page to 25, which in this case I do. Also, sometimes I think it is meaningful to have the metrics on for every bucket, but in this case it’s not entirely necessary.

Great! Everything is looking good and we created a visualization! Let’s save and title it.

Nice! Let’s navigate back to the dashboard view using the ‘X’ button in the top-right corner of the Analytics view.

Adding to a dashboard

Now that we have our visualization, let’s get it added to a dashboard. We can either add it to a pre-existing dashboard, or if you are like me, I prefer to have this ‘Data Table’ in its own new dashboard. Due to the size of the data, I find it easier to comprehend when I can view it all by itself, expanded as big as it can be.

To add our visualization simply click the ‘plus’ icon in the top-right of the Analytics view. From there simply find the visualization you just created, in my case the ‘Billing’ visualization, and select it.

This will insert it to the bottom of the dashboard you are currently on. You can then expand the visualization by dragging the bottom right-hand corner of it, and you can also drag it around the screen you put it in the location of your desire. Lastly, just save the dashboard. If it’s a pre-existing dashboard, just do not edit the name and it will overwrite the original dashboard. If it’s a new dashboard name it whatever you like, I go with the name ‘billing’ as my ‘Billing’ visualization is the only visualization on my newly created dashboard.

Exporting to CSV

So, now that we have the data we want, we want to be able to export this to a CSV file. As you will be able to see exporting the data is as easy as clicking either ‘Raw’ or ‘Formatted’ down in the bottom-left corner of your visualization. ‘Formatted’ will change any dates to a nice readable format, while ‘Raw’ will leave them in ISO 8601 format. We do not have any dates in this visualization, so both will do the same thing. After clicking one of them, the data in the visualization will download to a CSV file in your browser.

Once you have the CSV file, it’s your to use however you like! You can consume it in another service for billing applications, you can store it somewhere for record, or maybe even throw it into an external database for later applications, it’s your call!

Summary

Congrats! You successfully were able to import or create a visualization that contains an aggregation of call counts of your APIs used by your developer organizations. You added this dynamic visualization to a dashboard and were able to export the data to a CSV file. Finally, You may have even started using this data in your own applications.