August 4, 2017

Security groups and application authorizations

We all know how flexible Security Groups are when dealing with user roles and granting application authorizations. However, flexibility often brings complexity.
You have carefully designed user's roles defining what applications and actions they are allowed to access in Maximo and implemented all using Security Groups application. The system goes live and after one or two years everything is messed up. Is too hard to check who has access to what and you no longer sure if the young electrician that was just hired 2 months ago has access to Database Configuration or Application Designer  :-)

Maximo has a built-in report called Security Group Access that can help but I hardly find it useful. In my opinion it is too detailed to get an overall idea of the security configuration. For example, I'm now working on a medium-sized Maximo solution with 20 security groups and around 250 users. Well... the 'Security Group Access' report is 89 pages long!


That was not going to work. I needed I better solution for my purpose so I decided to open my preferred SQL client and Eclipse BIRT Designer to have some fun. Results were pretty good so I have decided to share them with the Maximo community.

I came up with two custom reports that give me a quick grasp of the setup of user's authorizations. The first one is called Security Overview and simply lists all the Security Groups and Start Centers counting how many users are assigned to them.


The second report is called Application Security Overview. It tries to represent which security groups provide access to applications. It is not an easy task to represent so many information in a single report but I'm finally proud of the results so here is what I have achieved to get.


The report lists all the applications in the rows and the security groups in the columns. The cell is yellow if read access is granted, orange if write access is granted, the number is the count of sigoptions granted.
The report can easily get too big if more than 20 security groups are defined so I decided to accept a list of security groups as filter so I can analyze smaller sets of data separately.

Download and installation instruction is available here.

Any feedback is highly appreciated.

June 13, 2017

MxLoader now supports attachments

I have just implemented the capability to upload attachments straight from MxLoader. This is in response to some requests on my blog and MxLoader support forum.

Starting from Maximo 7.1.1.6 it is possible to import attachments to an object through MIF using Base64 encoding.
MxLoader has the capability to read files from the filesystem, encode them in Base64 and upload them on Maximo. This can be done on any Maximo object that supports attachments but the integration object structure has to be modified to include the DOCLINK object.

In this example the MYASSET object structure has been created with the DOCLINKS child object.



To specify a file to be uploaded you have to prefix the full path of the file with the ‘file:///’ string. Look at the screenshot below for an example of how to add two attachments to an asset.




June 2, 2017

Is your Maximo database in good health?

We all know Maximo is a complex product. It has plenty of features and is very flexible but sometimes complex to manage especially when system performances are poor.
A lot of things may affect the perceived performances of Maximo and it may be hard to have an overview of the entire system configuration.

What are the largest tables in the database that may need archiving?
Are database statistics updated to optimize database queries?
How much data is stored for each site?
Has the workload of system increased over the last years?
What are the most heavy reports users execute?
What are the most resources-consuming crontasks and escalations?
How is the reports/crontasks/escalations workload spread across the day?

These and many other questions have an answer in the Maximo Health Check report.
Maximo Health Check report is a utility for IBM Maximo and IBM Control Desk that can help identify and analyze issues, errors, or incorrect configurations that can lead to unsatisfying system performances.

Adding more JVMs to your application server cluster seldom solves Maximo performance issues. In our experience, the most effective performance improvement techniques are on the database side.
For large Maximo systems data archiving may be needed. This is because, after several years of usage, large volume of data accumulated into some tables slowing down database inserts and updates. Moving unnecessary data out of those  tables can greatly improve overall system performances.
When specific application queries, reports or escalations have unsatisfying execution times database indexes optimization could be the answer. Adding the right indexes and removing the wrong ones is a complex activity that requires deep skills but can produce outstanding results in many cases.
Sometimes index optimization is not enough to fix a badly written SQL query. In this case SQL queries optimization is the right technique. SQL queries are everywhere in Maximo: start center portlets, application queries, escalations and reports. By simply refactoring those SQL statements I have obtained impressive results in many cases.
Database tuning is the last chance. Assuming IBM suggested settings have been applied, this is 'brute force' approach can bring some incremental improvement to overall performances.


Download and run the Maximo Health Check report and you may find interesting information about your Maximo system.


April 4, 2017

Maximo REST APIs examples

This entry is part of the Maximo Integration Framework series.

In this tutorial I will show how easy it is to query and update data in Maximo using the Integration Framework (MIF) REST interface.

HTTP test client setup

