Maintenance Connection
MCe API

Overview of the "Maintenance Connection Everywhere API" aka MCe API

Interface:

Is it REST? Yes, that is one of the options we fully provide. Representational State Transfer. Textual representational for stateless operations. Rest typically is much 'smaller' text than SOAP.

Is it SOAP? No. SOAP is an old trusted XML based protocol. In some ways very similar to REST but SOAP is much wordier and generally fewer API end points. Most companies are moving away from it.

Is it gRPC? No. A fast binary remote procedure call used primarily for tiny 'microservices'. Our API is the opposite of what gRPC is designed for, and so for most purposes it would be inappropriate.

Is it GraphQL? Yes, that is one of the options we provide. They describe themselves as: a query language for APIs and a runtime for fulfilling those queries with your existing data. GraphQL provides a complete and understandable description of the data in your API, gives clients the power to ask for exactly what they need and nothing more, makes it easier to evolve APIs over time, and enables powerful developer tools.

Which should you use? Generally, whatever your tooling and your expertise and preferences are. If you are starting with an open slate, we recommend you look at GraphQL since it was designed specifically for database APIs. It provides a lot of power with the least amount of work at your end. All the rest (no pun intended) derive their power from the code you write. But note: We have already passed most of the power of GraphQL to you in the other three end points so the difference isn't as great as it usually is.

Requesting data

Is it ODATA? No, ODATA is an 'opinionated Spec for REST'. We don't support it, but we do use many of its features. See the REST documentation for details.

Is it JSON? Yes, that is one of the options we provide for our RestQL and GraphQL.

Is it XML? The RestQL supports XML.

We also let you through configuration and GraphQL define static and dynamic where clauses.

Sending data

You will receive back important info. For example, when you create a WO, you will need to get back the PK that MC created, plus all the changes MC made to your query.

GraphQL, JSON you call a change query with the data it needs, and you define what you WANT to receive back (nothing or everything or anything in between)

REST, JSON and XML to send it, but you need to call back to ask for the changes in many cases because of the REST rules. Our SyncGUID allows you to accurately request the created data.

Receiving data back

REST comes back as JSON or XML

SOAP (not supported, at least currently) comes back as the SOAP XML protocol

gRPC (not supported, at least currently) comes back as binary

GraphQL comes back as JSON

What has the API been used for?

In 2003 we wrote the first version of the API, We used it in our VERY first product "Asset Pro CMMS", it was known as the Asset Pro API. Later in the year we used this in our very first "Maintenance Connection Mobile" product that shipped to customers in 2003, it was compatible with MC 1.0, it was called the "Maintenance Connection Canada API". We continue to evolve this API through the years, we use it all our products: that access EAM/CMMS data:

  • Maintenance Connection Everywhere modules
  • Asset Pro CMMS modules
  • Maintenance Connection DatatHub modules
  • A custom web page we wrote to the specs of a client for booking hotel rooms without any overhead of an 'app' having to be loaded
  • Custom code we write for customers, such as Municipalities that want a way on their web site for their citizens to record potholes that need fixing, and then be able to hear back when the pot hole is reported as fixed.
  • In the MCe report writer.
  • In the MCe SQL tool.
  • There isn't much, but we use it in our Desktop App for MC.

Why would you want to use the MCe API or any API to access MC data?

  • You may want to use it to programmatically move data back and forth between two systems. This is especially important if you want to 'create' things like Work Orders in another system, by giving you the offline functionality that we use for MCe Work Orders, you can reliably create non-conflicting data.
  • You may be writing custom service requestor pages on your web site to let users submit them with whatever flexibility and defaults make sense for your needs.
  • You may want to write your own specialized interface to MC, such as something similar to the Maintenance Connection Everywhere WO Technician module.
  • You may want to create your own user interface for signing up users for use with MC or through the Maintenance Connection LoginHub.

We use our own API, others don't use theirs

This is something that, in the CMMS and EAS space, as far as we have seen, we are distinctly unique.

Other companies do NOT use their own API for most of their work. This can be easily seen by noting that other APIs are missing most of what would be needed to write their products!

With the MCe API: you don't just get the data, you get access to every trick and optimization that we have, because we use our API for everything, there are no back doors or performance enhancing cheating for us! So if we think up an optimization, you get it as soon as we get it. This is partly because we have been using our API since 2003, whereas the APIs of other companies are introduced many versions after their initial product.

When we write new modules, one of the first things we have to do is update the API to support the new functionality, since we use the API for all data access, we can't do much beyond design without building out the API. This means the API has always been at or ahead of anything else we do.

We allow you to Export or import data in specialized or proprietary formats.

Best in class data defining tools

