possible error in netusage.py SQL query

[gja3]

hello,
take this with a grain of salt until someone can confirm because I am not extensively familiar with this project.

I've been noticing some discrepancies in our NetUsage - App Data outputs and did some cursory review of the relevant code to see if I could figure out why. I noticed that netusage.py is using the following SQL query:

            select
            datetime(ZLIVEUSAGE.ZTIMESTAMP + 978307200,'unixepoch'),
            datetime(ZPROCESS.ZFIRSTTIMESTAMP + 978307200,'unixepoch'),
            datetime(ZPROCESS.ZTIMESTAMP + 978307200,'unixepoch'),
            ZPROCESS.ZBUNDLENAME,
            ZPROCESS.ZPROCNAME,
            case ZLIVEUSAGE.ZKIND
                when 0 then 'Process'
                when 1 then 'App'
            end,
            ZLIVEUSAGE.ZWIFIIN,
            ZLIVEUSAGE.ZWIFIOUT,
            ZLIVEUSAGE.ZWWANIN,
            ZLIVEUSAGE.ZWWANOUT,
            ZLIVEUSAGE.ZWIREDIN,
            ZLIVEUSAGE.ZWIREDOUT
            from ZLIVEUSAGE
            left join ZPROCESS on ZPROCESS.Z_PK = ZLIVEUSAGE.Z_PK

I believe that this is incorrect, specifically the final line which is comparing primary keys from ZPROCESS and ZLIVEUSAGE. this is apparently resulting in missing and/or incorrect data in the output.

based on the layout of the netusage.sqlite db, I believe the correct query should be as follows:

            select
            datetime(ZLIVEUSAGE.ZTIMESTAMP + 978307200,'unixepoch'),
            datetime(ZPROCESS.ZFIRSTTIMESTAMP + 978307200,'unixepoch'),
            datetime(ZPROCESS.ZTIMESTAMP + 978307200,'unixepoch'),
            ZPROCESS.ZBUNDLENAME,
            ZPROCESS.ZPROCNAME,
            case ZLIVEUSAGE.ZKIND
                when 0 then 'Process'
                when 1 then 'App'
            end,
            ZLIVEUSAGE.ZWIFIIN,
            ZLIVEUSAGE.ZWIFIOUT,
            ZLIVEUSAGE.ZWWANIN,
            ZLIVEUSAGE.ZWWANOUT,
            ZLIVEUSAGE.ZWIREDIN,
            ZLIVEUSAGE.ZWIREDOUT
            from ZLIVEUSAGE
            left join ZPROCESS on ZPROCESS.Z_PK = ZLIVEUSAGE.ZHASPROCESS

where the LEFT JOIN instead evaluates ZHASPROCESS from ZLIVEUSAGE against the primary key from ZPROCESS. using this amended query against the native sqlite db results in a more complete and believable output.

further, I noticed that a query in this format is used in DataUsage.py, but I've confirmed that the NetUsage - App Data output is definitely coming from netusage.py in the v2.0.4 binary release.

thank you for creating and maintaining this project and TIA for any time spent looking into this. if I am wrong, please do let me know.

[stark4n6]

@gja3 I will look into it, I believe I might have made the Netusage parser, I definitely did the DataUsage one. Netusage needs updated anyhow for new formatting so I can try and test some queries and see.