To send REST calls you first need to setup an HTTP client. In this tutorial I will use a Google Chrome add-on called Advanced REST Client.

Retrieve a record

In this first example I will show you to retrieve a PERSON record from Maximo using a REST call with an HTTP GET request:

http://[MXHOST]/maxrest/rest/mbo/person/1

Probably you will get an authentication error like this:

Error 401: BMXAA0021E - User name and password combination are not valid. Try again.

This means that you have to pass the authentication credentials to your request. If you are using native authentication you can pass the _lid and _lpwd arguments as described in this example:

http://[MXHOST]/maxrest/rest/mbo/person/1?_lid=wilson&_lpwd=wilson

Now you should see the PERSON record identified by the PERSONUID=1.



NOTE: For all the examples below I will exclude the authentication arguments for simplicity.

MBO and OS resources

The REST API provides access to business objects and integration object structures.

The two calls below will provide access to the same resource:

http://[MXHOST]/maxrest/rest/mbo/person/1
http://[MXHOST]/maxrest/rest/os/mxperson/1

The first call access data straight from the PERSON object through MBO persistence layer.
The second call access data from the MXPERSON integration object structure through the MIF.
You will notice that results are slightly different.


Data format (XML or JSON)

By default Maximo retrieves data in XML format. JSON could be used instead passing the _format argument:

http://[MXHOST]/maxrest/rest/mbo/person/?_format=json


Query

To retrieve the REVIS person record use the following REST call:

http://[MXHOST]/maxrest/rest/mbo/person/?personid=revis

Note that Maximo will perform a wildcard search by default so if you type 're' instead of 'revis' you will get a list of records that contains 're' in the PERSONID field:

http://[MXHOST]/maxrest/rest/mbo/person/?personid=re

To search with an exact match use the ~eq~ token as demonstrated in this examples:

http://[MXHOST]/maxrest/rest/mbo/person/?personid=~eq~revis
http://[MXHOST]/maxrest/rest/mbo/person/?personid=~eq~re


Results can be sorted using the _orderby, _orderbyasc or _orderbydesc argument. Multiple attributes can be passed separated by a comma character.

http://[MXHOST]/maxrest/rest/mbo/person/?personid=re&_orderby=statusdate
http://[MXHOST]/maxrest/rest/mbo/person/?personid=re&_orderby=status,statusdate


Create or Update a record (AddChange)

To create an existing record the AddChange action can be used. The following example will create a new person named RESTINT. Note that in this case a POST request must be used instead of a GET.

http://[MXHOST]/maxrest/rest/mbo/person/?_action=addchange&personid=restint&firstname=Rest&lastname=Int

To update the same record we can use the PERSONUID returned from the create. In my example it's 161.

http://[MXHOST]/maxrest/rest/mbo/person/161?_action=addchange&personid=restint&firstname=RestNew&lastname=IntNew


Updating child objects

Lets now pretend we need to update an asset specification and one of its attributes. You will see things are now a little more complex.

First of all we can create a new asset with a POST request.

http://[MXHOST]/maxrest/rest/mbo/ASSET/?assetnum=myasset01&siteid=BEDFORD&description=TestTest

Take note of your ASSETUID and query the new record with a GET request.

http://[MXHOST]/maxrest/rest/mbo/ASSET/2585

Now login to Maximo, classify the MYASSET01 as a BEARING, add an ALN attribute and save it.
If you query the asset using the GET request above you will notice that you just have a CLASSSTRUCTUREID attribute with a number in it specifying the classification. This is not usable in our scenario and will not allow to update attribute.

The right approach is to switch to the object service structure. Try to query the new record with a GET request like this:

http://[MXHOST]/maxrest/rest/os/MXASSET/2585

You will see that the HIERARCHYPATH field is now available and a subelement ASSETSPEC returns the attribute.
If we now want to update the value of the ALN attribute we can use a POST with a dotted notation like this:

http://[MXHOST]/maxrest/rest/os/MXASSET/2585?ASSETSPEC.1.ASSETATTRID=BEARTYPE&ASSETSPEC.1.ALNVALUE=ABC&ASSETSPEC.1.LINEARASSETSPECID=0

If you want to set a new classification here's an example:

http://[MXHOST]/maxrest/rest/os/MXASSET/2585?hierarchypath=BEARING%20\%20ROLLER

Note how the spaces have been encoded in the URL with the '%20' string.

Hope this can help all of us dealing with integration scenarios using REST calls...

References