We are very proud of our tools for defining the data you require. While you can hand define long, complex SQL, and our professional services can help you when you need to, for almost everything most customers need, we provide powerful GUI tools to define the data you need.

  • We have intellisense when creating queries. You don't need to look up and spell correctly every field and table, just use the dynamic intellisense. If you aren't a programmer, think of this as "live help picking tables and columns etc.." instead of having to memorize or look up everything.
  • We know and understand the relationship between different tables of data
  • We know and understand MC. We have since 2003, and we pass that knowledge on to you. For example, there is a table in MC that has 'problems' 'failures' and 'solutions' all in the same table. We let you access it that way. But we ALSO let you access it as each of those 3 individually. Where this really helps is: There are fields in the table that do NOT apply to all three types; by accessing 'problems' as just problems, you only get access to the fields that apply to problems. Another example is for the different types of users, again, different columns/fields/data apply to the different types in the table. We sort that out for you so that you don't do things wrong.

The MCe API rejects bad data to protect your data.

Most APIs are 'dumb'. The MCe API is 'smart'.

Other APIs assume that you have verified the data is correct, and if it is technically possible to insert or modify it the way you asked, they will do so, even if that means your data is now corrupt. Since 2003 we have been adding rules to our API engine to refuse to accept corrupt data. While there are still more we will add as we realize them, we have a lot of rules and we will reject your data that would end up corrupting it.

Now… this doesn't mean we can catch EVERY mistake you make. If you enter a 'valid' date that happens to be 'wrong', we have no way of knowing that, so you should still try to vet your data the best you can, but we provide this extra help to reject as much data that we can know is wrong as possible. You get decades of experience catching these errors in the MCe API. Sadly, other APIs in the CMMS space provide none of that.

More specific data typing

GUIDs are returned as GUIDs, not as "Strings" and can therefore be used in your complex queries as identifiers.

Offline tooling – we give you the data access tools we use

Has features for offline creation of objects in non-conflicting ways, in particular though the 'sync guid'.

We conserve bandwidth

Space/bandwidth saving when 'default' values used. You do not have to pass us default values nor do we use bandwidth to send fields with default values to you. Strings default in the database to "" or NULL (which in the Maintenance Connection database family always have exactly the same meaning), so we do not return "" strings or NULL strings. Numbers default to 0 or NULL again, there is no mechanism in Maintenance Connection MC or the other products in the family to give any meaning to 0 vs NULL. Booleans default to F, and again, there is no mechanism in Maintenance Connection to give a difference in meaning between F and NULL.

Keeping up to date

Obviously, looking forward, as the database design changes, our API will have to change since we run everything through the API … if it doesn't we have no way to access the new tables and fields.

Why would you want to use the MCe API instead of the MCe DataHub?

The DataHub is designed for NON-programmers to be able to very easily provide Maintenance Connection a table of data (such as in a XLSX spreadsheet) and have it imported typically with zero programming.

The MCe API is more powerful, but it requires programming. Note that the DataHub contains over 2 million lines of code – but it exclusively uses the MCe API for all of its data handling to and from the Maintenance Connection database.

Why would you want to use the MCe API instead of the MCe UI modules such as the work order technician?

The first reason is because the API is designed to let you operate without any human interaction if that is your goal – to allow one system to talk to another with no user input, whereas most of the MCe UI modules are designed with specific types of users in mind.

  • The Work Order technician modules are designed to optimize day to day functions of technicians. The admin ones have the option of working in Admin mode or Technician mode to get the right type of optimizations for how you are currently working.
  • The Inventory Count Lite module is designed to make what is often called 'heads down data entry' as efficient as possible for inventory counters.

With the MCe API, you basically get no UI (other than the setup UI which is a MCe module). But since the MCe API is also THE way we access data for all the MCe modules and the datahub – the MCe API gives you all the power and speed and flexibility that we have had access to since 2003, and lets you create your own UI for any customized purposes. Basically, on the data access side – we give you exactly and everything that we have for accessing the data.

You can then use it to create simple web pages that interact with the MC database, such as the examples we gave earlier: A web page for potential customers to book available rooms in a hotel which then creates automatically a work order to clean the room upon the room being vacated. Or a web page for citizens of a municipality to record potholes, and then hear back with no human interaction required, when the pot hole is fixed.

Specific interesting fields

Row Version fields

The Row Version fields are a very convenient way of knowing 'did this row get updated since the last time I checked it?". But before you use the Row Version, check out the 'Hash' and see if it will better suit you needs.

MS SQL store, probably for a performance reason, Row Version values in MS-SQL are stored it what is called 'big endian'. Arguably this could be described as "everything is backwards".

We return you the ROW Version in the most commonly used form – as a number that increments. So that if a value is 'higher' then another, then it means the row was updated more recently. If the value is the same – then there have been no updates to the table, and if the value is lower, you are getting an 'older' copy. Note that the only way the API can return an older value is if you restored the database.

