How to DROP DATABASE in a MAC format that refuses to delete

1

Hi there I created a Banana PI R1 device that monitors and catalogs all network parameters and activity from remote servers and Internet connects - you simply plug it in and it snoops the entire network cataloging it usage, speeds, ping results .ETC ( pretty much everything) then once a day sends the data to influxDB it monitors 50 servers and internet connection in remote areas of the world as it acts as either router or a transparent bridge - i built the software so that you simple flash an SD insert it into the PI and it up and running in 30 seconds my problem is there seams to be a bug in influx as it will create a database based automatically on the INFLUXDB server in the MAC format and everything works as expected I used the MAC as an easy identifier as IPs change so do server names MACs stay the same — BUT you can not drop it later on - it will give you an error

example :DROP DATABASE 02:07:06:XX:XX:XX
ERR: error parsing query: found 02, expected identifier at line 1, char 15

is there anyway to delete these DataBases now after they being created or am I missing something

2

Try double quotes around the db name.

3

Also:

  • although this may seem immaterial in your environment, that’s an invalid filename on Windows; think it’s best to allow for a test env to be set up on a Windows box
  • MACs don’t stay the same if the NIC is swapped (and there are also user-settable MACs); if there’s a chance the device might have a partial field replacement you should use another unique identifier
4

thanks will give that a try tomorrow – not connected to the server at the moment. - I am using a linux environment so the file name is okay- as to swapping out NIC they are built in on BPI R1 ( actually 5 ports 6 if you include the wifi). perhaps i can format the MAC into a windows friendly dash or underscore… MAC is derived not from the servers but the PI as it the one transmitting the data… - truthfully there no way to change the MAC on theses devices even via a software switch ( as I have tried) - the way it calls the MAC it always brings up the original MAC … so as an identifier it is pretty good- even if someone steals one of the 50 devices - as soon as it calls home it automatically lists it current iP the provider and location of the device.

5

I was talking about the remote device’s MAC, yes. I suppose the remaining question is if someone steals one of the devices, do you want to have a continuous series of data from that location. I wouldn’t use the MAC myself but a GUID.

6

thank you that worked with the double quotes to delete the database–

curious do you know of any way to to import a CSV to influxdb I tried a couple but they do not seam to preserve the the timestamp cvs to influx it finds the time stamp but does not preserve it

7

I don’t know exactly what you mean by finds-but-does-not-preserve but to import, as far as I am aware, you can’t use an actual CSV. You have to use what I’ve nicknamed an “IDV” (Influx Delimited Values) file which follows the line protocol in the docs. (It’s comma-delimited tags, a space, comma-delimited fields, a space, timestamp.)

8

oh I used say csv-to-influxdb – GitHub - jpillora/csv-to-influxdb: Import CSV files into InfluxDB as described in influx docs ( https://getkotori.org/docs/handbook/influxdb.html )
sample data csv:
timestamp uptime loss median ping1 ping2 ping3 ping4 ping5
08/08/2017 11:48:00 0 0.06 0.1 0.04 0.06 0.1 0.14 0.19
08/08/2017 12:00:00 0 0.1 0.22 0.11 0.15 0.22 0.32 0.49
08/08/2017 12:12:00 0 0.1 0.17 0.09 0.12 0.17 0.23 0.37
08/08/2017 12:24:00 0 0 0.04 0.04 0.04 0.04 0.04 0.06
08/08/2017 12:36:00 0 0 0.05 0.04 0.04 0.05 0.06 0.1
08/08/2017 12:48:00 0 0 0.04 0.04 0.04 0.04 0.04 0.04
08/08/2017 13:00:00 0 0 0.04 0.04 0.04 0.04 0.04 0.04
08/08/2017 13:12:00 0 0 0.16 0.04 0.12 0.16 0.23 0.49
08/08/2017 13:24:00 0 0 0.1 0.04 0.05 0.1 0.13 0.24

/csv-to-influxdb --batch-size=1–timestamp-format="08/08/2017 23:20:00 " --server=http://192.168.9.172:8086 --database=$mac --measurement=$name /var/www/html/csv_daily/$name.csv `;

it outputs to influxdb but did not preserve the timestamp
example output in influxdb

time loss ping1 ping2 ping3 ping4 ping5 median uptime

1502298542965028814 0.06 0.04 0.06 0.10 0.14 0.19 0.10 0.00
1502298542996392526 0.10 0.11 0.15 0.22 0.32 0.49 0.22 0.00
1502298543029416943 0.10 0.09 0.12 0.17 0.23 0.37 0.17 0.00
1502298543063528513 0.00 0.04 0.04 0.04 0.04 0.06 0.04 0.00
1502298543096788738 0.00 0.04 0.04 0.05 0.06 0.1 0.05 0.00
1502298543130769074 0.00 0.04 0.04 0.04 0.04 0.04 0.04 0.00
1502298543164301005 0.00 0.04 0.04 0.04 0.04 0.04 0.04 0.00
1502298543197642822 0.00 0.04 0.12 0.16 0.23 0.49 0.16 0.00
1502298543231279787 0.00 0.04 0.05 0.10 0.13 0.24 0.10 0.00
1502298543265007347 0.00 0.04 0.04 0.04 0.05 0.06 0.04 0.00
1502298543298525886 0.10 0.04 0.04 0.05 0.07 0.08 0.05 0.00
1502298543340103765 0.00 0.04 0.04 0.04 0.04 0.05 0.04 0.00
1502298543382338650 0.10 0.04 0.04 0.04 0.05 0.05 0.04 0.00

if it does not locate the timestamp properly, it inserts the timesamp as and extra item… I will try converting the timestamps to unix timestamp and see it makes a difference but I think it probably not going to work … but I will give it a try… the reason is I use on the pi devices RRD containers to store info locally. and when you login to the device it will display all data in a nice visual format and a fixed size that is set and forgot … some of the RRD data i convert daily to CSV and then used to update centralized database daily - i was trying to make it work on a influxdb currently for more options