NetTraffic xml export to Excel

[itge13]

the statistics in the program itself look like this

[Select]

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:

[Select]

-<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?

-skwire (February 08, 2019, 05:30 AM)

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 [Select]

1. myTimestamp := "636850944"
2. sFormatString := "dddd, MMMM d, yyyy @ H:mm"
3. FormatTime, myFormattedTime, % timeUnix2Human( myTimestamp ), % sFormatString
4.  
5. MsgBox, % myFormattedTime
6.  
7. timeUnix2Human( unixTimestamp )
8. {
9.     returnDate = 19700101000000
10.     returnDate += unixTimestamp, s
11.     Return returnDate
12. }

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:

[Select]

-<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

[Select]

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.ne...timestamp_conversion

C# ticks to time span
---------------------
https://www.venea.ne...time_span_conversion

Pixel size
----------
https://www.venea.ne...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 [Select]

1. @echo off
2. :: Set the relative path to your xmlstarlet install:
3. set XML=..\Downloads\XML\xmlstarlet-1.6.1\xml.exe
4. :: Change filemask if desired
5. set "FILEMASK=NetTraffic 2*.xml"
6. :: Change result outputfile as desired, keep the .csv extension, when adding spaces, surround with quotes like FILEMASK
7. set OUTFILE=NetTrafficAll.csv
8. :: Add StatHour to this list if desired
9. set "ELEMENTS=StatYear StatMonth StatDay"
10. :: Choose csv separator, either , (comma) or ; (semicolon)
11. set CSVSEP=;
12.  
13. set XMLARG1=sel -T -t -m NetTraffic/
14. set XMLARG2=/key -v "concat('
15. set XMLARG3=%CSVSEP%',@ts,'%CSVSEP%',@tm,'%CSVSEP%',@dl,'%CSVSEP%',@ul,'%CSVSEP%',@dx,'%CSVSEP%',@dy)" -n
16.  
17. :: Write header
18. echo Period%CSVSEP%ts%CSVSEP%tm%CSVSEP%dl%CSVSEP%du%CSVSEP%dx%CSVSEP%dy>"%OUTFILE%"
19. :: Loop all files from FILEMASK
20. for %%F in ("%FILEMASK%") do (
21. echo File: %%F
22. :: Fetch all configured elements
23. for %%G in (%ELEMENTS%) do (
24. %XML% %XMLARG1%%%G%XMLARG2%%%G%XMLARG3% "%%F" >>"%OUTFILE%"
25. )
26. )

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.ne...web/pixel_calculator