Scout triggers and actions

​

Trigger Types

• Query pattern - Triggers when a running database query matches a specific pattern you define.
  • Use for:
    • Security: Kill dangerous queries like DROP, detect intrusions using honeypot tables
    • Performance: Kill inefficient IOPS-intensive queries
    • Sensitive data protection: Kill queries accessing sensitive tables
  • Examples:
    • Security: Create a “honeypot” dummy table - if someone queries it, this indicates a potential security intrusion
    • Performance: Kill IOPS-intensive queries that slow down the database
    • Sensitive data: Kill running SELECT queries from salary table
• Query Duration - Triggers when a running query exceeds the time limit you set.
  • Use for: Performance protection - kill long-running queries that can slow down the database
  • Example: Kill queries that have been running more than 30 seconds
• User(s) - Triggers when specific database users have running processes.
  • Use for: Kill problematic user activity, or use mostly as a secondary trigger combined with other conditions
  • Example: Kill running processes from “test_user” OR kill queries from “admin_user” that match dangerous patterns
• IP(s) - Triggers when running processes come from specific IP addresses.
  • Use for: Security (kill processes from suspicious IPs), or use mostly as a secondary trigger
  • Example: Kill running processes from 192.168.1.100 OR kill queries from internal IPs accessing sensitive data
• Database Name - Triggers when running processes access specific database schemas.
  • Use for: Mostly as a secondary trigger combined with other conditions to add database-specific rules
  • Example: Kill long-running queries only when running on production database
• Connection Utilization - Triggers when database connection usage exceeds a percentage you set.
  • Use for: Prevent applications from getting “too many connections” errors that would cause application failures
  • Why use rapydo: Rapydo proactively manages connections by killing idle ones before the limit is reached, keeping your applications up & running instead of waiting for connection errors to occur
  • Example: Kill idle connections when 80% of connection limit is reached, ensuring space for new application requests
• Connection idle time - Triggers when a database connection has been idle (in “sleep” mode) for longer than the specified time.
  • Use for: Resource management - kill idle connections to free up database memory
  • Why use rapydo: While both PostgreSQL and MySQL have built-in configuration parameters for idle connection timeout, rapydo allows you to set different values per user or per database for more granular control
  • Example: Kill connections that have been idle for more than 10 minutes, or set 5 minutes for test users but 30 minutes for admin users
• Custom Script - Triggers when your custom script identifies running processes that should be terminated.
  • Use for: Advanced monitoring needs that require custom logic
  • Example: Kill processes based on complex business rules

​

Trigger Operator Types

• In - this operator is relevant to the Trigger types: User(s) and IP(s), and allows us to define a few IP addresses or Users that will trigger the action(s) defined.
• Not In - similar to the operator In, but it is used for triggering an action when the IPs or Users are not included in the given list.
• Like - this operator is relevant to the Trigger type: Query Pattern, and allows you to define a pattern using the % wildcard to match queries. The % wildcard represents any content in the query. Pattern matching is case-insensitive and semicolons are automatically removed. - Examples: \
  • select % from users; - matches any SELECT from users table
  • delete from %; - matches any DELETE without WHERE clause
  • update admin set %; - matches any UPDATE on admin table
  Copy

  -- ORIGINAL QUERY
  SELECT 
      c.[customer_id],
      c.[first_name] + ' ' + c.[last_name] AS [full_name],
      cat.[category_name],
      p.[product_name],
      p.[price],
      oi.[quantity],
      (oi.[quantity] * p.[price]) AS [line_total],
      o.[order_date],
      r.[rating],
      r.[review_text],
      CASE 
          WHEN r.[rating] >= 4 THEN 'Positive'
          WHEN r.[rating] = 3 THEN 'Neutral'
          ELSE 'Negative'
      END AS [review_sentiment]
  FROM [customers] c
  INNER JOIN [orders] o ON c.[customer_id] = o.[customer_id]
  INNER JOIN [order_items] oi ON o.[order_id] = oi.[order_id]
  INNER JOIN [products] p ON oi.[product_id] = p.[product_id]
  LEFT JOIN [categories] cat ON p.[category_id] = cat.[category_id]
  LEFT JOIN [reviews] r ON p.[product_id] = r.[product_id] 
      AND c.[customer_id] = r.[customer_id]
  WHERE o.[order_date] >= '2023-01-01'
      AND p.[price] > 50.00
      AND c.[country] IN ('USA', 'Canada', 'UK')
  

  Copy

  -- Find Any date any proce and any country 
  SELECT 
      c.[customer_id],
      c.[first_name] + ' ' + c.[last_name] AS [full_name],
      cat.[category_name],
      p.[product_name],
      p.[price],
      oi.[quantity],
      (oi.[quantity] * p.[price]) AS [line_total],
      o.[order_date],
      r.[rating],
      r.[review_text],
      CASE 
          WHEN r.[rating] >= 4 THEN 'Positive'
          WHEN r.[rating] = 3 THEN 'Neutral'
          ELSE 'Negative'
      END AS [review_sentiment]
  FROM [customers] c
  INNER JOIN [orders] o ON c.[customer_id] = o.[customer_id]
  INNER JOIN [order_items] oi ON o.[order_id] = oi.[order_id]
  INNER JOIN [products] p ON oi.[product_id] = p.[product_id]
  LEFT JOIN [categories] cat ON p.[category_id] = cat.[category_id]
  LEFT JOIN [reviews] r ON p.[product_id] = r.[product_id] 
      AND c.[customer_id] = r.[customer_id]
  WHERE o.[order_date] >= %
      AND p.[price] > %
      AND c.[country] IN (%)
  

