The goal is to transform RealNetworks' streaming server text-based log files into Microsoft Excel files with well defined columns. The resulting Excel files must then be used to produce audio/video file usage reports using the Pivot Table Chart feature.
The required Excel Macro must receive as input a variable number of individual log files, and produce one or more consolidated Excel files. Consolidation must be performed on the basis of the "mountpoint" field found embedded along the string of characters of the log data.
The description of RealNetworks's log file can be found in: [url removed, login to view]
Sample log files are provided.
A sample Excel Pivot Table is provided where all mountpoints reside in the same worksheet. The desired output is one Table per "customer". A customer is defined by each "mountpoint" except for the system-defined mountpoints:
broadcast/gartner/[url removed, login to view]
filename= gartner/[url removed, login to view]
gartner/[url removed, login to view]
customer= mountpoint = gartner
filename= [url removed, login to view]
Only when the mountpoint value is system-defined ("broadcast", "secure" or "encoder") the expected output is the following:
* If the "filename" includes "esmas" (by far the largest customer) then a separate Excel file must be created. This workbook will be comprised of as many worksheets as days of the month are found in the input log files, one per day.
* If the filename does not include "esmas", a separate Excel file must be created. This workbook will be comprised of as many worksheets as customers are found, one worksheet per customer.
When the mountpoint represents a customer, a separate Excel file is expected per customer. The entire month's log data should fit in one worksheet.
In all cases, worksheet size can be reduced by discarding any log entry where either :
"bytes" = 0
"time sent" = 0
mountpoint = "admin"
There are two calculated fields that must be included in all Pivot Tables:
kbps = bytes * 8 / 1000 / 'Time Sent'
mins = 'Time Sent' / 60
1) Complete and fully-functional working Macro in Excel format as well as complete source code of all work done.
2) Installation package that will install the software (in ready-to-run condition) on the platform(s) specified in this bid request.
3) Complete ownership and distribution copyrights to all work purchased.
4) Pivot Table Charts including the following fields: