Help with MQTT Devices SQL Query

[AdmiralRaccoon]

Good Morning,
Refering to my feature Requtest, I must admit I do not have any SQL knowledge at all. :-(

Is it possible to edit the default query

select rowid, * from Devices where dev_Name not in ({s-quote}null{s-quote}, {s-quote}(name not found){s-quote}, {s-quote}(unknown){s-quote})

with an IP Filter to check weather the device is in an specific subnet and then continue or not?

Background: I have 2 subnets:

192.168.1.0 - My home VLAN -> I don´t want any "name_not_found" mqtt devices in Home Assistant.
192.168.10.0 - My guest VLAN -> I want all devices in Home Assistant, including the "name_not_found" devices

Thanks and a nice weekend
philipp

[jokob-sk]

Hi, try something in the lines of:

select rowid, * from Devices where dev_LastIP not like {s-quote}192.168.1.%{s-quote} and dev_Name not in ({s-quote}null{s-quote}, {s-quote}(name not found){s-quote}, {s-quote}(unknown){s-quote})

I can't test as I don't have subnets - you can also ask ChatGPT and test (just tell ChatGPT it has to replace ' with {s-quote}) to generate SQL queries