• Not Like - similar to the operator Like, but it is used for triggering an action when the given query pattern does not include the current query.
• Is Greater Than - this operator is relevant to the Trigger type Query Duration.

​

Trigger Values

• Query pattern - the string of the query pattern using % wildcard for matching any content. The system is case-insensitive and automatically removes semicolons before matching.
  • Use % to represent any content: select % from table_name;
  • Patterns must match query structure exactly (except case and semicolons).
• Rule will not work when different spacing/formatting such as extra spaces or different line breaks.

SELECT e.lastName, e.firstName, p.amount,pg_sleep(%)  FROM employees e JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber JOIN payments p ON c.customerNumber = p.customerNumber WHERE p.amount < 1000 limit 1;

select e.lastname, e.firstname, p.amount,pg_sleep(10)  from employees e join customers c on e.employeenumber = c.salesrepemployeenumber join payments p on c.customernumber = p.customernumber where p.amount < 1000 limit 1;

SELECT e.lastName,    e.firstName  , p.amount,   pg_sleep(10)  FROM employees e JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber JOIN payments p ON c.customerNumber = p.customerNumber WHERE p.amount < 1000 limit 1;

select e.lastname, e.firstname, p.amount,pg_sleep(10)
from employees e join customers c on e.employeenumber = c.salesrepemployeenumber
join payments p on c.customernumber = p.customernumber
where p.amount < 1000 limit 1;

• Best Practices for Copy-Paste Query Patterns
  • Copy query from Rapydo Application (Query worksapce | Dashbord slowest reports).
  • Copy from **Query Logs/Monitor (**Database query logs, Application logs, Query monitoring tools (rapydo), Database audit trails) This ensures the exact spacing and formatting that will actually be executed.
• Query Duration - the time above which the action will be triggered.
• User(s) - a comma separated list of a User(s).
• IP(s) - a comma separated list of IP(s).
• Database Name - a comma separated list of name(s) of the relevant database schema(s).
• Connection Utilization - a percentage value above which the action(s) will be triggered.
• Custom Script - the relevant script.

​

Action Types

• No Action - No action will be taken. Useful when only a notification is wanted.
• Kill Query - Kill/cancel the specific query that matches the trigger pattern while keeping the database connection alive. Uses pg_cancel_backend for PostgreSQL and CALL mysql.rds_kill_query for MySQL.
• Kill Connection - Terminate the entire database connection that matches the trigger pattern. Uses pg_terminate_backend for PostgreSQL and CALL mysql.rds_kill for MySQL.
• Rate Limit - Limit the rate of a certain connection that match the trigger value.
• Lock - Lock a certain User(s) or IP(s) etc’ out of the system.
• Kill idle connections - All idle connection will be killed when the trigger value is matched.

​

Action Values

• No Action - No value.
• Kill Connection - No value.
• Rate Limit - Limit the resources of the current request request to a defined based on the trigger type and value. All requests above the limit will be ignored.
• Lock - No value.
• Kill idle connections - No value.

​

Examples

​

Scout Rules List

​

Kill long Queries