IBM Documentation
Maximo REST APIs reference material

March 16, 2017

Load failure codes in Maximo with MxLoader

Creating and maintaining the hierarchy of failure codes in Maximo can be quite long and boring task using the standard Failure Codes application. The easiest and fastest way of importing Failure codes and the corresponding hierarchy is using MxLoader.

The example described in this post is available in this tailored in this sample MxLoader file. Please download it before proceeding.

Since there is no out-of-the-box object structure in Maximo to import failure codes, you first have to create the MxLoader custom object structures. This can be easily accomplished by clicking on the Create Custom Object Structures in the MxLoader ribbon in Excel.



The second step is to load the failure codes IDs and descriptions.
The FailureCodes worksheet on the example will load one failure class, two problems, two causes and three remedies.


Note that in the last column we have the language code. This is helpful if you want to transalate the failure codes in multiple languages.

We have just loaded a flat list of failure codes so now we need to create the hierarchical structure. The Failure Hierarchy template allows you to load failure class hierarchy using the custom MXL_FAILURELIST Object Structure. The example in the FailureHierarchy worksheet loads a small hierarchy using the failure codes defined previously.


Note how the failure hierarchy is specified in a flat table.
The object IDs in the square brackets will be generated dynamically by MxLoader during the import process. Do not enter those IDs manually.

This is how this failure tree will look in Maximo.


Note that the Failure Hierarchy template does not allow querying existing failure hierarchy. This can be a limitation if you want to copy a failure class hierarchy from one organization to another or from one server to another.
To extract an existing failure hierarchy you have use an SQL query like this.

SELECT f.orgid, f.failurelist classid, f.failurecode class,
  p.failurelist problemid, p.failurecode problem,
  c.failurelist causeid, c.failurecode cause,
  r.failurelist remedyid, r.failurecode remedy
FROM failurelist f
LEFT OUTER JOIN failurelist p ON p.parent=f.failurelist AND p.type='PROBLEM'
LEFT OUTER JOIN failurelist c ON c.parent=p.failurelist AND c.type='CAUSE'
LEFT OUTER JOIN failurelist r ON r.parent=c.failurelist AND r.type='REMEDY'
WHERE f.parent IS NULL
ORDER BY f.failurecode, p.failurecode, c.failurecode, r.failurecode;


MxLoader is also able to upload custom levels of failure codes in the hierarchy. For example, if you have a custom level called SYSTEM, you can simply add it to the columns and MxLoader will handle it.



References

Failure Codes in IBM Maximo Asset Management (SlideShare)
Failure codes (TechMCQ)
How to load failure code hierarchy using MIF

February 6, 2017

Maximo keyboard shortcuts

You know IBM Maximo has a lot of fields and sometimes can be boring entering data. Here are some keyboard shortcuts that can ease the pain. I have highlighted in bold the most common and useful ones.

  • Tab > The most important keyboard shortcut is the TAB key. It allows you to move across fields without using the mouse and also performs data validation and auto-completion.
  • Space > Select or clear a check box.
  • Alt + F1 > Display field help for field in focus.


There are few other useful shortcuts to move across applications.

  • Alt + C > Go to the Start Center.
  • Alt + G > Open the Go To menu. Use the arrow keys to choose the desired app.
  • Alt + B > Bulletins
  • Alt + R > Reports
  • Alt + P > Profile
  • Alt + S > Sign Out
  • Alt + H > Help
  • Alt + R > Return
  • Alt + W > Return with Value


Last but not least you can quickly create and save records with these Toolbar Buttons shortcuts.

  • Ctrl + Alt + I > Insert New Record
  • Ctrl + Alt + S > Save Record
  • Ctrl + Alt + A > Change Status
  • Ctrl + Alt + C > Clear changes
  • Ctrl + Alt + P > Previous Record
  • Ctrl + Alt + N > Next Record


This is a simplified list of the available keyboard shortcuts. For more information refer to the official documentation.

January 29, 2017

Maximo search operators

Maximo search features are quite advanced but often neglected. By using the advanced search techniques described in this post it is possible to find relevant records easier and faster.

Equal

The equal '=' operator can be used to find only records that match that a word or number exactly. In other words '=' means "exact match".
Example: Enter =123 to find any records with the exact characters 123 in the field. (Search results would not include numbers such as 0123 or 1234AB. If you enter just 123, without the =, search results would include 0123 and 1234AB.)

Not Equal

