For the last 40 hours, I was trying to save my data from the glasswire’s evil twin monster, and I finally managed to save it, so I want to write what I did here, in case it helps someone.
First of all, I would like to thank to the glasswire team for using standard sqlite databases and not a custom thing, otherwise I would just uninstall glasswire and try to find an alternative. (there aren’t. No other program can show how many data an application transferred to a certain host.)
Story time
I woke up yesterday and glasswire was crying in the bottom saying it cannot connect to the local server. I enabled logging in the config file but I didn’t get any clear error. (I don’t fully remember, but it was something like statistics manager failed to start.)
I don’t know much about databases, but I had a program, “DB Browser for SQLite”, I opened each of the databases, selected “Integrity check” from the Tools menu, and all of them returned “ok”, so none of my databases were corrupted. (I was wrong.)
Fortunately I had a backup, unfortunately from 2 months ago. After lots of trying, backing up, restoring and changing things around, I found that if I copy the old glasswire.db file from my backup, replace the current one in C:\ProgramData\GlassWire\service, (yes I haven’t upgraded to 2.3 yet, it should be service-full now.), restart the service from Windows services, the service now starts. (It would quickly close before.)
After starting the service, if I open the Glasswire app, press connect again, it works, and then I can quit the glasswire app, stop the service, replace the old glasswire.db (from 2 monhts ago) with the current one (that I backed up), I can restart the service and Glasswire app can run fine now. Except that now it lost last 1 month of data.
(Opening the glasswire after changing the .db file was a requirement, without it, after restoring the current database, the service would crash instantly again.)
End of story time.
Glasswire stores internet usage for every second, at C:\ProgramData\GlassWire\service\stats\glasswire_stats_1sec_16xxxxxxxx.db file. After some time, it groups those usages for each 30 second, at the 30sec_16xxxxx in the same folder, and after some more time, it groups those at glasswire_stats_600sec databases.
I looked at the Glasswire app, and found that I have no data after June 8. One of the 30sec databases starts just that day. So I initially tried looking that file to see if there is anything wrong with it, didn’t see anything unusual, tried partially deleting the data in that file (in case there is a corrupt record), creating a new database and copying all of the records to the new one and changing that file with the new one (in case the data is fine but the database is corrupt), and even merging that file with another 30sec database i had in that folder, (that started 3 days ago, coincidentally I guess.), nothing worked.
After some more careful looking around, I realized that the problem wasn’t in the 30 sec database, but the 600 second one. I didn’t realize that there was a new 600 second database created at June 8 at the same time, and I didn’t realize it was corrupt.
An empty glasswire database file, with the SQL tables created, weighs around 28 KB, while that 600sec file was only 4 KB. Same as an empty SQLite database without any tables. And the db browser program was returning “ok” at the integrity check, because even though there were no tables, there were no errors either. (DB Browser was not showing any tables for the corrupt 600sec database, but I didn’t notice that earlier.)
Solution
So it was clear that the corrupt database was the 600sec database created a month ago. I still had last month’s data at a 30sec database, and I had some free time.
I duplicated another 600sec database (from the same folder), deleted all of the records in it from DB Browser program, compacted the database and saved it with the name of the corrupt 600sec database, now I had an empty 28 KB sqlite file.
(I tried replacing the old corrupt 600sec database with this new one, glasswire worked again. There was no need to restore and old glasswire.db and delete it back. The old glasswire.db was simply creating a new 600sec database (because it was before June 8), therefore fixing the Glasswire service from crashing.)
Then I wrote a little python script that reads all the records from the 30sec database, groups those into 600 seconds, and inserts new records to the empty 600sec database I just created. After that, I had all of my history back again in GlassWire.