Run SQL directly from Automation script - Calling a Script Using Rest API

1.create a Script RUNSQL with below code

2. all this script with the sql in the end for instance:

https://[MAXIMO_URL]/maximo/oslc/script/runsql?method=SELECT&sql=SELECT assetnum,description,status FROM asset WHERE status='OPERATING' limit 5

from psdi.server.MXServer import getMXServer as MXS

def getHeader(rs):

    headerStr = ""    

    for i in range(rs.getMetaData().getColumnCount()):

        headerStr = headerStr + rs.getMetaData().getColumnName(i+1) + ","

    headerStr = headerStr[:-1] + "\r\n"                     # Remove the last "," and add a line break

    return headerStr

def getData(rs):

    dataStr = ""

    while (rs.next()):                                      #Loop through rows

        for i in range(rs.getMetaData().getColumnCount()):  #Loop through columns

            dataStr = dataStr + (rs.getString(i+1) or 'null') + ","

        dataStr = dataStr[:-1] + "\r\n"                     # Remove the last "," and add a line break

    return dataStr

### MAIN ###

connKey = MXS().getSystemUserInfo().getConnectionKey()

conn = MXS().getDBManager().getConnection(connKey)

method = request.getQueryParam("method").upper()            # SELECT or UPDATE,DELETE,INSERT

sql = request.getQueryParam("sql").upper()

stmt = conn.createStatement()

try:

    if method != 'SELECT':

        stmt.execute(sql)

        responseBody = "Done"

    else:

        rs = stmt.executeQuery(sql)

        headerStr = getHeader(rs)

        dataStr = getData(rs)

        responseBody = headerStr + dataStr

        rs.close()

finally:

    stmt.close()

    conn.commit()

    MXS().getDBManager().freeConnection(connKey)

#    https://[MAXIMO_URL]/maximo/oslc/script/runsql?method=SELECT&sql=SELECT assetnum,description,status FROM asset WHERE status='OPERATING' limit 5

######

maximo-runsql-automation/runsql.py at main · abdulqadeerel/maximo-runsql-automation

Asset Deletion Query Sql | IBM Maximo

To delete asset totally from system including all related tables in Maximo System.

Single Asset Delete Query Example:

delete from AMCREWTOOL where ASSETNUM='WAP-0071';

delete from AMCREWWOTL  where ASSETNUM='WAP-0071';

delete from AREASAFFECTED where AFFECTEDASSETNUM='WAP-0071';

delete from ASSET  where ANCESTOR='WAP-0071';

delete from ASSET  where ASSETNUM='WAP-0071';

delete from ASSET  where PARENT='WAP-0071';

delete from ASSET where PLUSTALIAS='WAP-0071';

delete from ASSETANCESTOR  where ANCESTOR='WAP-0071';

delete from ASSETANCESTOR  where ASSETNUM='WAP-0071';

delete from ASSETAUDIT  where ASSETNUM='WAP-0071';

delete from ASSETCALIBRATION  where ASSETNUM='WAP-0071';

delete from ASSETFEASPECHIST  where ASSETNUM='WAP-0071';

delete from ASSETFEATURE  where ASSETNUM='WAP-0071';

delete from ASSETFEATUREHIST  where ASSETNUM='WAP-0071';

delete from ASSETFEATURESPEC  where ASSETNUM='WAP-0071';

delete from ASSETHIERARCHY  where ASSETNUM='WAP-0071';

delete from ASSETHIERARCHY  where PARENT='WAP-0071';

delete from ASSETHISTORY  where ASSETNUM='WAP-0071';

delete from ASSETLOCCOMM  where ASSETNUM='WAP-0071';

delete from ASSETLOCRELATION  where SOURCEASSETNUM='WAP-0071';

delete from ASSETLOCRELATION  where TARGETASSETNUM='WAP-0071';

delete from ASSETLOCRELHIST  where SOURCEASSETNUM='WAP-0071';