To search for all values that are not equal to a specific value enter '!='.
For example, typing !=WAPPR in the status field will retrieve all records that are not in WAPPR status.

Greater/Less

The '<' and '>' characters can also be used to search for records that are bigger or smaller than a specific value.
  • Greater than '>': searches for any date after or number/word greater than the specified value.
    Example: Enter >1172 finds records of occurrences that have work order number greater than 1172.
  • Less than '<': searches for any date before or number/word smaller than the specified value.
    Example: Enter <1172 finds records of occurrences that have work order number smaller than 1172.

List of values

You can use comma ',' operator to list the records that match with either of the values specified. It basically acts as the OR operator.
For example, typing WAPPR,COMP in the status field will retrieve all records that are in WAPPR or COMP status .

Wildcards

You can use a "wildcard" characters with letters or numbers to indicate you want to find records that begin with, end with, or contain those letters/numbers.
There are four characters you can use as a wildcard:
  • Asterisk '*' or percent sign '%': stand for any number of characters (zero, one, or multiple) in the specified position.
    Example: Enter 123* to find records that start with 123, such as 123, 12345, 123ABC.
    Example: Enter *123 to find records that end in 123, such as 123, 5123, PUMP123.
    Example: Enter *123* to find records that contain 123, such as 123, 1234, PUMP123xy.
  • Underscore '_' or question mark '?': stand for a single character in the specified position.
    Example: Enter 123_ to find any four‐character records that start with 123, such as 1234, 1230, 123g.
    Example: Enter or _18 to find any three‐character records that end with 18, such as 418, J18.

NULL Values

You can search for null and not null values by entering the following values into a search field on the Find tab:
  • To search for a null value enter: ~null~
  • To search for not null values enter: != ~null~

If your keyboard does not have the tilde '~' character you may type enter this character by typing 126 on your numeric keypad while pressing the 'Alt' key.

Dates

A small additional tip for searching dates in the current month.
If you want to search for WO's with scheduled start date less than today, you don't need to type or select the whole date into the search field because Maximo automatically appends the current month and year in the where clause.
For example, if you search with <15, system will return all WO's where scheduled start date before the 15th of the current month.


References

Understanding Search Methodologies
To Search For Records in a Maximo Database
Search for NULL values in Maximo applications


January 18, 2017

Find inactive users in Maximo

Here is a quick SQL query you can use to find inactive users that haven't logged into Maximo in the last 2 months.

DB2
select * from maxuser
where status='ACTIVE'
and sysuser=0
and not exists (select userid from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and attemptdate>(current date-60 DAYS))
order by userid;

Oracle
select * from maxuser
where status='ACTIVE'
and sysuser=0
and not exists (select userid from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and attemptdate>sysdate-60)
order by userid;

SQL Server
select * from maxuser
where status='ACTIVE'
and sysuser=0
and not exists (select userid from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and attemptdate>getdate()-60)
order by userid;

Obviously, the above query works only if you have enabled the login tracking feature in Security Groups - Security Controls.

This is very useful if you want a quick idea about how many users are really accessing the system.
Another interesting use of this query is to optimize Maximo licenses by simply deactivating users that no longer use the system. You can even define an escalation that automatically do the job for you.

January 17, 2017

Long Descriptions in Maximo

Long descriptions in Maximo are a special fields meant to store long text. Unlike all the other fields they are stored in a dedicated table called LONGDESCRIPTION. Tables link to the LONGDESCRIPTION table through their unique ID and the long description attribute is stored in a CLOB field.

Let's change the long description of the work order '1000' with few lines of text.



If we run the following SQL we can retrieve the internal ID of the work order.

select wonum, workorderid, description from workorder where wonum='1000';




Here we are interested to the WORKORDERID field which is the internal numeric identifier of the work order. This value is referenced in the LDKEY of the LONGDESCRIPTION table.
This is how we can retrieve the long description.

select * from LONGDESCRIPTION where ldkey=37 and ldownertable='WORKORDER';




Multiple attributes in the Mbo can have long descriptions. The LDOWNERCOL says which column owns the long description. The LANGCODE allows to have translated versions of the same long description field.

Long description fields can be associated with any attribute in Database Configuration setting the 'Long Description Owner' checkbox.


In the MAXATTRIBUTE record, the ISLDOWNER column is set to 1 if the column has a long description. For each long description owner there is a nonpersistent attribute named +"[ATTRNAME]_LONGDESCRIPTION" that will store the data in the LONGDESCRIPTION table.