Help

Looking for help on a couple of items where Google has failed me..

1. Friedland door/window contact twin-pack DC-55.   I have them running – I have RFLINK talking to them – I understand the codes for door opening  and door closing and another for the internal spring being released (anti-theft) – but there is another signal which occurs occasionally – I’m assuming it is something like “battery ok” – but that suggests another “battery not ok” – but I have NO info on this – anyone?

Status: Still no feedback on this – and Friedland’s site has no useful info that I can find.

2. On the RFLINK board I have a little whip aerial – it is 70mm high and has the miniature screw fitting for the board at the other end.  What inexpensive, simple antenna would be SIGNIFINANTLY more sensitive without being directional.  From my outside office I can reach just into the house but only just.

Status – some GREAT feedback here but nothing definitive other than it is looking like a DIPOLE might’ve been a better bet.

3. Grafana and INFLUXDB.  I have upgraded Influx to 1.2 (see the blog entry if interested) but a problem persists – I have a graph showing several temperature lines – inside and outside stuff – nothing extreme.  I have two of them with duff readings in them – one is minus 400 and the other is +6000.  I cannot figure out how to delete readings outside of a range in InfluxDB (on the Pi) and I cannot seem to get WHERE clauses to work in Grafana.   WHERE value < 100 for example just makes the line disappear.   Anyone know the answers?

Status: Solved!

InfluxDB (I’m on v 1.2) you can only delete readings by time – but you CAN do normal queries which return the time.

The CLI (command line interface) is called “influx” – enter that and you end up in an interface you can get out of with “quit”.

While in that interface you will see a prompt >

use x  (where x is the name of the database)

select * from y where value>z

where y is the name of your measurement and z if the value – well, this is what I needed it might be equal to, less than etc etc.

You get a bunch of results with the timestamps.

To delete a value – well, here’s an example.

delete from uk_office_temperature where time=1490109437728705735

and that is it – it is that simple – when you know how – NOW I can clean up my database and in fact I’ll now add filters on the incoming data to stop this happening again.

Thanks are given in the comments – thanks to all for feedback on these subjects – now those of us who didn’t know – do.

 

Facebooktwitterpinterestlinkedin

