Need Help With Automation Comparing Sensor Value Now To Sensor Value An Hour Ago

I’m trying to create an automation that compares a sensor value now to a sensor value an hour ago, and if it’s more than 30% higher do something. I’m not sure how get the sensor value from an hour ago, or how to set up the math equation to check if the (now value / old value) is 1.3 or greater.

The sensor value I’m trying to use is:

sensor.pi_hole_dns_queries_today

My ultimate goal is to have Home Assistant use TTS and have Alexa tell me the PiHole saw an unusual spike in DNS queries. I know how to do the TTS to Alexa part, I just don’t know how check and compare the sensor values throughout the day.

Thanks in advance.

  1. Create a SQL sensor that get you the value from 1h ago:
- platform: sql
  queries:
    - name: Queries 1h ago
      query: "select state from states where entity_id = 'sensor.pi_hole_dns_queries_today' and created < datetime('now','-1 hour') order by created DESC LIMIT 1;"
      column: 'state'    
  1. Compare
{{ states('sensor.pi_hole_dns_queries_today')|float > states('sensor.pihole_queries_1h_ago')|float * 1.3 }}

EDIT: Fixed syntax of compare

1 Like

The SQL sensor works.

For the compare I tried using it with a Template Trigger, but that didn’t seem to work, what type of type of Trigger should I use?

Please show what you have

I modified the name slightly from your original example, and it’s showing a value so I don’t think that’s the problem. Right now I just have it switching my office light on and off, so I know it’s working, eventually it will do a TTS announcement to an Amazon Echo, but I wanted to get the Trigger working with something simple to start.

Here’s the yaml:

alias: Network Status PiHole Large Increase in Queries
trigger:
  - platform: template
    value_template: >-
      {{ sensor.pi_hole_dns_queries_today|float > (sensor.sensor.pihole_queries_1h_ago|float * 1.3) }}
action:
  - type: turn_off
    device_id: dd39fc1e2a344b7a9e25bdc40e44a408
    entity_id: switch.office_light
    domain: switch
  - type: turn_on
    device_id: dd39fc1e2a344b7a9e25bdc40e44a408
    entity_id: switch.office_light
    domain: switch
mode: queued
max: 10

Seems fine.
What are the values of the sensors when the automation should have been triggered?

There’s some syntax errors here.

    value_template: >
      {{ states('sensor.pi_hole_dns_queries_today')|float > states('sensor.pihole_queries_1h_ago')|float * 1.3 }}
2 Likes

Hi!

I found this old post on how to get old sensor values with the SQL-sensor, but I can´t get it to work.
Has anything changed since the original question was created (except from the fact that the integration has been moved to GUI)?

The query I´m trying looks like this:
SELECT state FROM states WHERE entity_id = 'sensor.probe1' and created = datetime('now','-1 hour') ORDER BY created DESC LIMIT 1;

and the result is always ‘Unknown’

I have also tested (just to check the SQL-integration) this qurey:
SELECT * FROM states WHERE entity_id = 'sensor.probe1' ORDER BY state_id DESC LIMIT 1;

And that works fine and gives me the latest sensor value.

I found the solution.
created should be replaced with last_updated, so here is what I´m using now:
SELECT state from states where entity_id = 'sensor.probe1' and last_updated < datetime('now','-5 minutes') order by last_updated DESC LIMIT 1;

1 Like