Implementing a Simple Asset Management System

In this tutorial you will use the Aggregation Builder, the Gateway and joins to implement a simple asset management/tracking system. Everything in the tutorial is simplistic and only meant to demonstrate techniques that you can use in your applications (for example, by using Gateway APIs as explained in the Using the Gateway to Build Dashboards and Applications tutorial).

The tutorial uses two collections. One collection defines all asset types in the company and includes documents such as:

{ "_id" : ObjectId("537fb57685dd83c379fb2684"), "published_as" : "Capital hybrid derivatives 2.3", "name" : "Capital", "bu" : "Fixed Income", "owner" : "John Doe" }
{ "_id" : ObjectId("537fb58285dd83c379fb2685"), "published_as" : "Capital hybrid derivatives 2.4", "name" : "Capital", "bu" : "Fixed Income", "owner" : "John Doe" }
{ "_id" : ObjectId("537fb58a85dd83c379fb2686"), "published_as" : "Capital hybrid derivatives 2.5", "name" : "Capital", "bu" : "Fixed Income", "owner" : "John Doe" }
{ "_id" : ObjectId("537fb5ab85dd83c379fb2687"), "published_as" : "CBX 2010-003", "name" : "CBX", "bu" : "FX", "owner" : "Jane Doe" }
{ "_id" : ObjectId("537fb5b485dd83c379fb2688"), "published_as" : "CBX 2012-004", "name" : "CBX", "bu" : "FX", "owner" : "Jane Doe" }
{ "_id" : ObjectId("537fb5e385dd83c379fb2689"), "published_as" : "Unicorn level 0", "name" : "Unicorn", "bu" : "Equities", "owner" : "Joe The Plumber" }
{ "_id" : ObjectId("537fb5ed85dd83c379fb268a"), "published_as" : "Unicorn HFT", "name" : "Unicorn", "bu" : "Equities", "owner" : "Joe The Plumber" }
{ "_id" : ObjectId("537fb60485dd83c379fb268b"), "published_as" : "Unicorn Flash", "name" : "Unicorn", "bu" : "Equities", "owner" : "Joe The Plumber" }

Each document has the unique application identifier (published_as), the name of the application, the business unit, and the owner of the application.

Application instances are discovered in the install base using a set of scanners. Scanners run continuously and when an application is first discovered it is inserted into the instances collection which looks like:

{ "_id" : ObjectId("537fb71085dd83c379fb268c"), "identifier" : "Unicorn Flash", "host" : "", "found" : ISODate("2014-05-23T21:00:54.344Z") }
{ "_id" : ObjectId("537fb72285dd83c379fb268d"), "identifier" : "Capital hybrid derivatives 2.3", "host" : "", "found" : ISODate("2014-05-23T21:00:54.344Z") }
{ "_id" : ObjectId("537fb73085dd83c379fb268e"), "identifier" : "CBX 2010-003", "host" : "", "found" : ISODate("2014-05-23T21:00:54.344Z") }
{ "_id" : ObjectId("537fb75085dd83c379fb268f"), "identifier" : "Capital hybrid derivatives 2.5", "host" : "", "found" : ISODate("2014-05-23T21:01:44.746Z") }
{ "_id" : ObjectId("537fb76985dd83c379fb2690"), "identifier" : "CBX 2012-004", "host" : "", "found" : ISODate("2014-05-23T21:02:13.562Z") }
{ "_id" : ObjectId("537fb78085dd83c379fb2691"), "identifier" : "Unicorn HFT", "host" : "", "found" : ISODate("2014-05-23T21:02:37.319Z") }

The identifier is scraped from various application log files and can be any string. While it usually contains the name of the application (e.g. “Unicorn HFT” contains “Unicorn”) this is not necessarily true and should not be assumed. Each such scanner is written by different groups which do not have access to any metadata and therefore the data in apptypes is not available to the writer of instances - it has to be done upon the time of query.

We want to answer a few questions based on scans and the type metadata:

  • We want to report on all discovered instances that has not been published as a type. Normally, when an application gets pushed, a new document in the apptypes collection should be created or we will not know who that instance belongs to. If it is not - we want to know about it so we can chase it down.
  • We want to know which hosts each owner needs access to. For example, if you are Jane Doe then you own all versions of the CBX application and therefore need access to and in the example above.
  • Per host we want to know which business units are relying on it to know the impact to the entire business if that host goes down.

Using a Subquery

The first question can easily be answered using a subquery (for more on subqueries see the Finder page). You’ll build a query on the instances collection to list all documents that has an identifier that is not in the apptypes collection.

Login to the Studio and in the Finder select the instances collection. In the “additional where” section (in the middle of the screen) select SUBQ for the subquery operator. Then double click on the identifier field on the left. This will create a template of the form:

"identifier" : {$in: {$ns: "<col_name>", $q: {}, $p: "<path>"}}

Change this to:

"identifier" : {$nin: {$ns: "apptypes", $q: {}, $p: "published_as"}}

