Workbook/spreadsheets

[boofus]

to Spice300:

Looks like you've don a TON of work analyzing FAPZILLA output with Excel. How 'bout making some of your workbooks/spreadsheets available here?

thanks

Boo

[grover92000]

Boo, a few of us have done a lot of analyzing of our FAP data. TalonDancer did a lot early on too but he is no longer with WB. Search for some of his posts, I'm not sure if he posted any of his Excel spreadsheets. One thing with the the way WB implements the FAP is it ISN'T a 30 day running total. It's more of a day of month total. IE what you used on the 1st of last month comes off on the first of this month. This is why there is no change in your FAP numbers on the 30th/31st of the month.

[boofus]

Originally Posted by grover92000

One thing with the the way WB implements the FAP is it ISN'T a 30 day running total. It's more of a day of month total. IE what you used on the 1st of last month comes off on the first of this month. This is why there is no change in your FAP numbers on the 30th/31st of the month.

Thos last few sentences right there do more to clarify things than anything I've seen. I know MANY of you folks, worked hard to figure it all out. I typically pay little attentionn to my usage, but have gotten more interested in it lately.

I've looked for existing sheets (searched for workbook and spreadsheet) but those don't turn up many results. I don't think TalonDancer attached his; just results.

Boy, would LOVE to get your fapzilla running IN my WG54GL!!!! Now that would be the cat's "whiskers" 8-) but without a memory mod, would be next to impossible. Actually, if I could find a cross-compiler for perl or C++ or something like that I would just build an executable for the 54GL and skip the perl runtime.

I'm going to look at BWLog; it's free and runs in the router. Just don't know if it can access the WB stats over the 'net; maybe just the stuff kept locally in the router.

Regards

Boo

[grover92000]

Originally Posted by boofus

I know MANY of you folks, worked hard to figure it all out.

Actually, there were very few of us that tracked our usage down to that level.

Originally Posted by boofus

Boy, would LOVE to get your fapzilla running IN my WG54GL!!!! Now that would be the cat's "whiskers" 8-) but without a memory mod, would be next to impossible. Actually, if I could find a cross-compiler for perl or C++ or something like that I would just build an executable for the 54GL and skip the perl runtime.

No need to cross-compile. Here's a simple script that will run in dd-wrt (or on any *nix system) that will pull your numbers. It just uses wget and some awk to get the values. Change the email address for your account.

#!/bin/sh

TEMPFILE=/tmp/fapzilla.$$
EMAIL="user@wildblue.net"

wget -O $TEMPFILE http://www.attwb.net/content/fap-sso/proxy_username.asp\?user_name=${EMAIL} > /dev/null 2>&1

# If wget fails then we need to bail.
if [ $? != 0 ]; then
	echo "ERROR getting FAP data."
	rm $TEMPFILE
	exit 1
fi

# If the size of the file wget retrieved is 0 then it was a bad request.
if [ `ls -l $TEMPFILE | awk '{ print $5 }'` == "0" ]; then
	echo "ERROR getting FAP data.  User is possibly unknown."
	rm $TEMPFILE
	exit 1
fi

UPLOAD=`cat $TEMPFILE | awk -F\< '{ print $3 }' | awk '{ print $5 }' | awk -F\' '{ print $2 }'`
DOWNLOAD=`cat $TEMPFILE | awk -F\< '{ print $5 }' | awk '{ print $5 }' | awk -F\' '{ print $2 }'`

echo $UPLOAD:$DOWNLOAD
rm $TEMPFILE

Originally Posted by boofus

I'm going to look at BWLog; it's free and runs in the router. Just don't know if it can access the WB stats over the 'net; maybe just the stuff kept locally in the router.

I haven't tried BWLog but I think it just looks at the routers local stats. If you want to monitor over a long period of time I would recommend using something like MRTG running on another PC pulling stats from the router.

[boofus]

Originally Posted by grover92000

Actually, there were very few of us that tracked our usage down to that level.

Well, whatever the number a big thanks to all.

Originally Posted by grover92000

No need to cross-compile. Here's a simple script that will run in dd-wrt (or on any *nix system) that will pull your numbers. It just uses wget and some awk to get the values. Change the email address for your account.

I figured out last night that WGET would do the job to get WB's numbers from the 'net. Thanks a lot for the AWK constructs though. It keeps me from having to dig out my very old (and unused) UNIX docs from 20 years ago. Once again..... You DA Man!!!!!

Originally Posted by grover92000

I haven't tried BWLog but I think it just looks at the routers local stats. If you want to monitor over a long period of time I would recommend using something like MRTG running on another PC pulling stats from the router.

You're right bwlog gets local stats only, but it's all done in shell scripts, so I can add the code to pull the WB figures easily. Obviously your code snippet will be quite helpful.

