Feature request: Clear application log automatically daily

I am using Networx because I'm behind a metered connection.
Networx is very handy because it logs which applications are using bandwidth. It would be a nice feature if that 'application log' can be deleted automatically daily.
Basically, I want a day-by-day monitoring of bandwidth consuming applications... Would it be possible to (someday) implement such a feature?

It would be, but then someone else might ask for a week, or a month reset.

I think it's a better idea to change the software to record active applications every day and then build weekly and monthly reports. What do you think?

I use Windows 7 Task Scheduler to run these .bat files.
Control Panel > Administrative Tools > Task Scheduler

The .bat files are in a folder C:\networx_tasks
I copied the sqlite3.exe to a folder c:\sqlite3_run

Note that I have changed the default folder for the networx database to a different drive/partition so that I can back it up with my other 'data' on a regular schedule.
It's not necessary, but I think that it's 'safer'.
Here's the .bat file that I have scheduled to run daily at 7am to completely clear only the Applications table in the networx.db:

cd c:\
    taskkill /FI "IMAGENAME eq networx*"  /IM networx*  /T /F
    taskkill /FI "IMAGENAME eq explorer*" /IM explorer*      /F
reg delete "HKCU\Software\Classes\Local Settings\Software\Microsoft\Windows\CurrentVersion\TrayNotify" /v IconStreams /f
reg delete "HKCU\Software\Classes\Local Settings\Software\Microsoft\Windows\CurrentVersion\TrayNotify" /v PastIconsStream /f
    start "Shell Restarter" /d "%systemroot%"   /i   /normal explorer.exe

cd   C:\sqlite3_run
    sqlite3.exe "G:\Portable\NetWorx\StatsI\networx.db" "DELETE FROM application"

cd   C:\
    start /D "C:\Program Files\Networx"        networx.exe

and here's the .bat file scheduled to run at 7:05am, annually, on the first day of the first month of a new billing year, to clear the data more than a year old from the Usage table in the networx.db
You do have to change the 'number of the first day of the prior year' each year, it's not fully automatic. You can determine the number by keying a date into a spreadsheet cell and formatting it as a numeric value. My billing year starts on the 28th day of December, yours will likely be different.
This can be run any time, it leaves a full prior years history, I don't feel anything older is of any importance, you may choose to use a different 'date number' depending on how much history you want to keep. You might want to keep only the last 12 prior months and run the job monthly, etc.

cd  c:\
    taskkill /FI "IMAGENAME eq networx*" /IM networx*  /T /F
    taskkill /FI "IMAGENAME eq explorer*" /IM explorer*      /F
reg delete "HKCU\Software\Classes\Local Settings\Software\Microsoft\Windows\CurrentVersion\TrayNotify" /v IconStreams /f
reg delete "HKCU\Software\Classes\Local Settings\Software\Microsoft\Windows\CurrentVersion\TrayNotify" /v PastIconsStream /f
    start "Shell Restarter" /d "%systemroot%" /i /normal explorer.exe

REM ---- Delete old records in Networx table "usage" ---------
REM on 2015-12-28 delete records prior to 2014-12-28	 where number is = 42001
REM on 2016-12-28 delete records prior to 2015-12-28	 where number is = 42366
REM on 2017-12-28 delete records prior to 2016-12-28	 where number is = 42732
REM on 2018-12-28 delete records prior to 2017-12-28	 where number is = 43097

cd "C:\Sqlite3_run"
    sqlite3.exe "G:\Portable\NetWorx\Stats1\networx.db" "DELETE FROM usage WHERE DAY < 42001"

cd  c:\
    start /D "C:\Program Files\Networx"        networx.exe

Note: The sqlite3 is case sensitive, some sqlite3 words must be in UPPER case.


J

@ Andrew: yes, all good for me
My metered connection displays sort of a progress bar for bandwidth usage every day. Suppose I see a large consummation of bandwidth a week ago, then it would be nice if I could open Networx to see which application(s) were using bandwidth that day.

For me, it doesn't matter how it would be implemented, it would just be nice if it got implemented

If this is really important to you, you can always:

Click on the Applications tab and then click the [Export] button.
Save the file, as type = csv, to your desktop,
Close Networx, (you can't open the csv file while it's 'owned' by the running networx.exe)
Open the csv file, (this has a HUGE font- Andrew - can you make it smaller?)
Copy all appropriate cells (columns & rows) and paste into another 'perpetual' sheet, without copying the formats.
Add a Date column to the 'perpetual' sheet and fill in the date for the rows just copy/pasted.
Delete the csv file.
Restart Networx.
Clear the applications tab to start fresh for the next day (or run the bat file which I showed you in my post above).

This doesn't take very long once you've set up the 'perpetual' worksheet, I created one which you can set up in the attached screenshot.

Then Andrew doesn't have to worry about providing it in networx or can take his time...

J

