ha_state_gaps_finder.rb

require 'pg'
require 'fileutils'
require 'dotenv'

num_days = 30 # How many days back to look at to establish a baseline for what is a normal gap
max_allowed_ratio = 4 # ratio between longest gap seen in the num_days period and current gap
min_required_count = 5 # only look at entities with at least this number of states reported in the period
min_gap_to_care_about = 5 * 60 # Time in seconds for the minimum gap we should notify about
min_state_id_seen_filename = 'min_state_id_seen.txt'
ignored_entity_ids_filename = 'ignored_entity_ids.txt'
watched_entity_ids_filename = 'watched_entity_ids.txt'
watched_entity_ids_from_patterns_filename = 'watched_entity_ids_from_patterns.txt'
ignored_states = %w[unavailable unknown]
Dotenv.load
Dotenv.require_keys('FROM_EMAIL_ADDRESS', 'TO_EMAIL_ADDRESS')

FileUtils.touch min_state_id_seen_filename
FileUtils.touch ignored_entity_ids_filename
FileUtils.touch watched_entity_ids_filename
FileUtils.touch watched_entity_ids_from_patterns_filename
min_state_id = File.read(min_state_id_seen_filename).to_i
ignored_entity_ids = File.readlines(ignored_entity_ids_filename).map(&:chomp)
watched_entity_ids = File.readlines(watched_entity_ids_filename).map(&:chomp)
watched_entity_ids += File.readlines(watched_entity_ids_from_patterns_filename).map(&:chomp)
watched_entity_ids = watched_entity_ids.reject(&:empty?).uniq

# TODO: Move connection details into env
conn = PG.connect(dbname: 'homeassistant', port: 5432)
date_result = conn.exec "select current_date, (current_date - interval '#{num_days} days')::date as oldest_date, date_part('hour', current_timestamp) as current_hour"
current_date = date_result.first['current_date']
oldest_date = date_result.first['oldest_date']
current_hour = date_result.first['current_hour'].to_i
ignored_states_sql = ignored_states.any? ? "and states.state not in (#{ignored_states.map { |state| "'#{state}'" }.join(', ')})" : ''

puts "Found #{watched_entity_ids.count} entity_ids in #{watched_entity_ids_filename} and #{watched_entity_ids_from_patterns_filename}, limiting search to only those"
puts "Found #{ignored_entity_ids.count} entity_ids in #{ignored_entity_ids_filename}, excluding those from search"
puts "Searching for states between #{oldest_date} and #{current_date}, current hour #{current_hour}"
stale_states_query = <<~QUERY
  with meaningful_states as (
    select
      states.metadata_id,
      states.state_id,
      states.state,
      states.last_updated_ts,
      lag(states.state) over state_window as previous_state
    from states
    where states.state_id >= #{min_state_id}
    and to_timestamp(states.last_updated_ts) > current_date - interval '#{num_days} days'
    #{ignored_states_sql}
    window state_window as (partition by states.metadata_id order by states.state_id)
  ),
  state_changes as (
    select
      metadata_id,
      state_id,
      last_updated_ts,
      to_timestamp(last_updated_ts) - to_timestamp(lag(last_updated_ts) over (partition by metadata_id order by state_id)) as update_duration
    from meaningful_states
    where state is distinct from previous_state
  )
  select
    sm.entity_id,
    max(state_changes.update_duration) as longest_update_duration,
    now() - to_timestamp(max(state_changes.last_updated_ts)) as current_update_duration,
    round(extract(epoch from (now() - to_timestamp(max(state_changes.last_updated_ts)))) / extract(epoch from max(state_changes.update_duration)), 2) as ratio,
    to_timestamp(max(state_changes.last_updated_ts)) as last_update_dt,
    extract(epoch from (now() - to_timestamp(max(state_changes.last_updated_ts)))) as current_update_duration_seconds,
    count(*) as count,
    min(state_changes.state_id) as min_state_id
  from state_changes
  join states_meta sm using (metadata_id)
  group by sm.entity_id
  order by 4 desc
QUERY
stale_states = conn.exec stale_states_query
puts "Found #{stale_states.count} entities in DB, checking for staleness"

min_state_id_seen = Float::INFINITY
problem_entity_count = 0
message_body = ''
stale_entity_ids = []
stale_states.each do |row|
  entity_id = row['entity_id']
  count = row['count'].to_i
  min_state_id_seen = row['min_state_id'].to_i if row['min_state_id'].to_i < min_state_id_seen && row['min_state_id'].to_i > 0
  next if (watched_entity_ids.count > 0) && (!watched_entity_ids.include? entity_id)
  next if ignored_entity_ids.include? entity_id
  next unless row['ratio'].to_f > max_allowed_ratio && count > min_required_count && row['current_update_duration_seconds'].to_f > min_gap_to_care_about
  message = "Entity #{entity_id} currently hasn't had an update in #{row['current_update_duration']}, with the previous longest gap seen of #{row['longest_update_duration']} (ratio #{row['ratio']}) and #{count} total updates seen."
  puts message
  message_body += message
  message_body += "\n"
  stale_entity_ids.append(entity_id)
  problem_entity_count += 1
end

puts "Lowest state_id seen: #{min_state_id_seen}"
File.write(min_state_id_seen_filename, min_state_id_seen)

exit unless problem_entity_count > 0
exit if $DEBUG
message_body += "\n\nStale Entity IDs:\n"
message_body += stale_entity_ids.join("\n")

from = ENV['FROM_EMAIL_ADDRESS']
to = ENV['TO_EMAIL_ADDRESS']
if problem_entity_count == 1
  subject = "Home Assistant: There is #{problem_entity_count} entity that has stopped updating"
else
  subject = "Home Assistant: There are #{problem_entity_count} entities that have stopped updating"
end

# TODO: Switch to email that supports bold around entities
email_message = <<~EMAIL_MESSAGE
  To: #{to}
  From: #{from}
  Subject: #{subject}
  #{message_body}
EMAIL_MESSAGE

IO.popen('/usr/sbin/sendmail -t', 'w') do |sendmail|
  sendmail.write(email_message)
end