NetTraffic xml export to Excel

[itge13]

the statistics in the program itself look like this

Code

Date              tot            received       sent            time         cursor distance
2019-02-07	28.56 MiB	26.91 MiB	1.65 MiB	0.00:57:17	    14.79 m

the export xml looks like this:

Code

-<StatDay>
<key dy="19904" dx="30080" ul="1725446" dl="28220776" tm="34377250805" ts="636850944"/>
</StatDay>

how can I import that in excel? I do not even understand how to get the date back
ts seems to be the date, it is the only variable which doesn't change over the day.

[skwire]

What is NetTraffix?

[itge13]

What is NetTraffix?

my bad. NetTraffic https://www.venea.net/web/nettraffic

[Ath]

Excel can open an xml file just fine as a worksheet:

• Start Excel (2007 or later I'd advise)
• Use File-Open to fetch the xml file
• Choose either the 1st 'As an XML table' or 2nd 'As a readonly workbook' option when prompted
• • The 1st option will induce a message about a missing schema, just continue with OK
  • The 2nd option will include some of the metadata as column headers, it can be removed if desired
• Process & massage the data as required/desired

[itge13]

@ath
yeah I got that, but how can i translate
<key dy="19904" dx="30080" ul="1725446" dl="28220776" tm="34377250805" ts="636850944"/>
to
2019-02-07 28.56 MiB 26.91 MiB 1.65 MiB 0.00:57:17     14.79 m

[Ath]

Well, I tried a couple of possibilities, and the closes guess I have is that 'ts' is related to 1/1/0000, in seconds, but with a factor 100 off. Guess that's close enough for determining the data.
ul and dl are quite self explanatory (million bytes).

Best advice would probably be to contact the developer and ask for documentation.

[skwire]

I'm pretty positive that "ts" value is a Unix timestamp.  Here's an AutoHotkey example showing how to convert it:

Code: ( autohotkey)

myTimestamp := "636850944"
sFormatString := "dddd, MMMM d, yyyy @ H:mm"
FormatTime, myFormattedTime, % timeUnix2Human( myTimestamp ), % sFormatString

MsgBox, % myFormattedTime

timeUnix2Human( unixTimestamp )
{
    returnDate = 19700101000000
    returnDate += unixTimestamp, s
    Return returnDate
}

This snippet returns a value of: Wednesday, March 7, 1990 @ 23:02

The year seems a bit off, but does that sound about right, @itge13?

[Shades]

why export to XML?

The netflow protocol has its own format and (free) reporting tools for about any operating system available. Most decent router software suites have this as standard built-in. Call me silly, but the router device in your home or company is the true connection hub where all traffic goes through, making it the most efficient location to track any and all traffic on your network.

Reports can be exported to excel, HTML, csv. Functionality that is built into the reporting tools by default. Some of these reports allow you to export the content in whatever shape or form. And most router software and reporting tools have the option to take the content coming in through netflow and display it graphically, using time-lines, statistics and/or charts, practically in real-time, keep track of history etc.

Collecting and processing of netflow data can be done on the router itself or on a dedicated (virtual) server, if you so wish. The ISP I worked for had such software already installed on the NT4 windows box assigned to my care. That software was already so complete and capable, so is was also used to bill the customers of that ISP. That was still in the days where you had to pay for the amount of data you pushed/pulled to/from the Internet.



So I don't know what problem Nettraffix is trying to solve here, just by by using XML as export.

[itge13]

@skwire
my example is the real data. Somehow this
<key dy="19904" dx="30080" ul="1725446" dl="28220776" tm="34377250805" ts="636850944"/>
translates to
2019-02-07  28.56 MiB  26.91 MiB  1.65 MiB  0.00:57:17      14.79 m

So the date should be 2019-02-07 and the time is the time logged at this day. Here for almost an hour.
here are some more examples:

Code

-<StatYear>

<key dy="358122" dx="582830" ul="312167827" dl="9446475679" tm="780111644557" ts="636818976"/>

</StatYear>


-<StatMonth>

<key dy="358122" dx="582830" ul="312167827" dl="9446475679" tm="780111644557" ts="636845760"/>

</StatMonth>


-<StatDay>

<key dy="19904" dx="30080" ul="1725446" dl="28220776" tm="34377250805" ts="636850944"/>

<key dy="139586" dx="221126" ul="44928805" dl="1278397665" tm="370183712706" ts="636851808"/>

<key dy="76867" dx="131457" ul="43921601" dl="325446856" tm="125459796269" ts="636852672"/>

<key dy="121765" dx="200167" ul="221591975" dl="7814410382" tm="250090884777" ts="636853536"/>

</StatDay>

which is

Code

2019	                  9.57 GiB	9.25 GiB	320.59 MiB	0.21:55:07	289.67 m
2019-02	          9.57 GiB	9.25 GiB	320.59 MiB	0.21:55:07	289.67 m
2019-02-07         28.56 MiB   26.91 MiB   1.65 MiB     0.00:57:17  14.79 m
2019-02-08	1.23 GiB	1.19 GiB	42.85 MiB	0.10:16:58	107.24 m
2019-02-09	352.26 MiB	310.37 MiB	41.89 MiB	0.03:29:05	62.45 m
2019-02-10	7.95 GiB	7.73 GiB	233.91 MiB	0.07:11:32	105.13 m

@shades
I am using a Huaweii router and in the only statistics I see is the total amount since the connection was set up. And this message "Volume statistics provided here are approximate. For accurate statistics and details of charges refer to your bills." So yeah

Also if someone has a small tool/program to log such data to excel I am willing to change.

[itge13]

got an answer from the author:
Hello,

thank you for contact.

In one of upcoming releases I will add export to CSV (Excel compatible).

Below format explanation.

Best regards,
Michael

Format
======

ts - beginning of period
------------------------
Number of 100 - nanosecond intervals (C# ticks) since 12:00:00 midnight, January 1, 0001, divided by 1000000000.

dl, ul - number of bytes
------------------------
Bytes: dl - downloaded, ul - uploaded.

dx, dy - number of pixels
-------------------------
Distance approximation calculated as square root of pw * pw * dx * dx + ph * ph * dy * dy. Where ph is pixel height, pw is pixel width (ph = pw).

tm - work time
--------------
C# ticks / 1000000000. 1 tick = 100 nanoseconds.

Links
=====

C# ticks to date time
---------------------
https://www.venea.net/web/net_ticks_datetime_converter#net_ticks_to_date_time_and_unix_timestamp_conversion

C# ticks to time span
---------------------
https://www.venea.net/web/net_ticks_timespan_converter#net_ticks_to_time_span_conversion

Pixel size
----------
https://www.venea.net/web/pixel_calculator

[itge13]

ok guys I know now how to translate the values. But not how to extract them.
I try to achieve it with powershell. But how can I separate all values of the same line with comas?
<key dy="19904" dx="30080" ul="1725446" dl="28220776" tm="34377250805" ts="636850944"/>
<key dy="139586" dx="221126" ul="44928805" dl="1278397665" tm="370183712706" ts="636851808"/>
should looke like this:
199904,30080,1725446 etc
139586,221126,44928805 etc

[Ath]

Thanks for sharing. And I'm happy my educated guess about the ts field was quite close

Can you attach an original xml file to a reply? Scraping it from your messages doesn't provide the original structure, you seem to have taken clips from a webbrowser (the dashes in front of the <Stat elements give it away ) (Finally downloaded NetTraffic and did a testrun )

[Ath]

Well, I mocked up a batchfile, using xmlstarlet to be downloaded here, that converts all "NetTraffic <date-time>.xml" files of current directory into a single NetTrafficAll.csv file.

(Used the PowerShell highlighter as Batch isn't supported by the board)
You'll need to adjust the path to the xml.exe file, and check the other settings as 'documented'
NOTE: No data-conversions done here, Excel can do that better and easier

Code: ( powershell)

@echo off
:: Set the relative path to your xmlstarlet install:
set XML=..\Downloads\XML\xmlstarlet-1.6.1\xml.exe
:: Change filemask if desired
set "FILEMASK=NetTraffic 2*.xml"
:: Change result outputfile as desired, keep the .csv extension, when adding spaces, surround with quotes like FILEMASK
set OUTFILE=NetTrafficAll.csv
:: Add StatHour to this list if desired
set "ELEMENTS=StatYear StatMonth StatDay"
:: Choose csv separator, either , (comma) or ; (semicolon)
set CSVSEP=;

set XMLARG1=sel -T -t -m NetTraffic/
set XMLARG2=/key -v "concat('
set XMLARG3=%CSVSEP%',@ts,'%CSVSEP%',@tm,'%CSVSEP%',@dl,'%CSVSEP%',@ul,'%CSVSEP%',@dx,'%CSVSEP%',@dy)" -n

:: Write header
echo Period%CSVSEP%ts%CSVSEP%tm%CSVSEP%dl%CSVSEP%du%CSVSEP%dx%CSVSEP%dy>"%OUTFILE%"
:: Loop all files from FILEMASK
for %%F in ("%FILEMASK%") do (
echo File: %%F
:: Fetch all configured elements
for %%G in (%ELEMENTS%) do (
%XML% %XMLARG1%%%G%XMLARG2%%%G%XMLARG3% "%%F" >>"%OUTFILE%"
)
)

NOTE2: The csv file will be fully replaced each run of the script!

[itge13]

@ath thanks a lot for your work. Like a charm

if anyone else needs it - excel formulas:

for date =((ts*POWER(10;2)/60/60/24)-693593)
for time =((C9/10000000))/24/60/60)
for dl/ul in MiB =dl/2^20
cursor =SQRT(pixel size*pixel size*dx*dx + pixel size*pixel size*dy*dy)

* pixel size in mm with the same name from here https://www.venea.net/web/pixel_calculator