Then everything can run in the router directly. wblog even has a mini-HTML server (listening on port 8000) built in so I should be able to configure the output just about any way I want. (CSS sheets, etc) The author currently has quite a few cgi scripts in there too so you can look at your data daily, weekly, monthly, etc. All you need it to put 192.168.1.1:8000 (Your router IP plus port 8000) into any browser and results come up on your screen.

Thanks again, Grover

Boo


UPDATE: Now have modified version of BWLOG running IN my router. It connects to the WB fapometer once an hour to get their figures. Of course, BWLOG also records the internal router counters for comparison.

In addition to the mini http server mentioned above, there is also included with WBLOG a small footprint daemon called haserl that allow embedded *nix shell scripts to be used as CGI scripts (normally perl, PHP or Python, etc of course).

Again, thanks for your help/advice, Grover.

[Spice300]

I am not sure what you want. Four of us studied the FAP extensively. Grover and I are the only ones still posting. We all used different methods to collect, process and analyze our data.

Because I do not use a router, I wrote a DOS batch file to execute Fapzilla and capture the actual usage as reported by netstat -e. This data goes into a text file which ends up like this whenever my computer & WB modem are running:

date,time,upload (MB),download (MB),netstat received (bytes),netstat sent (bytes)
2/14/2009,15:32:04,171,6843,45083639,3046198,comments
2/14/2009,16:03:22,171,6844,45377773,3092079
2/14/2009,16:32:04,171,6843,46134016,3231317
2/14/2009,17:02:02,171,6844,46781673,3402570
2/14/2009,17:32:04,171,6848,112116674,5180774
2/14/2009,18:02:03,172,6960,215756411,7150545
2/14/2009,18:32:05,172,7027,250742233,7979681
2/14/2009,19:02:03,172,7031,253257455,8400172

If I plan to plot the data, I copy and paste this data into Microsoft Works Spreadsheet v8.0. I convert the date and time into decimal days. Since I programed Task Scheduler to execute every 30 minutes, I space the data in the spreadsheet so that 1 row represents 30 minutes requiring me to manually insert blank rows when there are gaps in my data. This process is tedious and must be done with care to prevent errors.

Another column receives the results of the FAP download usage translating errors into blank cells allowing the graph to have gaps rather than errors where data is missing.

Because the netstat received data is a running total of the number of bytes received since my computer was last booted, I calculate the difference with the previous reading. Thus I put the number of megabutes (1,048,576 bytes) downloaded during the last 30 minute interval into the column labeled "received during interval (MB)" (see the attached image of a portion of my spreadsheet). I then calculate the sum of all the differences for the current FAPperiod in the column "netstat received rolling monthly (MB)." I also adjust the final row for the summation based on the delay which is usually 30 minutes to a few hours, (that is, 1 to 4 rows in my spreadsheet).

FAPperiod is the interval of time into the past when old usage is subtracted. It is based on the number of days in the prior month.

If prior month has 28 days, then FAPperiod = ~27.8 days.
If prior month has 29 days, then FAPperiod = ~28.8 days.
If prior month has 30 days, then FAPperiod = ~29.8 days.
If prior month has 31 days, then FAPperiod = ~30.8 days.

I adjust the summation interval in the table to match the appropriate FAPperiod. I instruct the spreadsheet to plot the data in the three columns on the right and compare the two curves. I adjust the parameters to produce the best match between the download FAP usage and the netstat received usage. I also have to adjust the parameters for the flatlines and sometimes other quirks. Wildblue's FAP meter is not a perfect measuring instrument.

The equation for the top data cell in the far right column, K1453, is: =sum(H23:H1452)-3063.2

In this case delay is 30 minutes and FAPperiod = 1430 rows = 29.79 days. The constant "3063.2" is the summed data that I download during the previous flatline in November 2007. It must be subtracted to eliminate the offset caused by the FAP meter not recording anything at the end of the month while netstat continued recording.

This is far from an automatic procedure and could be improved upon in several ways. TallonDancer suggested I use a search and extract function saving me the effort of spacing the data into 30 minute rows. However, the graphing function in Works Spreadsheet will not plot correctly if I do not space the data uniformly.

[boofus]

Originally Posted by Spice300

I am not sure what you want.

Sorry if I wasn't clear enough...... Was looking for any Excel workbook/spreadsheets that I can use as a TEMPLATE for my own collected data.

I can certainly create my own analysys formulas for Excel, but don't want to re-invent the wheel if I can borrow from someone that has alread done the major legwork.


Hope this explains it.

Thanks


Boo

[Spice300]

Sorry, I do not have any Excel template files. TalonDancer was the other poster who analyzed in depth, and I think he used Microsoft Office Excel. His spreadsheet was more automated than mine. He described how he implement his spreadsheet somewhere on this site, but I do not remember him posting a template.