sql

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 […]

Learn how to query data with SQL

Many times Maximo users and administrator lacks database skills. These are important in several situations: Write ‘where clause’ statements to filter data in Maximo UI. Develop complex SELECT statements to extract data from multiple tables when developing report. Optimize existing queries to solve performance issues. If you need to improve your SQL skills I suggest […]

Analyze groups to users association

The following SQL query lists all the security groups and the associated users. SELECT maxgroup.groupname, maxgroup.description, maxuser.userid, maxuser.loginid, maxuser.defsiteFROM maxgroupJOIN groupuser ON groupuser.groupname=maxgroup.groupnameJOIN maxuser ON maxuser.userid=groupuser.useridORDER BY maxgroup.groupname, maxuser.userid; The best approach to analyze the results is to load them into Excel and create a pivot table.

Find Roles with assignments in a Workflow

Roles in Maximo are used to represent a specific person or a group of people that can be used in workflows, communication templates and escalations. If you need to find which roles are used in a specific workflow you can use an SQL query like this. SELECT wfnode.title, wfnode.description, wfa.roleid, r.descriptionFROM wfnodeJOIN wfassignment wfa ON […]

Find all custom triggers on Maximo tables

Before Maximo 6 it was a common practice to implement customizations using database triggers. Although Conditional Expressions, SigOptions, Java coding and scripting are now available in Maximo 7, I still see many Maximo environments with a lot of database triggers.In a recent project I have found a Maximo system with more than 30 triggers and […]

How to extract failure codes, problems, causes and remedies

Today a customer asked me to extract the full listing of failure codes, problems, causes and remedies. He wants to be able to create an Excel spreadsheet to analyze the failure codes tree in order to improve and reorganize such structure.Here is the SQL query that can extract this information from Maximo database. select f.failurecode […]

MBO Performance Tip N.8 – If there is no other way… use JDBC

This entry is part of the Java MBO performance optimization golden rules series. Sometimes you have to perform some complex logic or you have to join several tables to retrieve a piece of data. In other cases there is a piece of code where performance is critical. In such cases it may be hard to […]

Scroll to top