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.

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

select * from maxuser
where status='ACTIVE'
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 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.

December 28, 2016

Improving Assets and Locations drilldown dialog

The Assets/Locations drilldown dialog is one of the most longstanding and criticized features of Maximo.

This dialog is almost unchanged from release 6.1 and definitely lacks some important capabilities to provide a unified view of assets and location hierarchy. However, we can make few minor improvements to the standard dialog to make it slightly better.

Increase size of the dialog

I think the most usability improvement is to increase the size of the dialog allowing to see more elements in the drilldown. The default size is definitely too small for the typical modern screen resolutions.
Unfortunately there is no system setting for this and we have to tweak the dialog definition in the library.
Open the Application Designer and select the Export System XML action from the menu. Click on the small arrow for the LIBRARY row to export the file. Open the XML file with a good text editor and search for psdi.webclient.beans.common.DrilldownTreeBean.
There are two occurrences of this string in the file inside the 'drilldown' dialog definition. One is for the Locations tab and one is for Assets tab.
Change the height attribute increasing it from the default 150 to 350 or 400.

<tree id="locations_tree" beanclass="psdi.webclient.beans.common.DrilldownTreeBean"
width="500" height="350" checkfornewnodes="true"
ignorestructurechange="false" orderby="location asc" relationship="LOCATIONS" selectfirstnode="true">

Now in the Application Designer app, click on the Import Application Definition to import the modified LIBRARY.xml file.

You can see from the screenshot below how much easier it is to get a grasp of the location hierarchy with the new larger dialog.

Increase child records limit

Another small improvement that can be made is to improve the default limit of child records that can be displayed. The default value is set in the control-registry.xml file and is typically set to 50 which is too low.
To override the default value, just add the maxchildren attribute and specify a larger value like 500. This is how the locations_tree definition should look like.

<tree id="locations_tree" beanclass="psdi.webclient.beans.common.DrilldownTreeBean"
width="500" height="350" maxchildren="500" checkfornewnodes="true"
ignorestructurechange="false" orderby="location asc" relationship="LOCATIONS" selectfirstnode="true">

For more information refer to this post.

November 29, 2016

Tracking Changes using Audit feature

A common requirement in Maximo is to track changes made by users on a specific object (database table). In Maximo terms this is called 'Audit Tracking'.
In this post I will describe how to enable audit tracking for a specific set of attributes of an object and how to display the updates in a separate tab of the corresponding application.
I will use the Assets application for this tutorial and here is the final outcome I have achieved.

There are three configurations to be implemented for this:
  1. Enable audit of the main object and select attributes to be audited
  2. Define relationship to the audit table
  3. Customize the application to display the audit log from the UI

Enable audit

First step is to enable the auditing of the table you want to monitor.
Open the Database Configuration application, select ASSET object and check the Audit Enabled flag.

You now have to select which fields will trigger the audit record. Switch to the Attributes tab and select the Enable Auditing flag for STATUS, SERIALNUM, LOCATION, DESCRIPTION fields.

Enable admin mode, apply configuration changes and disable admin mode.
A new database table A_ASSET will be created and, from now on, all the changes to the audited fields will be traced in the audit table.

Define relationship

In order to be able to display the audit records we have to create a relationship from the ASSET table to the A_ASSET table.
Open the Database Configuration application, select ASSET object and in the Relationships tab create the following relationship.
  • Relationship: A_ASSET
  • Child Object: A_ASSET
  • Where Clause: assetnum=:assetnum and siteid=:siteid

Customize application

Now you can use the relationship you have just created to display records from the child A_ASSET audit table in the Asset application.
Open Application Designer application, select the ASSET application and export the app's XML definition.
Backup the file and edit it with a text editor. Paste the text below before the last </tabgroup> before the </clientarea> tag.

<tab id="myhist" label="History">
  <section id="myhist_grid1">
    <sectionrow id="myhist_grid1_11">
      <sectioncol id="myhist_grid1_11_1">
        <section id="myhist_grid1_11_1_1">
          <multiparttextbox dataattribute="assetnum" descdataattribute="description" id="myhist_grid1_1"/>
  <table id="myhist_t1" inputmode="readonly" label="Asset history" orderby="EAUDITTIMESTAMP desc" relationship="A_ASSET">
    <tablebody displayrowsperpage="10" id="myhist_t1_tb">
      <tablecol dataattribute="SERIALNUM" id="myhist_t1_SERIALNUM"/>
      <tablecol dataattribute="STATUS" id="myhist_t1_STATUS"/>
      <tablecol dataattribute="LOCATION" id="myhist_t1_LOCATION"/>
      <tablecol dataattribute="DESCRIPTION" id="myhist_t1_DESCRIPTION"/>
      <tablecol dataattribute="EAUDITUSERNAME" id="myhist_t1_EAUDITUSERNAME" label="Changed By"/>
      <tablecol dataattribute="EAUDITTIMESTAMP" id="myhist_t1_EAUDITTIMESTAMP" label="Changed Date"/>