Maintenance & ReliabilityFree generatorPDF, Excel, Word exportDiscrete Manufacturing

Condition Monitoring Alarm SQL Query Generator

Condition monitoring systems store thousands of readings, but the useful question is simple: what is over its limit and nobody is working on. This generator writes the SQL for that, using the table names you supply.

Fill in

Readings table

Table that stores one row per measurement

Alarm limits table

Assets table

Work orders table

Look back (days)

Open work order statuses

Quoted, comma separated

Generated code

Download:

1. Readings over alert or danger limit in the last period

SELECT a.asset_tag,
       r.point_id,
       r.reading_time,
       r.reading_value,
       l.alert_value,
       l.danger_value,
       CASE WHEN r.reading_value >= l.danger_value THEN 'DANGER'
            WHEN r.reading_value >= l.alert_value THEN 'ALERT'
            ELSE 'OK' END AS severity
FROM sensor_readings r
JOIN alarm_limits l ON l.point_id = r.point_id
JOIN assets a ON a.asset_id = r.asset_id
WHERE r.reading_time >= CURRENT_DATE - INTERVAL '7 days'
  AND r.reading_value >= l.alert_value
ORDER BY severity DESC, r.reading_time DESC;

2. Latest reading per measurement point

SELECT r.asset_id, r.point_id, r.reading_time, r.reading_value
FROM sensor_readings r
JOIN (
    SELECT point_id, MAX(reading_time) AS last_time
    FROM sensor_readings
    GROUP BY point_id
) m ON m.point_id = r.point_id AND m.last_time = r.reading_time;

3. Alarming assets with no open work order

SELECT DISTINCT a.asset_tag, a.asset_id
FROM sensor_readings r
JOIN alarm_limits l ON l.point_id = r.point_id
JOIN assets a ON a.asset_id = r.asset_id
WHERE r.reading_time >= CURRENT_DATE - INTERVAL '7 days'
  AND r.reading_value >= l.alert_value
  AND NOT EXISTS (
      SELECT 1 FROM work_orders w
      WHERE w.asset_id = a.asset_id
        AND w.status IN ('OPEN','PLANNED','IN_PROGRESS')
  )
ORDER BY a.asset_tag;

4. Alarm count per asset for ranking

SELECT a.asset_tag,
       COUNT(*) AS alarm_readings,
       MAX(r.reading_value) AS peak_value
FROM sensor_readings r
JOIN alarm_limits l ON l.point_id = r.point_id
JOIN assets a ON a.asset_id = r.asset_id
WHERE r.reading_time >= CURRENT_DATE - INTERVAL '7 days'
  AND r.reading_value >= l.alert_value
GROUP BY a.asset_tag
ORDER BY alarm_readings DESC;

Table and column names are generic examples, not the schema of any specific CMMS or ERP. Map them to your own tables. The interval syntax follows PostgreSQL; use DATEADD in SQL Server or SYSDATE minus days in Oracle. Test on a read-only copy first.

Automate alarm to work request

Optional: we can look at your CMMS or ERP and design a read-only alarm check with human approval of any new request.

Optional. The tool and every export work without this. No spam.

How to use it

Enter the names of your readings table, limits table, asset table and work order table. Set how many days back to look and the statuses that count as open. Copy the four queries into your SQL tool. The first lists the readings over limit with a severity label. The second gives the latest reading per point. The third lists assets that are alarming but have no open work order, which is the gap that matters most. The fourth ranks assets by alarm count for review meetings.

Rename columns where your schema differs
Run on a reporting replica, not the production database
Schedule query three daily and send the result to the planner
Keep alarm limits in a table, not hard coded

The logic behind the queries

Each measurement point has an alert and danger limit. A reading at or above the alert value is flagged, and at or above danger is escalated. Joining to the work order table with NOT EXISTS finds assets where the alarm has not become a job. This mirrors how most condition-based maintenance programs close the loop: detect, notify, plan, execute, verify. Filtering by a time window prevents old, already handled readings from cluttering the list.

Common mistakes

Many teams compare readings to a single fixed limit for every machine. Limits should be set per point, based on the baseline and the standard for the class of machine. Another mistake is counting a single noisy spike as an alarm; consider requiring two consecutive readings or a moving average. Also check the units: velocity in mm/s and in/s mixed in one table will silently produce false alarms.

Frequently Asked Questions

Will these queries work on my CMMS database?

Not as written. The table and column names are generic placeholders. Replace them with your own schema. The structure of joins and the NOT EXISTS test is the part that carries over.

How do I avoid false alarms from single spikes?

Require two or more consecutive readings above the limit, or use a rolling average over several readings. You can add a HAVING COUNT(*) >= 2 clause to the grouped query to enforce a minimum count.

Can I run this against a time series database?

The logic is the same, but the syntax differs. Most time series stores have their own query language, so use the structure here as a guide and translate the joins into the equivalent filters.

Should write-back to the work order table be automatic?

We recommend keeping it read-only and creating a request that a planner approves. Automatic creation of orders from raw sensor data tends to flood the backlog if limits are not tuned.

If you want these alarms to create work requests in your ERP or CMMS automatically, with human approval, we can scope that with you. Ask for an expert review.

Talk to an Expert Explore More Free Tools

Related Tools

Maintenance & Reliability

More Maintenance & Reliability tools

See all

Back to All Tools