39 thoughts on “Help

  1. delete a single measurement in influxdb:
    do this via influx CLI, because in webgui you do not get the right time-format:

    select * from Temperature WHERE Location=’Kinderzimmer’ AND Value>100

    you get a list with timestamps, take this timestamps an insert in the below statement:

    delete from Temperature where time=1490803304773943371

    1. Krikk – that is the most helpful thing I’ve seen about Influx – about which I know zilch.. could you go one stage further – the command line interface – cli ??? Do you have to run that from a particular directory and what is the name of it? I tried something I found on the web and the Pi just came back not knowing what the hell I was on about…

      I feel like I’m almost there….. one last piece of info…

      1. CRACKED – slightly more involved than you suggested but only slightly – so for the benefit of those who are where I was 5 minutes ago…

        The CLI for InfluxDB is influx.

        So just type “influx” – you will enter the CLI and you will get a prompt

        Firstly – tell it what database you are using.. in my case…

        use logger

        It will respond to say it is using database logger.

        NOW you can give it a query – in my case… including the result..

        > select * from uk_office_temperature where value<0
        name: uk_office_temperature
        time value
        ---- -----
        1488406931386000000 -3944
        1490109437728705735 -3047

        And so you see two records whose values are utter crap.

        And now

        delete from uk_office_temperature where time=1488406931386000000
        delete from uk_office_temperature where time=1490109437728705735

        > select * from uk_office_temperature where value<0
        >
        >
        BANG – and the dirt is gone – as they say in the adverts – problem solved… I cannot tell you how happy I am.

        Thank you Krikk – problem solved – understanding gained.

          1. Be wary of old links like that. Influx is mutating at 1.2. and yes I am doing queries in Node Red. All working well.

            1. no, sorry, my point was not clear, eventually…
              i was saying that those commands you run via cli, can be run via an exec node in nodered, and piping the json output you can delete those spikes all without even opening putty and influx cli command…

              version could be higher now, but if those cli switches still exist (and seems so: https://docs.influxdata.com/influxdb/v1.2/tools/shell/ ), then you can surely do all in nodered… or even be alerted when a new spike arrives… well, you know how 🙂

              1. Yes of course – and i already do a lot of stuff like that through the exec – but for the purposes of clarifying how to clear a location I was avoiding complexity. This is the first time I have seen a simple – and full explanation of how to clear a single location in InfluxDB thanks to information from Krikk

  2. I didn’t read through everything, but in case this hasn’t been solved yet.

    You CAN delete single errant entries in InfluxDB, but you must do it using the time field. So first you select {whatever} where the values are outside the range you want, then you jot down the values of the time field and you delete based on those.

    I did it about 10 days ago, but like an idiot I didn’t cut and paste what I had done as an example.. But I assure you.. It can be done.

    1. Thank you and thanks to all. I bought one of the little rod aerials but with several turns in it – no better than the simple rodI bought in the first place… so I really do need to try something a LOT better.

      Thanks.

      1. Hey Pete,
        This is good link for a making a cheap 433 MHz aerial for individual devices,
        http://www.instructables.com/id/433-MHz-Coil-loaded-antenna/

        But as the RFLINK device has an SMA connector already, a pre-made aerial with a long lead wins. (i.e. no joins) simply because it can be postioned high up. When I got the RFLINK kit from Nodo, I got this aerial and it just works well
        https://www.nodo-shop.nl/en/antennes/102-antenne-433mhz-3dbi-sma-male-met-magneetvoet.html

        Personally, I wouldn’t get this one
        https://www.nodo-shop.nl/en/antennes/1-antenne-433mhz-3dbi-sma-male-met-magneetvoet.html
        because I think it would be more difficult to discretely position in a room and I don’t have the main benefit of a metal groundplace to mount it on.

  3. i guess you could try ’empirically’ – power the device from an adjustable supply and slowly drop the voltage until it is passed functioning – if the ‘low battery’ theory is correct, that should trigger it.]

  4. Hi Pete,
    ” – I’m assuming it is something like “battery ok” – but that suggests another “battery not ok” – but I have NO info on this – anyone?”

    Perhaps its just a heartbeat? Is it “occasionally”…at irregular inteervals or exactly every x minutes or so?

    1. Well Greg – not regular at all. I never heard it from implementing door security on my office until late at night (I’d made the mistake of assuming only two messages) the alarm went – and when I checked – here was the is odd command – so not every x minutes, more like every x hours,

      Someone, somewhere must know, surely.

  5. For antennas bigger is better. At 70 cm that’s a 1/8 wave antenna so look for a 1/4 wave or bigger antenna like this one – https://www.digikey.com/product-detail/en/rf-solutions/FLEXI-SMA-433/FLEXI-SMA-433-ND/2781767

    It may also help to have an extension cable so you can move the antenna to a better location (higher, away from RF noise sources, etc.), something like this 5/8 wave – https://www.digikey.com/product-detail/en/rf-solutions/MB450-RFS433/MB450-RFS433-ND/1999413

    1. Thanks for that – Digikey not a lot of use for me – buying from the US to UK always involves delays, duty, VAT and any other charges the post office/courier can manage. USA to Spain is even worse. Best options for me are UK or from mid-month on, China.

      1. These were just examples, I should have said as such. I figured you’d want to use the online stores that are best for your location.

        Here’s a 5dbi antenna that should work better than what you have – https://www.aliexpress.com/item/5-piece-lot-GSM-433Mhz-5-dbi-Antenna-SMA-Male-Connector-Rubber-Wireless-Aerial/32662476852.html?spm=2114.01010208.3.125.PCunCI

        Or make a simple dipole – https://nathan.chantrell.net/old-stuff/radio/radio-scanning/how-to-make-a-simple-dipole-antenna/

        1. Thanks for that Kris – I rather fancy having a go as suggested in Nathan’s article at making my own for fun – but first I need a supply of the little screw connector and lead… not sure what they’re called – female, maybe 6mm dia with a little pin inside?

  6. Pete I’ve just tried my temps and <100 works
    In the metric tab you can toggle edit mode for raw SQL
    SELECT ch1 FROM "my measurement" WHERE ch1 <30

    In terms of deleting you can't you can only Drop.
    You could Drop series but that doesn't allow where on value!!

    So best approach would be to create a new Measurement from the old using INTO which excluded your out of duff readings. Have a look at
    https://docs.influxdata.com/influxdb/v1.2/query_language/data_exploration/#description-of-syntax-4

    You can use the Influx web ui to experiment http://localhost:8083/

    1. re InfluxDB. Yes, I had that problem previously too.

      You can overwrite an entry so you could manually “correct” the points in error. You should also be able to delete entries, the docs aren’t very clear on this as deleting has always been a bit off the radar for Influx.

      https://docs.influxdata.com/influxdb/v1.2/query_language/spec/#delete

      The trick is to give a time range that only encompases the point in question. It has to be by time as well, duration doesn’t work.

      Other things you can do are to filter out the incorrect data in your Graphana query, though that can still throw out your consolidation/summary data.

      Some people have recommended using a TRUE/FALSE value field as well so that you only have to update the value to FALSE for errant entries & filter them in your queries again.

    2. Right – INFLUX partly solved – upgraded to version 1.2 – no change – rebooted – fixed. I can now filter results. New version has DELETE but does not (at least easily) let you delete a single point – what kind of database is this, he says.

      I COULD use :8083 had I opened the port last time I was in Spain 🙂 I’ll do that in a couple of weeks when I’m back there if the ui has a password on it.

      Thanks. One down.

          1. we discussed in a previous blog entry: ngrok free does not allow to have always the same name, so bit disappointing… and what i’m talking is a stable connection between 2 sites, so you can go from one to the other as if they were a single large lan, with all the traffic between encrypted…

        1. SSH tunnels FTW. They always work, and it’s really easy.

          I assume putty is used, and you are able to connect to Spain using ssh? In putty, on the left (in the tree) go to Connection -> SSH -> Tunnels. Enter 8080 at the Source port, and choose dynamic. Then press Add.

          Now configure the socks firewall in your firefox. Go to options -> Advanced -> Network -> Connection settings. Set it to manual. Only set the “SOCKS Host” to 127.0.0.1 and port 8080.

          Now when you use firefox to browse to 192.168.1.1 or whatever local IP on the remote network, it will browse to the remote network. You can now configure your router. Or you could leave your tunnel open, and make sure your services use the socks proxy as well.

          Using Firefox with a Putty SSH tunnel as a SOCKS proxy

          1. exactly what i mean, i had no time to be more specific, sorry 🙂
            an other thing that can be done is using apache reverse proxy capabilities (as we have already apache there…), so we can do this:

            http://ip:1880 –> http://ip/nodered
            http://ip:10000 –> http://ip/webmin

            and so on, all on port 80, and let apache do the internal redirects… 🙂

            the only drawbacks of proxy is if you want to access those services from smartphone… a vpn is preferred, in that case, and it’s not that complicated… but then you’ll have as a MUST to put some sort of smartphone protection, like pin or pattern… i don’t use it…

          2. I’m firefighting emails here but you seem to be referring to setting up a single PC? Rather than a miriad of devices that need to access Spain – from tablets to phones to PCs…. ?? Generally I use Chrome…

            I use Putty but only via WinSCP.

      1. re Influx, yes I think you probably still need to specify a start and end time that wraps the point in question.

        Thing is that these kind of databases are designed for very high performance when capturing many datapoints in time. Generally, deleting individual points from that data stream would be a fairly wasteful use of your excellent brainpower. 🙂

        The emphasis instead in on automatically aging data to summary tables and auto-trimming the detailed data which InfluxDB is excellent at.

        Please don’t open your Node-RED to the Internet without reading the issues raised in the link I’ve given below to see if you are bothered by them (hint, I think you probably should be). As suggested below, SSH is your friend along with port forwarding. This gives you an easy to set up, personal VPN from your desktop to the Pi.

        Though I would recommend moving SSH from its default port if opening to the Internet, stops your logs filling up with botnet breakin attempts. You can do that simply using your router to forward the port.

Comments are closed.