Does the Row Version guarantee the data is different? No. That is not it's job, even though it might seem on the surface like it is. The Row Version tells you that it MIGHT be different, and even if 99% of the time it is different, it does not guarantee that it is different.

Every time you execute an update statement, the database updates the RV value, even if there were no actual changes. Similarly, you may be only interested in 12 values, but if a 13th value changes, both the RV value will change, even though it changed in a way that you don't care about.

This factor is the same with the hash value, so both the RV value and the hash value should be treated as "You need to get the, potentially, new data and if "no changes" is an important distinction for you, then you need to compare the values YOU care about in the new data with your old data to determine whether there were any changes that you care about.

The RV gives you an optimization strategy where, if the RV is the same, you KNOW the row did not change and you can ignore it.

The RV can be used on a filter/where clause. If you know the highest RV value you have for a table, you can say "Give me all the rows in this table with an RV > [the max value you already have]' to get all the ones that are new or have changed. Warning: this will NOT give you a list of 'deleted' rows. It will only give you the new and updated rows.

Hash

For many tables, especially so thought of 'master' tables, we include a Hash.

The hash is used to compare two sets of data and ask "Are they the same". If the Hash is different, then you need to get (and use) the new data.

Unlike the Row Version you can not use < and > to determine which is newer. But since you are usually comparing data you have with the data in the database, unless the database was restored to an earlier version, if the Hash is different, the new data IS the newer data.

For those interested, here are the major factors we currently take into account when generating the hash:

  • When looking at a table and it's child tables, you want to know "did anything change in this table or the children". It may seem at first blush that the Row Version gives you everything you need. But consider this:
  • First problem: What you really need is the HIGHEST RV value of the row and all the children, to ensure you have the most up to date set of data.
  • But that still is not enough. What if a child record has been DELETED. If all you have is the highest value, you could still be getting out of date data, because, unless the row that was deleted also had the highest RV value, when that row is deleted, the highest RV will not have changed. So we add a hash for number of each set of children.
  • Then, you have the problem that the table schema may have changed. So we include that as part of the hash.
  • Then, you may have changed the amount of data you are returning. Perhaps you have added a field to your return result set. So we include that as part of the hash.

You can, and should, think of the Hash as a 'super reliable Row Version' value.

The last time we added to the Hashing algorithm concept was in 2015. But if we ever do find and consider another factor, it will still work for you because the new hash will be different, so you will know that the data might be different.

Does the Hash guarantee the data is different? No. That is not it's job, even though it might seem on the surface like it is. The Hash tells you that it MIGHT be different, and even if 99% of the time it is different, it does not guarantee that it is different. This is the same as the RV value. Every time you execute an update statement, the database updates the RV value, even if there were no actual changes. Similarly, you may be only interested in 12 values, but if a 13th value changes, both the RV value and the hash value will change, even though it changed in a way that you don't care about.

So both the RV value and the hash value should be treated as "You need to get the, potentially, new data and if "no changes" is an important distinction for you, then you need to compare the values YOU care about in the new data with your old data to determine whether there were any changes that you care about.

The hash value can be used on a master object by master object level. It cannot be used as a "give me all > [than the highest hash value I currently have]" in the where clause/filter value, because the hash does not have 'order' associated with its value. It only has 'change' associated with its value.

Keep your MCe API keys private

I hope this is obvious. But in case you overlook it … When you generate a MCe API key, you are creating an access point that has all the power that you assign that key, and that power usually is a lot.

You should ONLY give the keys you generate to trusted people. You should never make the keys visible to the public.

If at some point you fear that your keys may have been exposed, then like ALL private keys and access that you may have for any system, you should delete the existing keys and their associated users. See our 'API Security First' document, if still needed, generate new ones, and then install those new keys in your software that uses them. Note that, until you delete the key in the MCe API UI module (or delete them with a database command), they can be used to their full power by whoever has the key.

When a trusted employee leaves, you should replace all the keys, including MCe API keys, that that user had access to by first invalidating the old ones by deleting the user. You should not say "Oh, I trust Susan, it is OK." You want to replace them first, just in case Susan uses them in an inappropriate way. But you also want to replace them out of courtesy to Susan. That way, if the key is used for a 'bad' purpose, you will know with certainty that Susan didn't do it – because the new key was not known to her.

The author of this document once had access to 20% of the US payroll. When the contract was finished, the owner of the company said "I'll leave your access so that if we need you to make a quick fix, you can." I explained the above to him and said : "I don't want there even to be the possibility that something might be thought of as my mistake". So it was decided that he would delete my access and … if he needed my help again later, he would recreate new access. He never did need me to come back to make any changes, so I never had it. The point was: I do NOT want to have access, because if something goes wrong, people will always wonder if maybe it was me that did something.

So … whether it is the MCe API key or any other login or valuable keys, when employees leave that know them: Replace them or delete the user account to invalidate them, for your sake and theirs.