Your Asset Maintenance Data Template
Your Asset Maintenance Data Template
- Comprehensive list of maintenance attributes
- Critical process milestones to track
- Detailed technical extraction guidance
Asset Maintenance Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Activity | The specific event or status change that occurred in the work order lifecycle. | ||
| Description This attribute represents the step performed in the maintenance process, such as 'Work Order Approved' or 'Labor Recorded'. In IBM Maximo, this is typically derived from status changes in the history tables or specific transaction logs like labor reporting. It forms the nodes of the process map, allowing the visualization of the sequence of steps. By analyzing these values, organizations can identify process variants, loops, and deviations from the standard maintenance procedure. Why it matters It defines the 'what' of the process, essential for building the process map and understanding workflow execution. Where to get Table: WOSTATUS (Column: STATUS) or WOLOG Examples APPRINPRGCOMPCLOSEWMATL | |||
| Event Timestamp EventTimestamp | The specific date and time when the activity occurred. | ||
| Description This attribute records the exact moment an event took place, such as when a status changed or labor was logged. It provides the temporal dimension necessary for all performance analysis, including lead times and duration calculations. Accurate timestamps are critical for calculating the 'Mean Planning and Approval Lead Time' and ensuring the correct sequencing of events. In Maximo, this is usually the change date of the status record. Why it matters Required to order events chronologically and calculate all time-based KPIs. Where to get Table: WOSTATUS, Column: CHANGEDATE Examples 2023-10-12T08:30:00Z2023-10-12T14:15:00Z2023-10-13T09:00:00Z | |||
| Maintenance Work Order WorkOrderNumber | The unique alphanumeric identifier for the maintenance work order. | ||
| Description This attribute serves as the central case identifier for the maintenance process. It uniquely distinguishes each work order within the IBM Maximo system, linking all related activities, labor transactions, and material usage to a single executed job. In process mining analysis, this ID is used to group individual events into a cohesive case. It allows analysts to track the end-to-end lifecycle of a maintenance task from the initial request creation through approval, execution, and final administrative closure. Why it matters It is the fundamental key for reconstructing the process flow and enables the tracking of specific jobs across departments. Where to get Table: WORKORDER, Column: WONUM Examples WO100234WO100235CM-99281PM-11002 | |||
| Last Data Update LastDataUpdate | The timestamp when the data was extracted or last refreshed. | ||
| Description Indicates when the record was last processed or extracted for process mining. This helps in assessing data freshness and reliability, ensuring that the analysis reflects the most current state of the maintenance operations. This attribute is essential for incremental data loads and for validating that dashboards are displaying up-to-date information regarding work order status and backlog. Why it matters Critical for understanding data latency and freshness. Where to get System time at extraction Examples 2023-11-01T00:00:00Z2023-11-01T12:00:00Z | |||
| Source System SourceSystem | The name of the system where the data originated. | ||
| Description Identifies the source application for the data record. In this context, it will typically be 'IBM Maximo'. This is particularly useful when combining data from multiple maintenance systems or integrating with ERP data. It allows analysts to filter the view by system of record if the process mining project spans a complex system landscape involving multiple CMMS instances. Why it matters Ensures data lineage and traceability in multi-system environments. Where to get Hardcoded during extraction Examples IBM MaximoMaximo PRODMaximo Legacy | |||
| Actual Finish Date ActualFinishDate | The date and time when the physical work was completed. | ||
| Description Records when the maintenance task was technically completed. This is distinct from the administrative closure of the ticket. Comparing this date to the 'Target Completion Date' allows for the calculation of SLA compliance. It is used in the 'Work Order Administrative Lead Time' dashboard to measure the lag between finishing the job and closing the paperwork in the system. Why it matters Marks the end of the technical execution phase. Where to get Table: WORKORDER, Column: ACTFINISH Examples 2023-10-15T16:00:00Z2023-10-16T10:30:00Z | |||
| Actual Labor Hours ActualLaborHours | The total actual hours spent by all technicians on the work order. | ||
| Description The aggregation of all labor time logged against the work order. This metric is the reality check for resource utilization. It supports the 'Technician Utilization Variance' KPI. It is used to calculate the cost of labor and to refine future job plans by providing historical data on how long specific tasks actually take to execute. Why it matters The primary metric for labor cost and efficiency analysis. Where to get Table: WORKORDER, Column: ACTLABHRS Examples 2.55.012.0 | |||
| Asset Criticality AssetCriticality | A score indicating how important the asset is to business operations. | ||
| Description A classification (usually 1-10 or A/B/C) residing on the Asset record that indicates the consequence of failure. This must be joined to the Work Order view. This attribute is mandatory for the 'Critical Asset SLA Performance' dashboard. It ensures that analysis focuses on what matters most—delays on a critical generator are weighed more heavily than delays on a breakroom coffee machine. Why it matters Allows segmentation of process performance by business risk. Where to get Table: ASSET, Column: PRIORITY (Joined via ASSETNUM) Examples 1510 | |||
| Asset Number AssetNumber | Unique identifier of the equipment or asset being maintained. | ||
| Description The specific machine, vehicle, or facility component that is the subject of the maintenance work. This connects the process data to the physical asset hierarchy. It is vital for identifying 'bad actors'—assets that fail frequently. Used in the 'Maintenance Quality and Rework Rates' analysis, this attribute allows for the aggregation of work orders by asset to calculate mean time between failures (MTBF) and identify chronic reliability issues. Why it matters Links process execution to physical infrastructure performance. Where to get Table: WORKORDER, Column: ASSETNUM Examples PUMP-101HVAC-02FLEET-99 | |||
| Assigned Technician AssignedResource | The specific person or lead technician assigned to execute the work. | ||
| Description Identifies the individual or lead technician responsible for the work order. This can be found in the 'Lead' field or derived from labor assignments. It enables the 'Resource and Contractor Productivity' dashboard. Analyzing this attribute helps identify workload imbalances in the 'Maintenance Workload Distribution' view and allows for the comparison of efficiency between different technicians or crews. Why it matters Key for analyzing workforce productivity and balancing workload. Where to get Table: WORKORDER, Column: LEAD (or from ASSIGNMENT table) Examples JSMITHBPATELMRODRIGUEZ | |||
| Current Status Status | The current lifecycle state of the work order. | ||
| Description The current administrative status of the work order (e.g., 'APPR', 'WAPPR', 'COMP'). While the 'Activity' attribute captures the history of changes, this attribute captures the final known state. It is useful for filtering the dataset to show only 'Open' orders vs 'Closed' orders, supporting the 'Maintenance Workload Distribution' analysis. Why it matters Provides a snapshot of the current workload and backlog. Where to get Table: WORKORDER, Column: STATUS Examples APPRCLOSEINPRG | |||
| Priority Priority | The urgency level assigned to the work order. | ||
| Description A numerical or categorical value indicating the importance and urgency of the work order. In Maximo, this is typically a number where lower numbers often indicate higher urgency (e.g., 1 = Emergency). This attribute is used to prioritize work in the backlog and is essential for the 'Critical Asset SLA Performance' dashboard. It helps determine if high-priority work is genuinely being treated with the required speed compared to low-priority tasks. Why it matters Enables analysis of whether the organization is focusing resources on the most urgent tasks. Where to get Table: WORKORDER, Column: WOPRIORITY Examples 1234 | |||
| Target Completion Date TargetCompletionDate | The scheduled or required deadline for the work order. | ||
| Description The date by which the work order is expected to be finished. This is often calculated based on the priority and the generation date. It serves as the benchmark for the 'Critical Asset SLA Performance' dashboard. By comparing this field with the 'Actual Finish Date', analysts can determine on-time performance rates and identify which asset categories frequently miss their maintenance windows. Why it matters The baseline for measuring SLA adherence and schedule compliance. Where to get Table: WORKORDER, Column: TARGCOMPDATE Examples 2023-10-20T17:00:00Z2023-10-25T08:00:00Z | |||
| Work Order Type WorkType | Categorizes the work order as Preventive (PM), Corrective (CM), or Emergency (EM). | ||
| Description This attribute classifies the nature of the maintenance task. Common values in Maximo include PM (Preventive Maintenance), CM (Corrective Maintenance), and EM (Emergency Maintenance). This classification is the foundation for the 'Proactive Versus Reactive Maintenance' dashboard. By filtering on this attribute, analysts can calculate the 'Emergency Maintenance Ratio' KPI and identify shifts in maintenance strategy from reactive firefighting to planned reliability work. Why it matters Distinguishes between planned and unplanned work, a key indicator of maintenance maturity. Where to get Table: WORKORDER, Column: WORKTYPE Examples PMCMEMCPMOD | |||
| Estimated Labor Hours EstimatedLaborHours | The planned amount of labor time required for the work order. | ||
| Description The total number of hours estimated to complete the task during the planning phase. This is compared against 'Actual Labor Hours' in the 'Labor Estimation Accuracy Dashboard'. Large variances between this value and the actuals indicate either poor planning, lack of standard operating procedures, or unexpected asset conditions requiring more work than anticipated. Why it matters Essential for evaluating planning accuracy and resource forecasting. Where to get Table: WORKORDER, Column: ESTLABHRS Examples 2.04.58.0 | |||
| Failure Code FailureCode | Standardized code describing why the asset failed. | ||
| Description A structured code selected by the technician to categorize the cause of the failure (e.g., 'Wear', 'Electrical', 'Operator Error'). This is vital for Root Cause Analysis (RCA). Aggregating these codes helps engineering teams identify systemic issues across the asset base, driving the 'Maintenance Quality and Rework Rates' analysis and informing changes to preventive maintenance strategies. Why it matters The primary data point for reliability engineering and failure analysis. Where to get Table: WORKORDER, Column: FAILURECODE Examples LEAKOVERHEATVIBRATION | |||
| Is Emergency IsEmergency | Flag indicating if the work order is an emergency. | ||
| Description A boolean flag calculated based on the Work Type or Priority. If Work Type is 'EM' (Emergency) or Priority is 1, this is true. This simplified attribute allows for easy filtering in dashboards to isolate the 'Emergency Maintenance Ratio' without complex logic in the visualization layer. Why it matters Simplifies filtering for reactive maintenance analysis. Where to get Calculated from WORKTYPE Examples truefalse | |||
| Is SLA Breached IsSlaBreached | Flag indicating if the actual finish date exceeded the target date. | ||
| Description A boolean calculated field that compares 'Actual Finish Date' with 'Target Completion Date'. If Actual > Target, the value is true. This pre-calculated metric simplifies the 'Critical Asset SLA Performance' dashboard, allowing for an immediate count of failed SLAs without runtime date math. Why it matters Instant visibility into performance compliance. Where to get Calculated from ACTFINISH and TARGCOMPDATE Examples truefalse | |||
| Location Location | The functional location or physical site where the work is performed. | ||
| Description Specifies the physical area or functional location code where the asset resides. This is broader than the specific asset number and helps in geographic or zonal analysis. Used in the 'Maintenance Workload Distribution' dashboard to visualize hotspots of maintenance activity and to plan logistics for technicians moving between sites. Why it matters Provides geospatial context for logistics and resource distribution. Where to get Table: WORKORDER, Column: LOCATION Examples BRILER-RMPLANT-AOFFICE-1 | |||
| Reported Date ReportedDate | The date and time the issue was first reported or the request was created. | ||
| Description The timestamp indicating when the maintenance need was first identified and entered into the system. This serves as the true start of the customer experience timeline. It is used to calculate the 'Mean Planning and Approval Lead Time' by measuring the duration from this reported date to the time work actually commences. It helps assess the responsiveness of the maintenance organization. Why it matters Establishes the starting line for the entire maintenance lifecycle responsiveness. Where to get Table: WORKORDER, Column: REPORTDATE Examples 2023-10-10T08:00:00Z2023-10-10T09:15:00Z | |||
| Site ID SiteId | The high-level site identifier for multi-site Maximo implementations. | ||
| Description In large organizations, Maximo is often partitioned by 'Site'. This attribute distinguishes between different plants or facilities at the database level. It is essential for benchmarking performance across different business units in the 'Standard Maintenance Process Compliance' analysis, ensuring that comparisons are made within the correct operational context. Why it matters Crucial for scoping data in multi-site deployments. Where to get Table: WORKORDER, Column: SITEID Examples BEDFORDNASHUATEXAS | |||
| Total Actual Cost TotalActualCost | The sum of labor, material, service, and tool costs for the work order. | ||
| Description Represents the total financial impact of the maintenance task. In Maximo, this is the sum of various cost components (Labor + Material + Services + Tools). This attribute allows for cost-based process mining, where process inefficiencies (like delays or rework) can be directly correlated with financial loss. It helps identifying the most expensive maintenance types or asset classes. Why it matters Links operational activities to financial outcomes. Where to get Table: WORKORDER, Column: ACTMATCOST + ACTLABCOST + ACTSERVCOST + ACTTOOLCOST Examples 150.002500.500.00 | |||
| Vendor Vendor | The third-party contractor assigned to the work order, if applicable. | ||
| Description Identifies the external company responsible for the maintenance task. This is populated when work is outsourced. It is crucial for the 'Contractor Execution Efficiency' KPI. Analysis of this attribute allows the maintenance organization to compare the performance (cost, speed, quality) of different vendors against each other and against internal teams. Why it matters Enables vendor management and outsourcing performance analysis. Where to get Table: WORKORDER, Column: VENDOR Examples ACME SERVICESSIEMENSFAST REPAIR INC | |||
Asset Maintenance Activities
| Activity | Description | ||
|---|---|---|---|
| Maintenance Request Created | The initial event where a work order is generated in the system, often originating from a service request or automated schedule. This is explicitly captured from the creation timestamp in the WORKORDER table or the initial entry in the WOSTATUS history. | ||
| Why it matters Marks the start of the process instance and sets the baseline for measuring total resolution time and initial response responsiveness. Where to get WORKORDER.REPORTDATE or initial entry in WOSTATUS table (usually status WAPPR) Capture Logged when transaction creates the WO record Event type explicit | |||
| Work Commenced | Marks the actual start of physical work by the technician. This is explicitly logged when the user changes the status to indicate work is in progress. | ||
| Why it matters Crucial milestone for separating planning time from execution time. Used to calculate Mean Planning and Approval Lead Time. Where to get WOSTATUS table where STATUS = 'INPRG' Capture Logged when transaction changes status to INPRG Event type explicit | |||
| Work Order Approved | Indicates that the work order has passed necessary planning and financial authorization checks. This is derived from a status change in the system history. | ||
| Why it matters Critical for calculating the planning and approval lead time KPI. Delays here indicate administrative bottlenecks. Where to get WOSTATUS table where STATUS = 'APPR' Capture Compare status field before/after to identify APPR transition Event type explicit | |||
| Work Order Cancelled | The process ends prematurely because the work was deemed unnecessary, duplicate, or impossible. This is a terminal state. | ||
| Why it matters Feeds the Work Order Cancellation Analysis dashboard. High rates indicate upstream process failures in request generation. Where to get WOSTATUS table where STATUS = 'CAN' Capture Logged when transaction changes status to CAN Event type explicit | |||
| Work Order Closed | The final lifecycle event where the work order is financially settled and becomes read-only. No further charges can be applied. | ||
| Why it matters Marks the end of the Administrative Lead Time. Delays here affect financial reporting. Where to get WOSTATUS table where STATUS = 'CLOSE' Capture Logged when transaction changes status to CLOSE Event type explicit | |||
| Work Order Completed | The technician signs off that the physical work is finished. This status change stops the clock on execution time KPIs. | ||
| Why it matters The primary end timestamp for technical execution. Used to calculate SLA compliance and Technician utilization. Where to get WOSTATUS table where STATUS = 'COMP' Capture Logged when transaction changes status to COMP Event type explicit | |||
| Inspection Completed | Indicates that a safety or technical inspection was performed during the lifecycle. This is often a status change or the completion of a checklist measurement. | ||
| Why it matters Key for the Safety and Compliance Documentation dashboard. Ensures regulatory steps aren't bypassed. Where to get WOSTATUS change to 'INSP' or similar custom status, or completion of MEASUREMENT entries Capture Compare status field before/after Event type inferred | |||
| Labor Hours Recorded | Represents the entry of actual time spent by a technician on the specific work order. Multiple entries may occur for a single work order as different technicians contribute. | ||
| Why it matters Feeds the Labor Estimation Accuracy Dashboard by providing the actuals to compare against estimates. Where to get LABTRANS table entries linked to the Work Order Capture Logged when transaction occurs in LABTRANS Event type explicit | |||
| Material Issued | Records the physical consumption or issuance of parts from inventory to the work order. This confirms that parts are available and being used. | ||
| Why it matters Validates the supply chain process and impacts the total cost analysis of the maintenance intervention. Where to get MATUSETRANS table where ISSUETYPE = 'ISSUE' Capture Logged when transaction occurs in MATUSETRANS Event type explicit | |||
| Material Requisition Submitted | Signifies that spare parts or consumables have been requested for the maintenance task. This can be inferred from the status changing to Waiting on Material or the creation of material requirement lines. | ||
| Why it matters Essential for the Material Readiness dashboard to identify supply chain delays preventing work execution. Where to get WOSTATUS table where STATUS = 'WMATL' or creation of entries in WPMATERIAL table Capture Inferred from status change to WMATL Event type inferred | |||
| Quality Check Failed | Occurs when a completed job is rejected during review, often reverting the status back to In Progress. Represents rework loops. | ||
| Why it matters Directly supports the Maintenance Quality and Rework Rates dashboard by highlighting process failures. Where to get Inferred from WOSTATUS transition from COMP back to INPRG or WAPPR Capture Derive from comparing field WOSTATUS history for backward transitions Event type inferred | |||
| Resources Scheduled | The point where specific labor or crews are assigned to the work order. This is tracked when assignments are generated or the status moves to Waiting Schedule. | ||
| Why it matters Supports the Planning and Approval Cycle Analysis dashboard by isolating the time taken to find available technicians. Where to get Creation of rows in ASSIGNMENT table or WOSTATUS change to 'WSCH' Capture Derive from creation of Assignment records linked to WO Event type inferred | |||
| Target Date Updated | Logs a change to the scheduled completion date or SLA target. This helps identify when expectations are moved to accommodate delays. | ||
| Why it matters Important for analyzing Critical Asset SLA Performance and identifying 'cheating' where dates are moved to avoid SLA breach. Where to get Audit trail on TARGETCOMPDATE or SCHEDFINISH fields Capture Logged when transaction updates date fields Event type explicit | |||
Extraction Guides
Steps
Establish Database View Strategy: Because ProcessMind requires a flat event log and Maximo stores data hierarchically (Header in WORKORDER, History in WOSTATUS, Costs in WOLABTRANS), the most robust method is to create a Database View in the Maximo database first. This view will act as the source for the Integration Framework.
Create the SQL View: Execute the SQL provided in the Query section within your database management tool (SQL Developer, SSMS). This consolidates
WORKORDER,WOSTATUS,WOLABTRANS,MATUSETRANS, andA_WORKORDER(Audit) into a single flat structure.Register the View in Maximo: Log in to Maximo as an Administrator. Navigate to System Configuration, Platform Configuration, Database Configuration. Create a new Object named
PM_WO_EVENTLOG. Map it to the database view created in the previous step. RunConfigDBto register it (no downtime required for Views usually, but check your procedures).Create Object Structure: Navigate to Integration, Object Structures. Create a new Object Structure named
MX_PM_EVENTS. AddPM_WO_EVENTLOGas the source object. Ensure the support for Flat Structure is checked if available.Configure Publish Channel: Navigate to Integration, Publish Channels. Create a new channel
PC_PM_EVENTSassociated with theMX_PM_EVENTSObject Structure. This allows you to define processing rules if needed.Setup External System: Navigate to Integration, External Systems. Select your target system (or create a generic EXTSYS). Add the
PC_PM_EVENTSPublish Channel to this system.Enable Data Export: In the External Systems application, use the Data Export feature tab. Select the
PC_PM_EVENTSchannel. You can specify a SQL Where clause here (e.g.,EVENTTIMESTAMP >= '2023-01-01') to limit the export range.Export Data: Click Export. The system will generate the file (XML or CSV depending on the End Point configured). For ProcessMind, CSV is preferred. Ensure your End Point (e.g.,
MXFLATFILE) is configured to output CSV.Verify Output: Open the generated CSV file. Ensure headers match the attributes defined in the query (WorkOrderNumber, Activity, etc.) and that no hierarchical XML tags remain.
Final Formatting: If the Maximo CSV export includes standard system metadata columns (like
OWNER1,ORGID), remove them if not needed. Load the cleaned CSV into ProcessMind.
Configuration
- Maximo Status Synonyms: Maximo allows custom status codes (e.g., APPR, WAPPR). The query assumes standard internal values. Verify your system's
SYNONYMDOMAINfor theWOSTATUSdomain to map custom statuses correctly. - Audit Tables: The extraction of 'Target Date Updated' relies on the
A_WORKORDERaudit table. If auditing is not enabled for theWORKORDERobject, this specific activity will yield no rows. Enable auditing in Database Configuration if critical. - Date Range: For initial load, filter by
EVENTTIMESTAMPover the last 6-12 months. Large historical loads may timeout via the Integration Framework web interface; use background processing for datasets larger than 50k rows. - Site/Org Filters: Maximo is multi-site. Always filter by
SITEIDif the process analysis is scoped to a specific facility. - Performance: The
UNION ALLquery is resource-intensive. Ensure database indexes exist onWOSTATUS.WONUM,WOLABTRANS.REFWO, andMATUSETRANS.REFWO.
a Sample Query config
/* Create a Database View or Run directly to extract Event Log */
/* 1. Maintenance Request Created */
SELECT
W.WONUM AS WorkOrderNumber,
'Maintenance Request Created' AS Activity,
W.REPORTDATE AS EventTimestamp,
'Maximo' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
W.WORKTYPE AS WorkType,
W.WOPRIORITY AS Priority,
W.ASSETNUM AS AssetNumber,
W.LEAD AS AssignedResource,
W.ACTFINISH AS ActualFinishDate,
W.TARGCOMPDATE AS TargetCompletionDate,
0 AS ActualLaborHours,
W.ASSETLOCPRIORITY AS AssetCriticality,
W.STATUS AS Status
FROM WORKORDER W
WHERE W.REPORTDATE IS NOT NULL
UNION ALL
/* 2. Status Driven Activities (Approved, Scheduled, Commenced, Completed, Closed, Cancelled, etc.) */
SELECT
S.WONUM AS WorkOrderNumber,
CASE
WHEN S.STATUS = 'APPR' THEN 'Work Order Approved'
WHEN S.STATUS = 'WMATL' THEN 'Material Requisition Submitted'
WHEN S.STATUS = 'WSCH' THEN 'Resources Scheduled'
WHEN S.STATUS = 'INPRG' THEN 'Work Commenced'
WHEN S.STATUS = 'INSP' THEN 'Inspection Completed' /* Verify Synonym */
WHEN S.STATUS = 'COMP' THEN 'Work Order Completed'
WHEN S.STATUS = 'REJECT' THEN 'Quality Check Failed' /* Verify Synonym */
WHEN S.STATUS = 'CLOSE' THEN 'Work Order Closed'
WHEN S.STATUS = 'CAN' THEN 'Work Order Cancelled'
ELSE 'Status Change: ' || S.STATUS
END AS Activity,
S.CHANGEDATE AS EventTimestamp,
'Maximo' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
W.WORKTYPE AS WorkType,
W.WOPRIORITY AS Priority,
W.ASSETNUM AS AssetNumber,
S.CHANGEBY AS AssignedResource,
W.ACTFINISH AS ActualFinishDate,
W.TARGCOMPDATE AS TargetCompletionDate,
0 AS ActualLaborHours,
W.ASSETLOCPRIORITY AS AssetCriticality,
S.STATUS AS Status
FROM WOSTATUS S
JOIN WORKORDER W ON S.WONUM = W.WONUM AND S.SITEID = W.SITEID
WHERE S.STATUS IN ('APPR', 'WMATL', 'WSCH', 'INPRG', 'INSP', 'COMP', 'REJECT', 'CLOSE', 'CAN')
UNION ALL
/* 3. Labor Hours Recorded */
SELECT
L.REFWO AS WorkOrderNumber,
'Labor Hours Recorded' AS Activity,
L.STARTDATE AS EventTimestamp,
'Maximo' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
W.WORKTYPE AS WorkType,
W.WOPRIORITY AS Priority,
W.ASSETNUM AS AssetNumber,
L.LABORCODE AS AssignedResource,
W.ACTFINISH AS ActualFinishDate,
W.TARGCOMPDATE AS TargetCompletionDate,
L.REGULARHRS AS ActualLaborHours,
W.ASSETLOCPRIORITY AS AssetCriticality,
W.STATUS AS Status
FROM WOLABTRANS L
JOIN WORKORDER W ON L.REFWO = W.WONUM AND L.SITEID = W.SITEID
UNION ALL
/* 4. Material Issued */
SELECT
M.REFWO AS WorkOrderNumber,
'Material Issued' AS Activity,
M.TRANSDATE AS EventTimestamp,
'Maximo' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
W.WORKTYPE AS WorkType,
W.WOPRIORITY AS Priority,
W.ASSETNUM AS AssetNumber,
M.ISSUETO AS AssignedResource,
W.ACTFINISH AS ActualFinishDate,
W.TARGCOMPDATE AS TargetCompletionDate,
0 AS ActualLaborHours,
W.ASSETLOCPRIORITY AS AssetCriticality,
W.STATUS AS Status
FROM MATUSETRANS M
JOIN WORKORDER W ON M.REFWO = W.WONUM AND M.SITEID = W.SITEID
WHERE M.ISSUETYPE = 'ISSUE'
UNION ALL
/* 5. Target Date Updated (Requires Audit Table) */
SELECT
A.WONUM AS WorkOrderNumber,
'Target Date Updated' AS Activity,
A.AUDITSTAMP AS EventTimestamp,
'Maximo' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
W.WORKTYPE AS WorkType,
W.WOPRIORITY AS Priority,
W.ASSETNUM AS AssetNumber,
A.AUDITUSER AS AssignedResource,
W.ACTFINISH AS ActualFinishDate,
A.TARGCOMPDATE AS TargetCompletionDate,
0 AS ActualLaborHours,
W.ASSETLOCPRIORITY AS AssetCriticality,
W.STATUS AS Status
FROM A_WORKORDER A
JOIN WORKORDER W ON A.WONUM = W.WONUM AND A.SITEID = W.SITEID
WHERE A.TARGCOMPDATE IS NOT NULL
AND A.TARGCOMPDATE <> COALESCE((SELECT TOP 1 PREV.TARGCOMPDATE FROM A_WORKORDER PREV WHERE PREV.WONUM = A.WONUM AND PREV.AUDITSTAMP < A.AUDITSTAMP ORDER BY PREV.AUDITSTAMP DESC), '1900-01-01')Steps
Database Connection: Establish a read-only JDBC or ODBC connection to the IBM Maximo backend database (commonly DB2, Oracle, or SQL Server). Ensure the user has SELECT permissions on the WORKORDER, WOSTATUS, LABTRANS, and MATUSETRANS tables.
Identify Scope: Determine the specific SITES or ORGIDS you need to extract. Maximo is a multi-site system, and Work Order Numbers (WONUM) are only unique when combined with SITEID. Decide on the date range, typically filtering by the REPORTDATE (creation date) or STATUSDATE.
Understand the Data Model: The WORKORDER table acts as the header. The WOSTATUS table contains the history of lifecycle changes. The LABTRANS table holds detailed labor entries, and MATUSETRANS holds material movements. These must be combined using UNION ALL to form a single event stream.
Handle Synonyms: Maximo uses internal values (MAXVALUE) and displayed values (VALUE) for statuses defined in the SYNONYMDOMAIN. The query should ideally filter on the internal MAXVALUE to ensure consistency across different sites that might use different display labels for the same logical status.
Prepare the Query: Copy the SQL provided in the Query section. Replace placeholders like [Your Database Schema] and [Start Date] with your actual values. If your environment uses specific custom status codes for Inspections or Quality Checks, update the WHERE clauses in the respective sections.
Execute Extraction: Run the query. Depending on the volume of data, you may need to run this in batches (e.g., month by month) to avoid database timeouts.
Validate Data: Check that the 'Maintenance Request Created' event exists for every work order. Ensure that timestamps are in a format compatible with ProcessMind (ISO 8601 is recommended).
Post-Processing: Maximo timestamps usually contain milliseconds. Ensure these are preserved to maintain the correct sorting order of events that happen in rapid succession.
Export: Save the results as a CSV or Parquet file. The column headers must match the attributes defined in the query output.
ProcessMind Upload: Import the file into ProcessMind. Map 'WorkOrderNumber' as the Case ID, 'Activity' as the Activity Name, and 'EventTimestamp' as the Timestamp.
Configuration
- Database Platforms: Maximo typically runs on IBM DB2, Oracle, or SQL Server. The syntax provided is standard SQL but may require minor date function adjustments (e.g., TO_DATE vs CAST) depending on your specific platform.
- Date Filtering: Use the REPORTDATE column in the WORKORDER table to define the scope of the process instances. A rolling window of the last 12 months is standard.
- Site ID Importance: Never rely on WONUM alone for uniqueness. Always concatenate WONUM and SITEID or include SITEID as a case attribute if analyzing multiple sites.
- Status Logic: Maximo allows custom status values. Check the SYNONYMDOMAIN table if standard statuses like WMATL or COMP are not yielding results.
- Performance: The LABTRANS and MATUSETRANS tables can be very large. Ensure these tables are indexed on REFWO and SITEID.
a Sample Query sql
SELECT
W.WONUM AS WorkOrderNumber,
'Maintenance Request Created' AS Activity,
W.REPORTDATE AS EventTimestamp,
'Maximo' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
W.WORKTYPE AS WorkType,
W.WOPRIORITY AS Priority,
W.ASSETNUM AS AssetNumber,
W.LEAD AS AssignedResource,
W.ACTFINISH AS ActualFinishDate,
W.TARGCOMPDATE AS TargetCompletionDate,
W.ACTLABHRS AS ActualLaborHours,
W.ASSETLOCPRIORITY AS AssetCriticality,
W.STATUS AS Status
FROM
WORKORDER W
WHERE
W.REPORTDATE >= '2023-01-01'
UNION ALL
SELECT
WS.WONUM AS WorkOrderNumber,
CASE
WHEN WS.STATUS = 'APPR' THEN 'Work Order Approved'
WHEN WS.STATUS = 'WMATL' THEN 'Material Requisition Submitted'
WHEN WS.STATUS = 'WSCH' THEN 'Resources Scheduled'
WHEN WS.STATUS = 'INPRG' THEN 'Work Commenced'
WHEN WS.STATUS = 'INSP' THEN 'Inspection Completed'
WHEN WS.STATUS = 'COMP' THEN 'Work Order Completed'
WHEN WS.STATUS = 'REJECT' THEN 'Quality Check Failed'
WHEN WS.STATUS = 'CLOSE' THEN 'Work Order Closed'
WHEN WS.STATUS = 'CAN' THEN 'Work Order Cancelled'
ELSE 'Status Change: ' || WS.STATUS
END AS Activity,
WS.CHANGEDATE AS EventTimestamp,
'Maximo' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
W.WORKTYPE AS WorkType,
W.WOPRIORITY AS Priority,
W.ASSETNUM AS AssetNumber,
WS.CHANGEBY AS AssignedResource,
W.ACTFINISH AS ActualFinishDate,
W.TARGCOMPDATE AS TargetCompletionDate,
W.ACTLABHRS AS ActualLaborHours,
W.ASSETLOCPRIORITY AS AssetCriticality,
WS.STATUS AS Status
FROM
WOSTATUS WS
JOIN
WORKORDER W ON WS.WONUM = W.WONUM AND WS.SITEID = W.SITEID
WHERE
W.REPORTDATE >= '2023-01-01'
AND WS.STATUS IN ('APPR', 'WMATL', 'WSCH', 'INPRG', 'INSP', 'COMP', 'REJECT', 'CLOSE', 'CAN')
UNION ALL
SELECT
L.REFWO AS WorkOrderNumber,
'Labor Hours Recorded' AS Activity,
L.STARTDATE AS EventTimestamp,
'Maximo' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
W.WORKTYPE AS WorkType,
W.WOPRIORITY AS Priority,
W.ASSETNUM AS AssetNumber,
L.LABORCODE AS AssignedResource,
W.ACTFINISH AS ActualFinishDate,
W.TARGCOMPDATE AS TargetCompletionDate,
W.ACTLABHRS AS ActualLaborHours,
W.ASSETLOCPRIORITY AS AssetCriticality,
W.STATUS AS Status
FROM
LABTRANS L
JOIN
WORKORDER W ON L.REFWO = W.WONUM AND L.SITEID = W.SITEID
WHERE
W.REPORTDATE >= '2023-01-01'
UNION ALL
SELECT
M.REFWO AS WorkOrderNumber,
'Material Issued' AS Activity,
M.TRANSDATE AS EventTimestamp,
'Maximo' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
W.WORKTYPE AS WorkType,
W.WOPRIORITY AS Priority,
W.ASSETNUM AS AssetNumber,
M.ENTERBY AS AssignedResource,
W.ACTFINISH AS ActualFinishDate,
W.TARGCOMPDATE AS TargetCompletionDate,
W.ACTLABHRS AS ActualLaborHours,
W.ASSETLOCPRIORITY AS AssetCriticality,
W.STATUS AS Status
FROM
MATUSETRANS M
JOIN
WORKORDER W ON M.REFWO = W.WONUM AND M.SITEID = W.SITEID
WHERE
W.REPORTDATE >= '2023-01-01'
AND M.ISSUETYPE = 'ISSUE'
UNION ALL
SELECT
WC.WONUM AS WorkOrderNumber,
'Target Date Updated' AS Activity,
WC.CHANGEDATE AS EventTimestamp,
'Maximo' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
W.WORKTYPE AS WorkType,
W.WOPRIORITY AS Priority,
W.ASSETNUM AS AssetNumber,
WC.CHANGEBY AS AssignedResource,
W.ACTFINISH AS ActualFinishDate,
W.TARGCOMPDATE AS TargetCompletionDate,
W.ACTLABHRS AS ActualLaborHours,
W.ASSETLOCPRIORITY AS AssetCriticality,
W.STATUS AS Status
FROM
WOCHANGE WC
JOIN
WORKORDER W ON WC.WONUM = W.WONUM AND WC.SITEID = W.SITEID
WHERE
W.REPORTDATE >= '2023-01-01'
AND (WC.MODIFIEDATTRIBUTE = 'TARGCOMPDATE' OR WC.MODIFIEDATTRIBUTE = 'SCHEDFINISH')