topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • May 20, 2019, 02:24 PM
  • Proudly celebrating 13 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: NetTraffic xml export to Excel  (Read 1331 times)

itge13

  • Participant
  • Joined in 2019
  • *
  • default avatar
  • Posts: 9
    • View Profile
    • Donate to Member
NetTraffic xml export to Excel
« on: February 08, 2019, 04:28 AM »
the statistics in the program itself look like this
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:
-<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.
« Last Edit: February 08, 2019, 09:33 AM by itge13 »

skwire

  • Global Moderator
  • Joined in 2005
  • *****
  • Posts: 5,148
    • View Profile
    • Donate to Member
Re: NetTraffix xml export to Excel
« Reply #1 on: February 08, 2019, 05:30 AM »
What is NetTraffix?

itge13

  • Participant
  • Joined in 2019
  • *
  • default avatar
  • Posts: 9
    • View Profile
    • Donate to Member
Re: NetTraffix xml export to Excel
« Reply #2 on: February 08, 2019, 09:33 AM »
What is NetTraffix?
my bad. NetTraffic https://www.venea.net/web/nettraffic

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,347
    • View Profile
    • Donate to Member
Re: NetTraffic xml export to Excel
« Reply #3 on: February 08, 2019, 10:26 AM »
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

  • Participant
  • Joined in 2019
  • *
  • default avatar
  • Posts: 9
    • View Profile
    • Donate to Member
Re: NetTraffic xml export to Excel
« Reply #4 on: February 08, 2019, 11:13 AM »
@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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,347
    • View Profile
    • Donate to Member
Re: NetTraffic xml export to Excel
« Reply #5 on: February 09, 2019, 05:02 AM »
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

  • Global Moderator
  • Joined in 2005
  • *****
  • Posts: 5,148
    • View Profile
    • Donate to Member
Re: NetTraffic xml export to Excel
« Reply #6 on: February 09, 2019, 04:36 PM »
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

  • Member
  • Joined in 2006
  • **
  • Posts: 2,526
    • View Profile
    • Donate to Member
Re: NetTraffic xml export to Excel
« Reply #7 on: February 10, 2019, 01:15 AM »
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

  • Participant
  • Joined in 2019
  • *
  • default avatar
  • Posts: 9
    • View Profile
    • Donate to Member
Re: NetTraffic xml export to Excel
« Reply #8 on: February 10, 2019, 09:36 AM »
@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:
-<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
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

  • Participant
  • Joined in 2019
  • *
  • default avatar
  • Posts: 9
    • View Profile
    • Donate to Member
Re: NetTraffic xml export to Excel
« Reply #9 on: February 13, 2019, 12:08 PM »
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
« Last Edit: February 14, 2019, 04:07 AM by itge13 »

itge13

  • Participant
  • Joined in 2019
  • *
  • default avatar
  • Posts: 9
    • View Profile
    • Donate to Member
Re: NetTraffic xml export to Excel
« Reply #10 on: February 13, 2019, 01:34 PM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,347
    • View Profile
    • Donate to Member
Re: NetTraffic xml export to Excel
« Reply #11 on: February 13, 2019, 01:44 PM »
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 8))
« Last Edit: February 13, 2019, 03:06 PM by Ath, Reason: Cancelled request »

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,347
    • View Profile
    • Donate to Member
Re: NetTraffic xml export to Excel
« Reply #12 on: February 13, 2019, 03:17 PM »
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

  • Participant
  • Joined in 2019
  • *
  • default avatar
  • Posts: 9
    • View Profile
    • Donate to Member
Re: NetTraffic xml export to Excel
« Reply #13 on: February 14, 2019, 04:45 AM »
@ath thanks a lot for your work. Like a charm :Thmbsup:

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