April 26, 2019

Where was an asset located at a certain time in the past?

Maximo has a well known feature to display the history of where an asset was located. It is accessible from View Asset Move History from the Asset application.
In the following example the asset DS01-A-002 was created in location DS01-S-SA-01 and then moved to other two locations.



The data displayed in this dialog is stored in the ASSETTRANS database table.
By querying this table we can answer questions like this:
  • Where was my asset located at a specific point in time?
  • What assets were in a specific location at a specific point in time?

Now lets start reading date from the ASSETTRANS table for asset DS01-A-002. We will get the same results displayed in the dialog above.

select assetnum, toloc, datemoved
from assettrans
where assetnum='DS01-A-002'
order by datemoved;


If we want to know where the asset was the 20th of April we need to know the largest DATEMOVED before that date.

select assetnum, max(datemoved)
from assettrans
where datemoved<TO_TIMESTAMP('2019-04-20', 'YYYY-MM-DD')
and assetnum='DS01-A-002'
group by assetnum;


Now we can join this result in the following way to retrieve the list of asset's locations 'as of' 20th of April at 11AM.

select a.assetnum, a.siteid, a.toloc
from assettrans a
join
(select assetnum, siteid, max(datemoved) d
 from assettrans
 where datemoved<TO_TIMESTAMP('2019-04-20 11:00:00', 'YYYY-MM-DD HH24:MI:SS')
 group by assetnum, siteid
) h on h.assetnum=a.assetnum and h.siteid=a.siteid and h.d=a.datemoved
order by a.assetnum;



We can now see that asset DS01-A-002 was located in DS01-S-SA-02 as expected. The query includes all the assets and can be filtered by asset or location.

Note that in this last query we have added the SITEID field since ASSETNUM/SITEID is the primary key of the ASSET table.

April 3, 2019

Logging in automation scripts

I have to admit, I'm a big fan of automation scripts.
Recent updates released with Maximo 7.6.0.9 have almost covered all the possible customization needs including library scripts, REST APIs, MIF processing, before/after save event. All the new features are well documented in this somewhat secret document.

However whatever nice feature will be added in the next feature pack, we will never have a debugger like we have with Java and Eclipse. This could be a serious problem when you have to develop and maintain a complex set of scripts in your environment.
The best way to mitigate such problem is to correctly use Maximo logging APIs.

In my projects I usually define a custom logger in the Logging application. It's a good practice to give a short name of your customer. In this example I have defined mxdev logger for my MaximoDev customer.



I can now log messages to this custom logger using Maximo APIs. In the following example I first import the MXLoggerFactory class, then I get the reference to my mxdev logger and then I use the MxLogger APIs to write messages in the system log.


from psdi.util.logging import MXLoggerFactory

logger = MXLoggerFactory.getLogger("maximo.mxdev")

logger.info("Entering SR_INIT script for ticket " + mbo.getString("TICKETID"))
logger.debug("This is a debug message")


There are several good reasons for doing that.
  1. I can select the logging level of my scripts dynamically in the Logger applications. I typically have that set to DEBUG in development and test environment. In production I set that to WARN or INFO to reduce verbosity.
  2. Logging is always a good way of commenting the code.
  3. It allows me to search for a specific string in the scripts when I see strange behaviors in the logs.

March 18, 2019

Avoiding FetchResultStopLimit errors in Java or scripts

Today I had to face an issue deleting a large set of records from a Maximo table using an automation script. My script was something like this.

mboSet = MXServer.getMXServer().getMboSet("MYTABLE", mbo.getUserInfo())
mboSet.deleteAll()
mboSet.save()

Unfortunately the table I had to purge (MYTABLE in the example) was having more than 5000 rows so when I launched the script I got the following error.
BMXAA7387E - While attempting to retrieve 5001 of MYTABLE, the operation was terminated because the preset limit 5000 was exceeded for retrieving MYTABLE into a single set. Reduce the number of selected objects for the operation.
The simple solution would have been to increase the mxe.db.fetchResultStopLimit Maximo system parameter as documented in this post but I was looking for a more elegant solution.
Searching a little in the Maximo Java code i discovered the MboSet.setLogLargFetchResultDisabled() method that was exactly what I was looking for. Calling this method before the bulk operation will disable logging of large fetch result set to avoid FetchResultLogLimit errors. As a positive side effect it also increased the performances a lot.

The updated script now works like a charm.

mboSet = MXServer.getMXServer().getMboSet("MYTABLE", mbo.getUserInfo())
scSet.setWhere(where)
# disable logging of large fetch result set to avoid FetchResultLogLimit errors
mboSet.setLogLargFetchResultDisabled(True)
mboSet.deleteAll()
mboSet.save()

November 28, 2018

Automation Script to reset user's Start Centers

In a previous post I have explained how to force the reload of a the start center for a specific group of users.
I have now developed a useful automation script that can be invoked from the Security Group application to automatically perform this task with just one click.
Register the script as a Script with an Action Launch Point and link it to a sigoption and to a menuaction, toolbar icon or application button as described in this post or this one.


# Reset users Start Centers
# Object: MAXGROUP
# Resets the Start Centers for all users assigned to the selected Security Group
# Should be called when all users are logged off so the start center is reloaded after the login
# See: http://maximodev.blogspot.com/2012/11/how-to-reset-users-start-centers.html

from psdi.server import MXServer
from psdi.mbo import MboConstants
from psdi.util.logging import MXLoggerFactory


def deleteSc(objectName, where):
 scSet = MXServer.getMXServer().getMboSet(objectName, mbo.getUserInfo())
 scSet.setWhere(where)
 logger.debug("Deleting rows from " + objectName)
 # disable logging of large fetch result set to avoid FetchResultLogLimit errors
 scSet.setLogLargFetchResultDisabled(True)
 scSet.deleteAll()
 scSet.save()


logger = MXLoggerFactory.getLogger("maximo.maximodev")
logger.debug("Entering ASSET_INIT script")

logger.info("Entering ASTS_RESET_STARTCENTER")
grpname = mbo.getString("GROUPNAME")

where1 = "scconfigid IN (SELECT scconfigid FROM scconfig WHERE groupname='" + grpname + "')"
where2 = "layoutid IN (SELECT layoutid FROM layout WHERE " +where1+ ")"


deleteSc("RSCONFIG", where2)
deleteSc("FACONFIG", where2)
deleteSc("INBXCONFIG", where2)
deleteSc("KPILCONFIG", where2)
deleteSc("KPIGCONFIG", where2)
deleteSc("ACTIONSCFG", where2)
deleteSc("PORTLETDISPLAY", where2)
deleteSc("LAYOUT", where2)
deleteSc("SCCONFIG", where1)