keep logs for more than a year

[markc]

Excuse me if this is possible but it's not obvious in any manual pages or docs that I've come across. Would it be possible to be able to log and report on periods longer than 12 months, like say over a 10 year period?

[vergoh]

The current database structure has a fixed size and it doesn't support (well) changing the duration of data being stored to it. I don't either have any plans on modifying the current structure as upgrades between structure changes have proved to cause problems far too easily.

However, I have a separate branch (https://github.com/vergoh/vnstat/tree/sqldb) in (slow) development which uses sqlite as database and will eventually replace the currently used solution. That's planned to allow users to have better control on the period and resolution being stored to the database.

[NoSuck]

From the TODO list:

everything gets currently stored forever

Nice! I would love to see hourly statistics from years gone by.

[vergoh]

Just a quick status update:

The sqldb branch has seen good development recently and from a feature point of view, all data can already be stored in the database forever when configured to do so (and disk space permits), including the new 5 minute precision statistics. I'm currently working on getting all the outputs working in the same way as in 1.x versions and then plan to proceed on adding some way of querying more specific time ranges.

[Misiek304]

I wanted to ask something about the sqldb branch. Can you maybe make a tool or something that would allow to export information from current database to the sqldb? Because of the limits of current database I'm making monthly backups to keep all the data. I would appreciate a way to to export data from multiple 1.x databases to one 2.x database.

[vergoh]

That might be a little bit too case specific to be included in vnStat itself and that data is likely to contain some duplication that shouldn't get imported.

I assume it's the daily data that you'd like to get import and possibly also older months? It probably shouldn't be too hard to write a script to run vnstat --exportdb to those 1.x databases one by one using an older vnstat binary and then inject the data directly to the sqlite database.

[Misiek304]

I already have those data exported as part of the backup script I wrote. I just need a way to import them to the new database from the oldest one to the newest to assure the survival of as much data as possible. The files are named by date.

[vergoh]

Just to clarify, does your backup script export the data with --exportdb or something else?

The import order itself shouldn't really matter as long as the exported data has a timestamp available. The database stores all the data using the timestamp this time instead of a round robin like indexing used in 1.x versions.

[Misiek304]

Yes, it's using --exportdb as backup method and then it compresses it into .tar.gz.
I can upload you an example if you'd like.

[vergoh]

Something like this should do the trick if executed for every exported v1.x database: https://gist.github.com/vergoh/1619965f1132fda3412b1031c822be1e

[Misiek304]

Yes, I think that would be perfect. I will try it when you'll be ready to release a stable version of vnstat 2.0 or at least a beta. Thanks.

[hmw42]

Hi,

thanks for providing the conversation script. It spared me some work ;). I had to adapt the indices of the data fields to get the expected results:

rx = int(c[3])10241024 + int(c[5])*1024
tx = int(c[4])10241024 + int(c[6])*1024

Regards
hmw

[vergoh]

rx = int(c[3])*1024*1024 + int(c[5])*1024
tx = int(c[4])*1024*1024 + int(c[6])*1024

Right, my bad for not really testing that it did was was intended. Fixed now also in the previously provided link.

[vergoh]

sqlite database implementation has been merged from the branch to master and will be included in vnStat 2.0 release. Data retention is user configurable.