As an afterthought: Andrew, is there a way to use sqlite3 to export the applications table to a csv file on the desktop while networx has been 'killed' in my bat file scenario? This would mean that the user wouldn't have to perform some of the steps above and would have lots of time to open and copy/paste from the csv file to the 'perpetual' sheet.
If the answer is yes, can you provide the sqlite3 instructions (and I'll add them to my bat file)?

I discovered how to copy the rows from the Application table to a csv file, here's the modified bat file to run daily.
Note that I specify a custom folder for the Networx db and the two spreadsheet files so a user will likely need to modify this for their own situation. I kept the spreadsheet names short: NWapps.csv and NWapps.odt. The last one could be NWapps.xls for Office users.

cd c:\
@echo on
REM Sqlite3 can't copy/delete records if networx.exe is active so kill it
taskkill /FI "IMAGENAME eq networx*"  /IM networx*  /T /F
REM =========================
REM Stop explorer.exe, reset the notification area icon cache, start explorer.exe
taskkill /FI "IMAGENAME eq explorer*" /im explorer* /f
reg delete "HKCU\Software\Classes\Local Settings\Software\Microsoft\Windows\CurrentVersion\TrayNotify" /v IconStreams /f
reg delete "HKCU\Software\Classes\Local Settings\Software\Microsoft\Windows\CurrentVersion\TrayNotify" /v PastIconsStream /f
start "Shell Restarter" /d "%systemroot%" /i /normal explorer.exe
REM --------------- must run sqlite3 from C: ----------------
REM ----- copy contents of the application table while networx is NOT running
REM ----- Delete the contents of the application table
REM ----- Restart networx
cd   C:\sqlite3_run
sqlite3.exe "G:\Portable\NetWorx\StatsI\networx.db" "SELECT * FROM application" > "G:\Portable\NetWorx\StatsI\NWapps.csv"
sqlite3.exe "G:\Portable\NetWorx\StatsI\networx.db" "DELETE FROM application"
cd   C:\
start /D "C:\Program Files\Networx" networx.exe

The csv has a huge font so the screenshot only gets a bit of the file.
The columns extracted this way are slightly different than those exported from networx itself so I've provided a snapshot of my spreadsheet which shows both formats.

This still requires some manual work by a user, either schedule it or run it manually at an appropriate time and then open both the csv and the ods and copy/paste without formats, note the date is already in the bat file version. The data is in bytes so if a user wants to see something else, then a column to calculate MB etc can be added (as I did). In any event, the largest numbers stand out quite clearly.

J

Quote

Praxis

Close Networx, (you can't open the csv file while it's 'owned' by the running networx.exe)
Open the csv file, (this has a HUGE font- Andrew - can you make it smaller?)

With the file being owned by NetWorx, it's actually a bug. It's been fixed in the latest build. As to the font size, it's not written to the CSV file as the file only contains data. I guess it's just the office program for some reason defaults to the huge font.

Quote

Praxis

As an afterthought: Andrew, is there a way to use sqlite3 to export the applications table to a csv file on the desktop while networx has been 'killed' in my bat file scenario? This would mean that the user wouldn't have to perform some of the steps above and would have lots of time to open and copy/paste from the csv file to the 'perpetual' sheet.
If the answer is yes, can you provide the sqlite3 instructions (and I'll add them to my bat file)?

Sure, there is a way:

sqlite3 -header -csv c:\ProgramData\SoftPerfect\NetWorx\NetWorx.db "SELECT * FROM application;" > myfile.csv

Thanks Andrew,
I see that the documentation that I found was 'wrong' because it specified the use of a "." rather than the "-" which works perfectly. I've changed my bat file to reflect the new code except that I've not used the "-header" spec, I just want the actual data rows.

There's a bug in LibreOffice where we're supposed to be able to specify the default font for new csv files, it doesn't work and I'll report it.

Note that Networx must be 'killed' in order to delete the records in the Application table, so there's still the need to perform the taskkills and starts.

J

cd c:\
@echo on
REM Sqlite3 can't delete records if networx.exe is active so kill it
taskkill /FI "IMAGENAME eq networx*"  /IM networx*  /T /F
REM =================================
REM Stop explorer.exe, delete the notification area icon caches, start explorer.exe
taskkill /FI "IMAGENAME eq explorer*" /im explorer* /f
reg delete "HKCU\Software\Classes\Local Settings\Software\Microsoft\Windows\CurrentVersion\TrayNotify" /v IconStreams /f
reg delete "HKCU\Software\Classes\Local Settings\Software\Microsoft\Windows\CurrentVersion\TrayNotify" /v PastIconsStream /f
start "Shell Restarter" /d "%systemroot%" /i /normal explorer.exe
REM --------------- must run sqlite3 from C: ----------------
REM ----- copy contents of the application table
REM ----- Delete the contents of the application table while networx is NOT running
REM ----- Restart networx
cd C:\sqlite3_run
sqlite3.exe -csv G:\Portable\NetWorx\StatsI\networx.db "SELECT * FROM application" > "G:\Portable\NetWorx\StatsI\NWapps.csv"
sqlite3.exe        G:\Portable\NetWorx\StatsI\networx.db "DELETE FROM application"
cd C:\
start /D "C:\Program Files\Networx" networx.exe
REM ================================