In effect you are saying that you are looking for all instances where the identifier is not in the return value of the subquery that gets all the published_as from apptypes. If you click on the “Query” tab at the top you will see the main query that went to MongoDB:

  "identifier": {
    "$nin": [
      "Capital hybrid derivatives 2.3",
        "Capital hybrid derivatives 2.4",
        "Capital hybrid derivatives 2.5",
        "CBX 2010-003",
        "CBX 2012-004",
        "Unicorn level 0",
        "Unicorn HFT",
        "Unicorn Flash"

The array has been populated for you by the Finder using the subquery.

Back on the “Point-and-click” tab give the query the name new_apps by entering it in “Save search as” and clicking save.

This is a query you will want to run from your own tools and applications rather than logging into the Studio every time - so you can use the Gateway to make this call. Log out and using curl or a browser or any library that can make an https call make a call to the Gateway similar to the one below:


As you can see the result set is empty because all apps have been documented in apptypes. But let’s add a new document to instances as follows:

{ "_id" : ObjectId("537fc51185dd83c379fb2692"), "identifier" : "CBX Ultimate EOW", "host" : "", "found" : ISODate("2014-05-23T21:59:53.147Z") }

If you now re-run the API you get:


Joining the Data

To answer the other two questions you need data from both collections - therefore, the first thing you need to do is create a new collection that is a join of apptypes and of instances (note that with the joined collection we can also answer question #1, but it was useful to see how to use subqueries).

Use the Finder and the Gateway to build the join collection. The main collection is the instances collection and the secondary is the apptypes collection. The join will be done by iterating on the instances collection and for each document making one additional query on the apptypes collection.

First define the query on the apptypes collection that you will use per document in the instances document. In the Finder select the apptypes collection, make sure the == operator is selected, and double click on published_as in the left-hand-side pane. This will add a condition:

"published_as" : <enter value>

Change this to:

"published_as" : "$$app_string"

Because you have not bound any value to this bind variable the result set will be empty - but that’s ok because the join will supply the value. Save this query by entering “type_query” in the “Save search as:” field and clicking the save button.

Now select the instances collection and either de-select the apptypes collection or make instance the primary collection in the left pulldown. Click on the JOIN radio button in the middle-top pane, hold down the alt key and double click on “identifier” in the left-hand-side pane. This will create a projection condition of the form:

"<join field name>" : {col: "<col name>", q: "<saved query name>", "$$<join bind var>": "identifier"}

Change it to:

"type" : {col: "apptypes", q: "type_query", "$$app_string": "identifier"}

You are using the query you just saved and the bind variable you defined and specifying that the new subdocument from the apptypes query should be called “type”. If you run the query you may get data from instances but you will not get the additional type subdocument because you need to run it from the Gateway. Give the query a name (e.g. instance_join) and save the query.

Now run the join from the Gateway by invoking this query from an API to test it out:


Now every document in instances includes the data about host, business unit and owner in the type subdocument.

Rather than get the join data out in a result set, insert this data into a new collection called full_instances by adding output=collection.full_instances as shown below; you now have the data in a collection from which to answer the second and third questions:


In real life you would add another parameter to the query based on time and insert or update documents incrementally - e.g. run the API call every hour while looking only at the last hour’s documents using a query condition. You can also run a Gateway API as an upsert rather than an insert.

Answering Questions with Aggregation

To know which hosts every owner needs access to is a simple one-stage aggregation that groups by owner (from the metadata) and adds distinct values to array from the host field as shown below. You can use this query both from the Studio or from the Gateway (and thus your own apps):


To know which business units rely on which host is also a simple one-stage aggregation grouping by host and adding the distinct business units to an array and again can be run from either the Studio or as an API:


Building a Dashboard

Since Gateway APIs are simple HTTPS services you can call them from your own dashboards and applications.

See Configuring for Embedded URLs in Dashboards and Applications, for a configuration change you need to make to web.xml.

To create a simple Web page that answers the three questions in a dashboard page you can use the following simplistic example code:

<tr><th>Undocumented Instances</th><th>Host Access Needed</th><th>Dependency on Hosts</th></tr>
<td><iframe src="https://localhost:8443/sonarFinder/Gateway?col=instances&name=new_apps&type=find&port=47017&db=test&host=localhost&prettyPrint&keepSession=1" width="400" height="800"></iframe></td>
<td><iframe src="https://localhost:8443/sonarFinder/Gateway?col=full_instances&name=owner_access&type=agg&db=test&host=localhost&port=47017&prettyPrint=1&keepSession=1" width="400" height="800"></iframe></td>
<td><iframe src="https://localhost:8443/sonarFinder/Gateway?col=full_instances&name=host_dependency&type=agg&db=test&host=localhost&port=47017&prettyPrint=1&keepSession=1" width="400" height="800"></iframe></td>

This produces the following page:


See Using the Gateway to Build Dashboards and Applications for another tutorial on embedding Gateway API calls in applications and dashboards.

Table Of Contents

Previous topic

Using the Gateway to Build Dashboards and Applications

Next topic

Internet of Things (IoT) Use Case

Copyright © 2013-2016 jSonar, Inc
MongoDB is a registered trademark of MongoDB Inc. Excel is a trademark of Microsoft Inc. JSON Studio is a registered trademark of jSonar Inc. All trademarks and service marks are the property of their respective owners.