Earlier I provided an example of how to extract and send data as CSV to a user via email. A friend asked me about a requirement he is dealing with. In this case, he has an escalation which sends an email when there is an error with integration. The problem with this approach is if there are many failed transactions, the administrator will receive a lot of emails.

The alternative approach is setting up a scheduled BIRT report which lists all errors in one file. However, this approach also has a problem. On the days when there are no failures, the admin would still receive an email and still have to open the file to see whether there is an error or not.

This is actually a common requirement. Below are some examples:

  • Operation managers like to monitor a list of critical assets. Maximo should send out a maximum of one email per day with the list of active SR and WOs when the asset is down. Do not send emails if there are no issues.
  • Operators like to receive a list of all high-priority work orders reported daily in one email, such as work orders that deal with water quality issues or sewer overflow.
  • The system admin wants to get a list of suspicious login activities daily.
  • System owners like to monitor data quality issues. Only send out a report if there are issues.

Below is an example of how we extract all P1 work orders in site BEDFORD, and save the data to an Excel file. I didn’t include the code to attach the file and send out an email as it has already been provided in my previous post

from psdi.server import MXServer
from psdi.mbo import MboConstants
from java.io import FileOutputStream
from org.apache.poi.xssf.usermodel import XSSFWorkbook
def createHeaderRow(row, fields):
for i in range(len(fields)):
row.createCell(i).setCellValue(fields[i])
def createDataRow(row, mboRecord, fields):
for i in range(len(fields)):
row.createCell(i).setCellValue(mboRecord.getString(fields[i]))
def buildExcelFile(mboSet, fields):
workbook = XSSFWorkbook()
sheet = workbook.createSheet("Sheet1")
createHeaderRow(sheet.createRow(0), fields)
rowIndex = 1
mboRecord = mboSet.moveFirst()
while mboRecord:
createDataRow(sheet.createRow(rowIndex), mboRecord, fields)
rowIndex += 1
mboRecord = mboSet.moveNext()
return workbook
def main():
mboSet = MXServer.getMXServer().getMboSet("WORKORDER", MXServer.getMXServer().getSystemUserInfo())
mboSet.setFlag(MboConstants.DISCARDABLE, True)
mboSet.setWhere("istask = 0 and historyflag = 0 and siteid = 'BEDFORD' and wopriority = 1")
mboSet.setOrderBy("reportdate desc")
mboSet.reset()
if mboSet.count() == 0:
return "Script result: No records found"
fields = ["WONUM", "DESCRIPTION", "WOPRIORITY", "ASSETNUM", "ASSET.DESCRIPTION","STATUS", "REPORTDATE", "REPORTEDBY.DISPLAYNAME"]
workbook = buildExcelFile(mboSet, fields)
mboSet.close()
try:
outputStream = FileOutputStream("C:\Temp\output.xlsx")
workbook.write(outputStream)
return "Script result: Success "
except Exception, e:
return "Script result: Failed. " + str(e)
### Script entry point ###
responseBody = main()

As usual, I test the script by calling it via API. Below is how the data looks when opened in Excel.

For data aggregation or when complex joins are required, we can also run an SQL query to retrieve data. Below is an example that provides a list of locations and the total number of work orders for each location.

from psdi.server import MXServer
from psdi.mbo import MboConstants
from java.io import FileOutputStream
from org.apache.poi.xssf.usermodel import XSSFWorkbook
def buildExcelFile(rs):
workbook = XSSFWorkbook()
sheet = workbook.createSheet("Sheet1")
# Headers
rowIndex = 0
row = sheet.createRow(rowIndex)
row.createCell(0).setCellValue("Location")
row.createCell(1).setCellValue("NoOfWorkOrders")
# Data Rows
rsMetaData = rs.getMetaData()
colCount = rsMetaData.getColumnCount()
while rs.next(): # Iterate through each row
rowIndex += 1
row = sheet.createRow(rowIndex)
for i in range(colCount): # Iterate through each column
row.createCell(i).setCellValue(rs.getString(i+1))
return workbook
def main():
mxs = MXServer.getMXServer()
connKey = mxs.getSystemUserInfo().getConnectionKey()
conn = mxs.getDBManager().getConnection(connKey)
sql = "SELECT coalesce(location, 'N/A') as Location, count(1) as NoOfWorkOrders" \
+ " FROM workorder" \
+ " WHERE istask = 0 AND historyflag = 0 AND siteid = 'BEDFORD'" \
+ " GROUP BY location " \
+ " ORDER BY location"
try:
stmt = conn.createStatement()
rs = stmt.executeQuery(sql)
workbook = buildExcelFile(rs)
rs.close()
stmt.close()
outputStream = FileOutputStream("C:\Temp\output.xlsx")
workbook.write(outputStream)
return "Script result: Success "
except Exception, e:
return "Script result: Failed. " + str(e)
### Script entry point ###
responseBody = main()

Below is the data exported by the script