Excel Formulas - Helpful in BIRT Reports

 

• BIRT reports are widely used in Maximo and sometimes require to build reports with many columns and field mapping.

• I’ve been using excel formulas to generate scripts for Open, Fetch method

• These are simple text formulas, yet useful for working with reports with many columns.

Open Method:

To Convert 'Select Query' for Open Method

Enter the SQL query as multiple lines in Excel sheet. Example: column A

Apply the formula in column B as below.

For First row only:

=CONCAT("var sqlText=""",A1,"""")

For other rows:

=CONCAT("+ "" ",A2,"""")

=CONCAT("+ "" ",A3,"""")

=CONCAT("+ "" ",A4,"""")

..

...

Note: The above formula does not add/append the params["where"] or other parameters. Manually edit before using it.

Fetch Method:

To Generate Fetch method field mapping scripts.

Copy the BIRT report dataSet field names to column A in excel sheet

Copy the Select Query Field names from 'Open method' to column B in excel sheet

Enter the formulas in Column C as below to generate Script of Fetch method

=CONCAT("row[""",A2,"""] = maximoDataSet.getString(""",B2,""");")

=CONCAT("row[""",A3,"""] = maximoDataSet.getTimestamp(""",B3,""");")

=CONCAT("row[""",A4,"""] = maximoDataSet.getInteger(""",B4,""");")

Extract 'Select Query' from 'Open Method’ sqlText:

Copy the value of sqlText Variable from Open method to 'A1' cell in column in excel sheet.

Apply the below formula in another cell. This will convert the sqlText to executable query.

=SUBSTITUTE(SUBSTITUTE(A1,"+ """,),"""",)

Unlock the Maximo Start Center template via SQL

Maximo Start centers are useful to view different types of user data such as Workflow assignments, Result sets and other portals like Favourite applications, Bulletin board etc.

Each Start center is assigned to specific 'Security group' and Users can be granted 'access to edit' the portal themselves.

·    When a user opens the start center template to edit, a record will be inserted in SCCONFIG table with ‘groupnname as NULL’

·      If any other user tries to edit the same template, will get an error message as below.

·   Start center become locked for other user to edit and must wait until the main user Finish/Cancel the changes

·   In case, the main user is not reachable, or intermittently blocked access, it becomes impossible for others to edit.

·   There is a workaround to this by removing the entries in SCCONFIG, LAYOUT and other portal tables such as RSCONFIG, INBXCONFIG etc.

Here are the detailed steps.

1.  Query the SCCONFIG as below and get the layoutid

select scconfigid,* from scconfig (nolock) where sctemplateid=1601 and groupname is null;

2.  Query the SCCONFIG and LAYOUT tables using inner join to fetch related portal details.

select lo.scconfigid,layoutid,portletid,* from scconfig sc

inner join layout lo on sc.scconfigid=lo.scconfigid

where sc.sctemplateid=<>and sc.groupname is null and portletid<>'BBOARD'

Note: Bulletin board is not editable and no table for BBOARD. Hence it is excluded in the results.

Copy the LayoutIDs of each portal table.

 

3.  Generate and Execute the "Deletion Scripts' to remove entries from SCCONFIG, LAYOUT and Other Portal tables by using scconfigid, layoutid copied from above step.

delete from SCCONFIG where sctemplateid=<> and groupname is null and scconfigid=<scconfigid>;

delete from LAYOUT where scconfigid=<scconfigid>;

delete from ACTIONSCFG where layoutid=<layoutid>;

delete from FACONFIG where layoutid=<layoutid>;

delete from RSCONFIG where layoutid=<layoutid>;

delete from RSCONFIG where layoutid=<layoutid>; 

delete from INBXCONFIG where layoutid=<layoutid>;

Execute the script.

    Now, try opening the star center template, it will be editable.

Power-Shell Script to check Maximo/Websphere Ports

Power-shell script to check port connection of Maximo servers and export result to .csv file.

Overview:

• Maximo server use a variety of ports dedicated to specific function/application.

• Ports at UI level (9080, 9081 etc). or RMI port are obvious and easy to validate. But other ports at NodeAgent, Deployment manager level and few JVM ports that are crucial for health of the application not often checked.

• It is best practice to periodically check connection of these ports. Escpecially during any server upgrade/patching.

• This article shares a method to automate this via powershell script.

• We use 'tnc 'ServerName' -p '<portNumber>' as base code, which is recursively called  against a list of Ports and Servers. The Result is then printed to a .csv file a report.

Click here to view this article in Linked-in

Steps:

Retrieve the Port details from Webspshere for each JVM, Node Agent.

Create a powershell script in any text editor as explained below and save as .ps1 file.(We can execute this manually , or schedule a windows Job.)

• Declare the output file with timestamp:

$today=Get-Date -Format "dd-MM-yyyy-HHmm";

$exportFilePath="C:\Maximo\HealthCheck\Reports\PROD_Server_Ports_Health"+$today+".csv";

• Declare each server names to a variable.

$appSvr01="PRODMAXIMOAP01.orgxx.com"; 
$appSvr02="PRODMAXIMOAP01.orgxx.com"
$adminServer="PRODMAXIMOADM01.orgxx.com"

• Declare list of ports of different type (JVM, Node Agent etc.)

 $JVMPorts="9080","9081","9082","9083";      

 $nodeAgentPorts="2810","9902","9203","9204","7273","8879","9630","7063","11005","11006"; 

$nodeAdminPorts="2810","9902","9203","9204","7273","8880","9630","7063","11007","11008"; 

$dmgrPorts="7277","9809","9632","8879","9100","9402","9060","9043","9352","7060","9420","11005","11006";

• Declare List of Port Types in Seqence

$portTypeList = "JVM","JVM","NodeAgent","NodeAgent","NodeAdmin","Dmgr";

• Now, declare List of all Servers and ports. That is combination of avove variables in correct sequence.

$serverList=$appSvr01,$appSvr02,$appSvr01,$appSvr02,$adminServer,$adminServer

$portListAll = $JVMPorts,$JVMPorts,$nodeAgentPorts,$nodeAgentPorts,$nodeAdminPorts,$dmgrPorts;

The sequence of values in each list should match the value of others. So that, when we itrate through these list, it matches with correct server and port.

Server	Port Type	Port List
AP01	JVM	"9080","9081","9082","9083"
AP01	JVM	"9080","9081","9082","9083"
AP01	NodeAgent	"2810","9902","9203",..."11006"
AP01	NodeAgent	"2810","9902","9203",..."11006"
ADM01	NodeManager	"2810","9902","9203",..."11008"
ADM01	Dmgr	"7277","9809","9632",..."11006"

• Finally, iterate through the list of servers , for each server, test the list of ports and write the result.

Result:

The output of the script will generate .csv file with details as shown below.

Source Code:

$today=Get-Date -Format "dd-MM-yyyy-HHmm";

$exportFilePath="C:\Maximo\HealthCheck\Reports\PROD_Server_Ports_Health"+$today+".csv";

$appSvr01="PRODMAXIMOAP01.orgxx.com"; 

$appSvr02="PRODMAXIMOAP01.orgxx.com"

$adminServer="PRODMAXIMOADM01.orgxx.com"

$JVMPorts="9080","9081","9082","9083"

$nodeAgentPorts="2810","9902","9203","9204","7273","8879","9630","7063","11005","11006";

$nodeAdminPorts="2810","9902","9203","9204","7273","8880","9630","7063","11007","11008"; 

$dmgrPorts="7277","9809","9632","8879","9100","9402","9060","9043","9352","7060","9420","11005","11006";

#Declare combined PortList and ServerList in correct sequence.

#Such that, it matches relevant Port to Server as below

#AP01,AP02,AP01,AP02,ADM01,ADM01

#JVM,JVM,nAgnt,nAgnt,nAdmin,Dmgr

$serverList=$appSvr01,$appSvr02,$appSvr01,$appSvr02,$adminServer,$adminServer

$portListAll = $JVMPorts,$JVMPorts,$nodeAgentPorts,$nodeAgentPorts,$nodeAdminPorts,$dmgrPorts;

$portTypeList = "JVM","JVM","NodeAgent","NodeAgent","NodeAdmin","Dmgr";

$Result=

for ($i=0; $i -lt $serverList.Length; $i++){

$server=$serverList[$i];

$portList=$portListAll[$i];$portType=$portTypeList[$i];

foreach ($port in $portList)

{

$resultHashTable=[Ordered]@{

ServerName=$server

RemoteAddress=''

PortType=$portType

PortNumber=$port

Status=''

}

try{

$tncCheck=tnc $server -p $port 

$resultHashTable.ServerName=$tncCheck.ComputerName

$resultHashTable.RemoteAddress=$tncCheck.RemoteAddress

$resultHashTable.PortType=$portType

$resultHashTable.PortNumber=$tncCheck.RemotePort

$resultHashTable.Status=$tncCheck.TcpTestSucceeded

}

catch{

$resultHashTable.PingStatus='N/A'

$resultHashTable.Status='N/A'

}

[PSCustomObject]$resultHashTable

}

}

$Result|Export-Csv $exportFilePath

 

Dynamic BIRT Report - Utilizing HTML Text

·        BIRT report has ‘Text’ block control, that supports HTML code. This is generally used in formatting report data but can be improved to work like interactive web page.

·    Here is an example of using this feature to call a function (some calculation) and display the result in output column.

·    Same functionality could be achieved in simpler ways. But this document is meant to explain the ’HTML Text’ + ‘JavaScripting’ features of BIRT report.

Click Here for more full document.

Maximo has in-built functionality of processing workflow assignment, updating /creating records using ‘𝐄𝐦𝐚𝐢𝐥 𝐈𝐧𝐭𝐞𝐫𝐚𝐜𝐭𝐢𝐨𝐧’ application. This however, has few drawbacks.

◾ 𝘜𝘴𝘦𝘳 𝘮𝘶𝘴𝘵 𝘮𝘢𝘯𝘶𝘢𝘭𝘭𝘺 𝘵𝘺𝘱𝘦 𝘵𝘩𝘦 𝘙𝘦𝘴𝘱𝘰𝘯𝘴𝘦 𝘤𝘰𝘥𝘦, 𝘵𝘺𝘱𝘪𝘤𝘢𝘭𝘭𝘺 1 𝘰𝘳 2, 𝘵𝘩𝘦𝘯 𝘴𝘦𝘯𝘥 𝘦𝘮𝘢𝘪𝘭 𝘳𝘦𝘴𝘱𝘰𝘯𝘴𝘦. 𝘐𝘧 𝘶𝘴𝘦𝘳 𝘮𝘪𝘴𝘵𝘺𝘱𝘦 𝘰𝘳 𝘢𝘥𝘥 𝘦𝘹𝘵𝘳𝘢 𝘴𝘱𝘢𝘤𝘦 𝘸𝘩𝘪𝘭𝘦 𝘴𝘦𝘯𝘥𝘪𝘯𝘨, 𝘪𝘵 𝘮𝘢𝘺 𝘧𝘢𝘪𝘭.

◾ 𝘈𝘭𝘴𝘰, 𝘵𝘩𝘦 𝘦𝘮𝘢𝘪𝘭 𝘸𝘪𝘭𝘭 𝘩𝘢𝘷𝘦 𝘥𝘦𝘵𝘢𝘪𝘭𝘴 𝘭𝘪𝘬𝘦 𝘓𝘚𝘕𝘙𝘐𝘋, 𝘞𝘍𝘈𝘚𝘚𝘐𝘎𝘕𝘔𝘌𝘕𝘛𝘐𝘋 𝘦𝘵𝘤. 𝘸𝘩𝘪𝘤𝘩 𝘮𝘢𝘺 𝘯𝘰𝘵 𝘣𝘦 𝘱𝘳𝘦𝘧𝘦𝘳𝘢𝘣𝘭𝘦 𝘣𝘺 𝘴𝘰𝘮𝘦 𝘤𝘭𝘪𝘦𝘯𝘵𝘴.

There is an 𝐚𝐥𝐭𝐞𝐫𝐧𝐚𝐭𝐞 way to achieve this via automation script as below. 

1. When a workflow is assigned to specific User/Role, Maximo will send Email containing two 𝐡𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤𝐬 to 𝐀𝐩𝐩𝐫𝐨𝐯𝐞/𝐑𝐞𝐣𝐞𝐜𝐭 the assignment and other details.

2. On 𝐜𝐥𝐢𝐜𝐤 of 𝐡𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤, will create 𝐧𝐞𝐰 𝐄𝐦𝐚𝐢𝐥 with ‘𝐖𝐨𝐫𝐤𝐟𝐥𝐨𝐰 𝐀𝐬𝐬𝐢𝐠𝐧𝐦𝐞𝐧𝐭 𝐈𝐃 𝐚𝐧𝐝 𝐑𝐞𝐬𝐩𝐨𝐧𝐬𝐞 𝐜𝐨𝐝𝐞 (𝑨𝒑𝒑𝒓𝒐𝒗𝒆 𝒐𝒓 𝑹𝒆𝒋𝒆𝒄𝒕)’ 𝐢𝐧 𝐒𝐮𝐛𝐣𝐞𝐜𝐭, then user only need to 𝐜𝐥𝐢𝐜𝐤 ‘𝑺𝒆𝒏𝒅’.

This way,

✔𝘜𝘴𝘦𝘳 𝘥𝘰 𝘯𝘰𝘵 𝘯𝘦𝘦𝘥 𝘵𝘰 𝘵𝘺𝘱𝘦 𝘢𝘯𝘺 𝘳𝘦𝘴𝘱𝘰𝘯𝘴𝘦 𝘤𝘰𝘥𝘦 𝘖𝘙 𝘴𝘦𝘦 𝘢𝘯𝘺 𝘵𝘦𝘤𝘩𝘯𝘪𝘤𝘢𝘭 𝘥𝘢𝘵𝘢 𝘪𝘯 𝘐𝘯𝘣𝘰𝘶𝘯𝘥 𝘌𝘮𝘢𝘪𝘭. 

✔ 𝘕𝘰 𝘯𝘦𝘦𝘥 𝘵𝘰 𝘤𝘰𝘯𝘧𝘪𝘨𝘶𝘳𝘦 𝘌𝘴𝘤𝘢𝘭𝘢𝘵𝘪𝘰𝘯 𝘵𝘰 𝘴𝘦𝘯𝘥 𝘐𝘯𝘵𝘦𝘳𝘢𝘤𝘵𝘪𝘷𝘦 𝘰𝘶𝘵𝘣𝘰𝘶𝘯𝘥 𝘌𝘮𝘢𝘪𝘭𝘴.

Click here for more details.

Custom Email Interation using Automation Script

  SQL Query to get the Auto Script Launch Point Events

• Maximo automation script has different launch points such as Attribute, Object, Action etc. The values like 'Objectname', 'Attributename', 'Launch Point Type' etc. can be viewed via both Maximo application and back end 

• But Object events ('Before Save' After Save' with combination on 'Add/Update/Delete') and Attribute events ('Validate','Run Action' etc.) are stored as number format in the 'OBJECTEVENT' column of SCRIPTLAUNCHPOINT table. 

• This number follow certain sequence and multiplication of factor specific to launch point type. It is quite complex task to identify these values from back end. 

• Here is a simple query to get these values via back end. This is useful for Object and Attribute launch point events.    

SELECT OBJECTEVENT,
CASE 
WHEN LAUNCHPOINTTYPE ='OBJECT' THEN 
	CASE 
	WHEN OBJECTEVENT=1 THEN 'Initialize' 
	WHEN OBJECTEVENT BETWEEN 2 AND 14 THEN
		CASE 
		WHEN OBJECTEVENT/2=1 THEN 'BeforeSave - Add' 
        WHEN OBJECTEVENT/2=2 THEN 'BeforeSave - Update' 
        WHEN OBJECTEVENT/2=3 THEN 'BeforeSave - Add+Update'
		WHEN OBJECTEVENT/2=4 THEN 'BeforeSave - Delete' 
        WHEN OBJECTEVENT/2=5 THEN 'BeforeSave - Add+Delete'
		WHEN OBJECTEVENT/2=6 THEN 'BeforeSave - Update+Delete' 
        WHEN OBJECTEVENT/2=7 THEN 'BeforeSave - Add+Update+Delete'
		END
	WHEN OBJECTEVENT BETWEEN 16 AND 112 THEN
		CASE 
		WHEN OBJECTEVENT/16=1 THEN 'After Save - Add' 
        WHEN OBJECTEVENT/16=2 THEN 'After Save - Update' 
        WHEN OBJECTEVENT/16=3 THEN 'After Save - Add+Update'
		WHEN OBJECTEVENT/16=4 THEN 'After Save - Delete' 
        WHEN OBJECTEVENT/16=5 THEN 'After Save - Add+Delete'
		WHEN OBJECTEVENT/16=6 THEN 'After Save - Update+Delete' 
        WHEN OBJECTEVENT/16=7 THEN 'After Save - Add+Update+Delete'		
		END
	WHEN OBJECTEVENT BETWEEN 128 AND 896 THEN 
		CASE 
		WHEN OBJECTEVENT/128=1 THEN 'After Commit - Add' 
        WHEN OBJECTEVENT/128=2 THEN 'After Commit - Update' 
        WHEN OBJECTEVENT/128=3 THEN 'After Commit - Add+Update'
		WHEN OBJECTEVENT/128=4 THEN 'After Commit - Delete' 
        WHEN OBJECTEVENT/128=5 THEN 'After Commit - Add+Delete'
		WHEN OBJECTEVENT/128=6 THEN 'After Commit - Update+Delete' 
        WHEN OBJECTEVENT/128=7 THEN 'After Commit - Add+Update+Delete'		
		END
	END
WHEN LAUNCHPOINTTYPE='ATTRIBUTE' THEN 
	CASE 
	WHEN OBJECTEVENT=0 THEN 'Validate'
    WHEN OBJECTEVENT=1 THEN 'Run action' 
    WHEN OBJECTEVENT=2 THEN 'Initialize' 
    WHEN OBJECTEVENT=8 THEN 'Initialize Access Restriction'
	WHEN OBJECTEVENT=64 THEN 'Retrieve list'
	END
WHEN OBJECTEVENT IS NULL THEN 'Not Applicable'
END as EventType,* FROM SCRIPTLAUNCHPOINT

The SQL Query is based logic as explained below .

Object event types can be assigned a factor based on the range of 'Object event' value.

Type
Range of values
Factor
Before Save 
 >=2<=14
2
After Save
 >=16<=112
16
After Commit
 >=128<=896
128

Apply the below Formula and get the results specific Sub-event type.
            Value = ObjectEvent / EventFactor

ObjectEvent = SCRIPTLAUNCHPOINT.OBJECTEVENT

EventFactor = Value from above table. (2, 16, 128)

Now, map the output of above formula to 'Sub-Event type' such as Add/Update/Add+Update etc. to get the final result.

Event
Value
Add
    1
Update
    2
Add+Update
    3
Delete
    4
Add+Delete
    5
Update+Delete
    6
Add+Update+Delete
    7
    

Automate Email attachment specific to a record

Automate Email attachment specific to a record
  Maximo can send email communication with attachments, in following ways.

1. By adding a default attachment(s) to a communication template and wherever this template is used, it will send the associated files as well. But this is set to a default document(s) and cannot be specific to record.
2. Other option is using 'Create Communication' option from an application, where user can manually add attachments and send.

This process of sending document specific to a record (like Workorder, PO etc.) OR a Person (like an approver in workflow) can be automated using script.

---

Create an Action Launch Point, add Script, choose script launguage as 'python/jython' and write code as explained below .

• Import the MXServer and SqlFormat APIs; Use MXServer to get the communication template MboSet

• Get the CommTemplate Mbo from MboSet;
  Use CommTemplate.convertSendTo() method to get the email IDs of sendTo, cc,bcc etc; Usually, these details are retrieved from a Role associated to Communication template or Workflow.
  
• Use SqlFormat.resolvecontent() method to replace the values of binding variables in subject, message.
  
• We can use custom relationship to get a specific type of documents. (Example: DOCTYPE='TYPE-A' and ownerid=:workorderid and ownertable='WORKORDER').
  Then, use the default relationship from DOCLINKS to DOCINFO to retrieve document details.(URLNAME, URLTYPE)
  Add the document path values (DOCINFO.URLNAME) to a string array. []
  
  
  
  

• Finally, invoke the MXServer.SendEmail() method with sender, subject, message and attachment details. MXServer.sendEMail(sendTo, cc, bcc, sendFrom, subject, message, replyTo, fileNames, urlNames)
  
  
  
  Source Code:
  https://github.com/sankargh/maximo/blob/main/Maximo_Send-Email-with-Attachments.py