delete from ASSETLOCRELHIST  where TARGETASSETNUM='WAP-0071';

delete from ASSETLOCUSERCUST  where ASSETNUM='WAP-0071';

delete from ASSETMETER  where ASSETNUM='WAP-0071';

delete from ASSETMNTSKD  where ASSETNUM='WAP-0071';

delete from ASSETOPSKD  where ASSETNUM='WAP-0071';

delete from ASSETSPEC  where ASSETNUM='WAP-0071';

delete from ASSETSPECHIST  where ASSETNUM='WAP-0071';

delete from ASSETSTATUS  where ASSETNUM='WAP-0071';

delete from ASSETTOPOCACHE  where SOURCEASSETNUM='WAP-0071';

delete from ASSETTOPOCACHE  where TARGETASSETNUM='WAP-0071';

delete from ASSETTRANS  where ASSETNUM='WAP-0071';

delete from ASSETTRANS  where FROMPARENT='WAP-0071';

delete from ASSETTRANS  where TOPARENT='WAP-0071';

delete from ASSETWORKZONE  where ASSETNUM='WAP-0071';

delete from AUTOATTRUPDATE where ASSET='WAP-0071';

delete from CI  where ASSETNUM='WAP-0071';

delete from COLLECTDETAILS  where ASSETNUM='WAP-0071';

Find Records Between Specific DateTime Range | SQL

This Query is written in DB2 with Maximo on Workorder table, but I believe its the same for almost all.

SELECT w.reportdate
,to_date(to_char(CURRENT timestamp-1,'YYYY-MM-DD')||' 05:00:00','YYYY-MM-DD HH24:MI:SS' ) yesterday
,to_date(to_char(CURRENT timestamp,'YYYY-MM-DD')||' 05:00:00','YYYY-MM-DD HH24:MI:SS' ) today
FROM WORKORDER W
WHERE 1=1
--FETCH FIRST 10 ROWS ONLY
AND W.reportdate BETWEEN to_date(to_char(CURRENT timestamp-1,'YYYY-MM-DD')||' 05:00:00','YYYY-MM-DD HH24:MI:SS' ) AND to_date(to_char(CURRENT timestamp,'YYYY-MM-DD')||' 05:00:00','YYYY-MM-DD HH24:MI:SS' )

for Oracle: Use Sysdate instead of Current Timestamp

Top N rows in a group by using row_number function | SQL

Let say I have this below data in my table for instance:

and my desired output is as below: Group it by WONUM but also I want to see the 2 records of each group.

How to Delete Custom Applications from Maximo | IBM Maximo

Note: First of all shut down application server before going to delete an application.

run these SQL commands one by one:

delete from maxapps where app='CSPINSPECTION';
delete from maxpresentation where app='CSPINSPECTION';
delete from sigoption where app='CSPINSPECTION';
delete from applicationauth where app='CSPINSPECTION';
delete from maxlabels where app='CSPINSPECTION';
delete from maxmenu where moduleapp='CSPINSPECTION' and menutype!='MODULE';
delete from maxmenu where elementtype='APP' and keyvalue='CSPINSPECTION';
delete from appdoctype where app= 'CSPINSPECTION';
delete from sigoptflag where app='CSPINSPECTION';
delete from wfapptoolbar where appname='CSPINSPECTION';

Now just start the application server, deleted application is now longer available. Wasn't that simple.

if you added any system XMLs for your app (like lookups), then you will need to export the System XML and manually remove them and then re-import. (Or just import the copy of the system XML you should have made before you started. 

Maximo Safetyplan and associated Hazard n Precautions SQL | IBM Maximo

select
      sp.safetyplanid
      , sp.description
      , h.hazardid
      , h.description
      , p.precautionid
      , P.description
      , p.siteid
  from
      safetyplan sp
  join
      spworkasset spwa
      on sp.safetyplanid = spwa.safetyplanid
  join
      splexiconlink spll
      on spwa.spworkassetid = spll.spworkassetid