# Condition Monitoring Alarm SQL Query Generator

> Source: https://netray.co/tools/condition-monitoring-alarm-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your table and column names and get SQL that finds sensor readings over limits and flags assets with no open work order.

## Category

maintenance

## Industry

Discrete Manufacturing

## Audience

- Reliability engineer
- CMMS administrator
- Maintenance planner

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Readings table

**Key:** readings

**Type:** text

**Default:** sensor_readings

**Help:** Table that stores one row per measurement

#### Alarm limits table

**Key:** limits

**Type:** text

**Default:** alarm_limits

#### Assets table

**Key:** assets

**Type:** text

**Default:** assets

#### Work orders table

**Key:** wo

**Type:** text

**Default:** work_orders

#### Look back (days)

**Key:** days

**Type:** number

**Default:** 7

#### Open work order statuses

**Key:** open_status

**Type:** text

**Default:** 'OPEN','PLANNED','IN_PROGRESS'

**Help:** Quoted, comma separated

**Blocks:**

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

**Body:** 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 {{readings}} r
JOIN {{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 '{{days}} days'
  AND r.reading_value >= l.alert_value
ORDER BY severity DESC, r.reading_time DESC;

#### 2. Latest reading per measurement point

**Body:** SELECT r.asset_id, r.point_id, r.reading_time, r.reading_value
FROM {{readings}} r
JOIN (
    SELECT point_id, MAX(reading_time) AS last_time
    FROM {{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

**Body:** SELECT DISTINCT a.asset_tag, a.asset_id
FROM {{readings}} r
JOIN {{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 '{{days}} days'
  AND r.reading_value >= l.alert_value
  AND NOT EXISTS (
      SELECT 1 FROM {{wo}} w
      WHERE w.asset_id = a.asset_id
        AND w.status IN ({{open_status}})
  )
ORDER BY a.asset_tag;

#### 4. Alarm count per asset for ranking

**Body:** SELECT a.asset_tag,
       COUNT(*) AS alarm_readings,
       MAX(r.reading_value) AS peak_value
FROM {{readings}} r
JOIN {{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 '{{days}} days'
  AND r.reading_value >= l.alert_value
GROUP BY a.asset_tag
ORDER BY alarm_readings DESC;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use it

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Will these queries work on my CMMS database?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

condition monitoring sql query, sensor reading threshold query sql, cmms alarm without work order sql, predictive maintenance sql, vibration alarm database query, latest sensor reading per asset sql, asset health query cmms, alarm limit exceeded sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json