Extract usage information for specific IP address

We have a feature request we would like to see in the product.
In data usage report, as it stands now, to export the Data Usage we click a tab on a date (or tabs on several dates) and export the data to an Excel file. What we would like to see is a feature that allows to open the Usage Report tab, enter an IP address (or range of IP addresses) and then enter a date range. Then we would be able to see on what days during the specified period the IP address(es) hit the system.

Example:

1. We enter that we want to search for IP address 172.031.129.091
2. We want to search from 1 April to 30 April 2018

We would then get back something like this, much like we get now, but for a specific range.

2-Apr-2018 8089772788 172.031.129.091 88.1 MB
3-Apr-2018 8089772788 172.031.129.091 63.5 MB

For customer-based reasons, we had to set up the rules individually per IP address instead of a range.

This is a rather specific report and instead of having it implemented in the software itself, it's possible to extract the data directly from the database.

You can use an SQL statement like this:

SELECT DAY, R.NAME, SRC_ADDR_VALUE,
SUM(RECV_BYTES) AS BYTES_IN, SUM(SEND_BYTES) AS BYTES_OUT 
FROM DAY_USAGE DU
JOIN STREAM S ON DU.STREAM_ID = S.ID
JOIN RULE R ON S.RULE_ID = R.ID
WHERE SRC_ADDR_VALUE = '1.1.1.1'
GROUP BY 1, 2
ORDER BY 1, 2, 3

To achieve that,

1. Download the SQLite command line shell and extract sqlite3.exe.
2. In a command prompt execute this:

sqlite3 -csv "C:\ProgramData\SoftPerfect\Bandwidth Manager\Bandwidth Manager.DB" "SELECT DAY, R.NAME, SRC_ADDR_VALUE, SUM(RECV_BYTES) AS BYTES_IN, SUM(SEND_BYTES) AS BYTES_OUT FROM DAY_USAGE DU JOIN STREAM S ON DU.STREAM_ID = S.ID JOIN RULE R ON S.RULE_ID = R.ID WHERE SRC_ADDR_VALUE = '1.1.1.1' GROUP BY 1, 2 ORDER BY 1, 2, 3" > C:\path-to\somefile.csv

This will join three tables and extract the data you requested, for sample address 1.1.1.1 as date, rule name, source IP address, bytes inbound and outbound. The result will be saved to a CSV file that can then be open in MS Excel.

If the IP addresses are configured as destination IP addresses, replace SRC_ADDR_VALUE with DST_ADDR_VALUE. If you omit the WHERE line, the report will be made for all IP addresses.

You can also use the SQLiteSpy tool to explore the database and play with the queries.