Hi all.
I might be missing something, but I’ve been playing around with opensky.
I can see the sensor has number of current flights, and in an out events get triggerd.
Is there a way to get a current list of all the active flights?
Will I need to build something to capture the in and out events and keep a list accordingly?
TIA!
Did you come up with any solution?
I haven’t no, I can’t find a native way to do it.
My thinking at this point is that I can use the events to fire a webhook and maintain the current list outside of HA. Seems clunky though.
Somebody got this figured out?
What I basically want to achieve: put the call sign + time stamp of each overflying flight into a list that I can display on a Lovelace page. More info: https://www.home-assistant.io/integrations/opensky/
Steps I’m thinking of:
I need some help with the action part and in the string conversion syntax in de last line.
automation:
- id: 'flightlistautomation'
alias: Flight List Automation
trigger:
platform: event
event_type: opensky_entry
action:
service: input_text.set_value
data_template:
entity_id: input_text.flightlist
value: 'Flight entry of {{ trigger.event.data.callsign }}'
Hope somebody can support in this.
I actually ended up writing a node red flow, and i write the contents ok every entry and exit to a database, and then have an sql sensor to display the difference !! ie flights that have an entry timestamp, but no exit one.
Hi Dan,
That is also a workable solution, but I want to try to keep it into Home Assistant. Would be very much appreciated if somebody can help here.
Short update: I managed to get the flightlist text helper updated by using the input_text service. See my updated code in the message above in this thread. I now can show the latest flight callsign on my Lovelace dashboard.
What I still need want to accomplish is making a list of the last 15 flights with a time stamp for each flight. Somebody who can put me in the right direction?
In the future I want to enhance each entry with aircraft type, origin, destination by using some public web lookup services.
Hey flexer, did you get anywhere with making the list of the last 15 flights? any configs to share?
Me too interested… any news?
How do you show this in Lovelace and did you get any further on this?
my solution is VERY bespoke.
I track in and out events, and write these to a mysql database table.
I then have an sql sensor that pulls out the current list (ie, where there has been an in event, and no out event)
i use a markdown card in lovelace to display the data.
I can provide more detail if someone else wants to go down this path, but there is surely a better solution
@sh00t2kill I would love to get more details on your solution!
I am looking into figuring out when a certain flight enters my defined area, therefor your in event would be potentially very helpful.
Up to now i can only get it like this:
name: “3c6443”
lon: 11.8238
lat: 48.3671
heading: 263.66
icon: “plane”
but i would like to have the flight name or at least the callsign.
looking forward to your explanations
For some silly reason, i used http requests and wrote a simple endpoint to save events to a database.
I dont know why i did that, and in hindsight it seems silly.
If i were to re-tackle this (and i might one day) i would build some node red flows that do something along the lines of;
watch for opensky events
use a template node to get the attributes of the event
if its an in event,
write the time_in, and the callsign to the database
if its an out event,
update the record for the callsign, and write the time_out
For right now i have some scripts for event in, and event out, and those scripts call my http endpoint.
i then use an sql sensor to get the data into HA.
Here is what I am doing now:
scripts:
add_opensky_in:
sequence:
- service: rest_command.opensky_list
data_template:
payload: "{\n \"callsign\": \"{{ callsign }}\" \n}\n"
add_opensky_out:
sequence:
- service: rest_command.opensky_list_out
data_template:
payload: "{\n \"callsign\": \"{{ callsign }}\" \n}\n"
automation
- alias: Flight API
trigger:
platform: event
event_type: opensky_entry
action:
- data_template:
callsign: '{{ trigger.event.data.callsign }}'
service: script.add_opensky_in
id: 59db686497064a55be06b354abf8faa6
- alias: Flight API Out
trigger:
platform: event
event_type: opensky_exit
action:
- data_template:
callsign: '{{ trigger.event.data.callsign }}'
service: script.add_opensky_out
id: 6194bf1566ba4f06b4a00f1be3d003e2
the rest commands:
opensky_list:
url: http://xxxxx?status=in
method: post
timeout: 20
payload: '{{ payload }}'
opensky_list_out:
url: http://xxxxx?status=out
method: post
timeout: 20
payload: '{{ payload }}'
SQL sensor:
queries:
- name: "Current Flights"
query: "SELECT IFNULL(GROUP_CONCAT(callsign SEPARATOR \" \"), \"None\") AS current_flights FROM events WHERE when_out IS NULL"
column: current_flights
Lovelace card:
type: markdown
content: |-
Flights: {% for flight in state_attr("sensor.opensky_list", "flights") %}
* {{flight.callsign}} - {{flight.when}}
{%- endfor %}
title: Opensky Flights
The result:
As i mentioned, i would replace the automation, script, and rest commands with a node red flow, and directly save the events to mysql.
thanks a lot! I will look into it
Hi folks,
I propose a simple solution for this problem: use the internal Home Assistant SQLite database which already contains the events fired, as this is their primary storage place.
So - all you need is love and this piece of code to get a basic sensor running in no time:
sensor:
- platform: opensky
radius: 8
altitude: 5000
- platform: sql
queries:
- name: Active flights
query: |
SELECT
IFNULL(GROUP_CONCAT(callsign || ' (' || strftime('%H:%M',timestamp_entry) || 'z)',', '),"Clear sky") as flight_list --concatenation into a single sensor string with short entry timestamp in UTC
FROM
(
SELECT
JSON_EXTRACT(shared_data,'$.callsign') as callsign --extract callsign from json
,MIN(CASE WHEN event_type = 'opensky_entry' THEN time_fired ELSE null END) as timestamp_entry --first occurence of this callsign within the defined period
FROM "events"
INNER JOIN "event_data" ON event_data.data_id = events.data_id
WHERE event_type IN ('opensky_entry','opensky_exit')
AND ((JULIANDAY(CURRENT_TIMESTAMP) - JULIANDAY(time_fired)) * 86400) < 1800 --30 minutes max to get rid of possible missed exit events
GROUP BY json_extract(shared_data,'$.callsign') --group events per calsign
HAVING MAX(CASE WHEN event_type = 'opensky_exit' THEN time_fired ELSE null END) IS NULL --only flights which do not have exit events within the time frame
) ACTIVE_FLIGHTS --dataset of currently active flights with entry timestamps
column: "flight_list"
Hey - how do we use this? i’ve added this into my sensors section, but i’m not seeing any new entities or anything in my HA.
Hi
To have access to current list of all the active flights and make Lovelace card like this
This is amazing. I tried to get things to work via Opensky for almost a year now, with no luck. And your integration works in 10 minutes. Thank you.
