Redmine Scheduled Alerts for Inactive Tickets

Redmine Alerts for Inactive Tickets

Add the below script in: /opt/redmine/lib/tasks/Redmine_Alerts_for_all_users.rake

namespace :redmine do
  desc "Send alerts for tickets (all except closed) where last note (comment) was older than 30 days for all users"
  task send_inactive_ticket_alerts: :environment do
    cutoff = 30.days.ago.beginning_of_day

    # Exclude only closed statuses
    open_status_ids = IssueStatus.where(is_closed: false).pluck(:id)

    # All active users with an email
    users = User.active.select { |u| u.mail.present? }

    users.each do |user|
      puts "Checking tickets for #{user.login} (#{user.mail})"

      issues = Issue.where(assigned_to_id: user.id, status_id: open_status_ids)

      inactive = issues.select do |issue|
        last_note = Journal.where(journalized_type: "Issue", journalized_id: issue.id)
                           .where.not(notes: [nil, ""])
                           .order(created_on: :desc)
                           .first

        if last_note.nil?
          issue.created_on < cutoff
        else
          last_note.created_on < cutoff
        end
      end

      if inactive.empty?
        puts " No inactive tickets found for #{user.login}"
        next
      end

      reviewer_emails = []

      # Build summary email body
      body = "Hello #{user.name},\n\n"
      body += "The following tickets assigned to you have had no note updates for more than 30 days:\n\n"

      inactive.each do |issue|
        last_note = Journal.where(journalized_type: "Issue", journalized_id: issue.id)
                           .where.not(notes: [nil, ""])
                           .order(created_on: :desc)
                           .first
        last_update_date = last_note ? last_note.created_on : issue.created_on

        body += <<~TICKET
          ------------------------------------------------------
          Ticket ##{issue.id}: #{issue.subject}
          Project: #{issue.project.name}
          Priority: #{issue.priority.try(:name)}
          Author: #{issue.author.name}
          Assignee: #{user.name}
          Status: #{issue.status.name}
          Last Note Update: #{last_update_date.strftime("%d-%b-%Y")}
          ------------------------------------------------------

        TICKET

        # Reviewer field (if exists, assumed to store user_id)
        reviewer_field = issue.custom_field_values.find { |cf| cf.custom_field.name.downcase == "reviewer" }
        if reviewer_field && reviewer_field.value.present?
          reviewer_user = User.find_by_id(reviewer_field.value.to_i)
          if reviewer_user&.mail.present?
            reviewer_emails << reviewer_user.mail
          else
            puts "Reviewer '#{reviewer_field.value}' not found as valid user"
          end
        end
      end

      body += "\nPlease update these tickets at the earliest.\n\nRegards,\nRedmine Helpdesk"

      recipients = [user.mail] + reviewer_emails.compact.uniq

      ActionMailer::Base.mail(
        from: "helpdesk-noreply.gbb.co.in",
        to: user.mail,
        cc: reviewer_emails.compact.uniq,
        subject: "[Reminder] #{inactive.count} tickets inactive (>30 days)",
        body: body
      ).deliver_now

      puts "Summary alert sent to #{user.mail} (CC: #{reviewer_emails.compact.uniq.join(', ')})"
    end
  end
end

---

Schedule with Cron

Once the above script is added, create a **crontab entry** to schedule the alerts every Monday at 10 AM:

0 10 * * 1 cd /opt/redmine && RAILS_ENV=production bundle exec rake redmine:send_inactive_ticket_alerts >> log/alerts.log 2>&1

---

Summary

The above steps send **alerts from Redmine** to users for tickets:

• Assigned to them
• Still open (not closed)
• Not updated with any notes for the past **30 days**