Making a Test Log

WebTrends On-Premises users can test their customizations using a test log. Here’s one approach to making one.

Applies to:  On-Premises (Software) version of WebTrends

Every On-Premises user should know how to make a test log.  It’s essential if you want to

  • be absolutely sure your customizations are right
  • just want a bunch of data to analyze, for your own WebTrends practicing

It usually takes only a few minutes to make a test log and feed it into a test WebTrends report profile.  In this post I describe one way to do it.

There are a lot of steps below, but if you understand how WebTrends and log files work together in a general way, you shouldn’t have any trouble and you can probably skim most of it.

Steps 6 and 7 in the second section are the value-adds of this post, the details that most people don’t think about.

You’ll need:

  • Access to existing logs (ones you already feed to WebTrends)
  • Admin access to WebTrends (the ability to create a new profile, Data Source, custom report, etc)
  • A plain text editor, preferably one that will handle large files, for example TextPad.  NotePad will work okay if your logs aren’t very big.
  • Knowledge of what you want to test, i.e. what parameters, URLs, whatever.
    • If you already know what you want in the log, you can build a short hypothetical visit in a fresh log by making copies of just one log file line, then editing the lines so they work as one visit.
    • Or go to your site and click around, making your own test visit that you can isolate into a test log and analyze by itself.  Often, just a few lines will be all you need to test.
    • If you mainly want bulk data as a WebTrends sandbox, do a lot of clicking.  Change browsers and erase cookies as you go in order to make yourself look like more than one visitor.  Use this as a core and copy, paste, jumble up to make the file bulkier.  You’ll straighten it out in Step 5.
    • For bulk data, you can also install a crawler and tell it to crawl your site, then use the resulting log.   HTTP Track is one crawler; see Wikipedia for others.  (contribute a little $$ to Wikipedia while you’re at it!)
    • Also using a crawler, you can feed it a list of the important URLs, then use the resulting log.

Steps to create a test log:

  1. Find out where the current logs are located.  A basic way to find out is to edit a profile, identify the profile’s Data Source, then edit that Data Source  to see the file location info.
  2. With the text editor, create a new empty file.
  3. With the text editor, open one of the existing log files.  Open the one that contains your test visit or your crawler’s visit, depending on your basic approach.  If you want to build a hypothetical visit, you can copy just one line from any log.
  4. Copy one or more data lines and paste it/them into a new blank file.
    1. Short hypothetical visit – Open any log file.  Copy one existing line and paste it several times into the new log, then edit the lines so they contain whatever URLs (etc) you are trying to check.  With this method, all the lines will have the same cookie and therefore they will all look like the same visitor, same visit.  Your resulting WebTrends report will be small, but it will be focused on exactly what you wanted to test.
    2. Test visit – Locate the lines from your test visit, if you did one, and copy  and paste them into a new file.  (After analysis, you’ll check to see if WebTrends shows exactly what you did in your test visit, no more and no less.)
    3. Bulk data – Grab the log containing your many-click visit or your crawler activity.  Copy and paste all the data lines.  To get more bulk, paste over and over again.  You probably should change the cookie values here and there to show a bigger number of unique visitors.
  5. Change the time/date stamps on all the data lines in your file so they are strictly consecutive, although the time spacing between them doesn’t matter.  Check to make sure not a single line is out of order!  (I usually do this editing manually.) (Oh, and it goes without saying that none of your dates/time should be in the future.)
  6. Copy and paste the #Fields line from an existing log into the first position of the new log.
    #Fields: date time c-ip cs-username cs-host cs-method cs-uri-stem cs-uri-query sc-status sc-bytes cs-version cs(User-Agent) cs(Cookie) cs(Referer) dcs-id
  7. Copy the very last line of your new log and paste it into the very last position (you’ll have a pair of identical lines at first) then change its date to be 1 day later.WebTrends will not analyze this line but it MUST be there, with a time stamp at least a half hour later than everything else in the log.  I use a whole day later to be easier to see.
  8. Save this new file.
    • The name of the file and the extension don’t matter to WebTrends; you’ll be telling the Data Source the exact path and name.
    • Save it in same place as the other logs or create a new folder, it doesn’t matter.  The important thing is that WebTrends can reach the file as well as not mistake it for a regular log and process it into your regular reports!

Steps to analyze the log:

  1. Go into the WebTrends admin console
  2. Go to Administration >> Data Sources and find the data source that corresponds to the one you’ve been messing with.  Create and edit a copy of it.
  3. Edit the file location information to point to the file you just created.
  4. Save and close the new Data source.
  5. Go to the list of profiles and clone one or create a new one.  Name it appropriately.
  6. Edit the new profile’s Data Source to be the new Data Source you just created.
  7. Turn off all hit and visit filters (such as spider filters) especially if you used a crawler.
  8. Save the profile, analyze it, and, most importantly, see if the result is what you expected.

Automate your reports with the ODBC driver

The ODBC driver is probably my favorite WebTrends feature.  I still haven’t bothered to use REST because ODBC does everything I want.

With the ODBC driver I can create a 1 click report in Excel that I can distribute to anyone in my organization. They don’t have to log in, they don’t have to figure out which profile has the report they want, they just need to click on the link and open a nice familiar Excel file that tells them exactly what they need to know.

The easiest way to use the ODBC driver is to use a program called Datalinks. But, if you’re like me and you don’t have a few thousand extra dollars lying around you can do much of what you need to do directly from Excel for free. It just takes a bit of hacking around in Excel. Here is what you need to do (this works in Excel 2003, it is probably slightly different in other versions) :

  1. Install the ODBC driver. You can get to it from the “install components” link in the left nav of the admin area. If you don’t see it you may not have rights to use it. Ask your administrator to set you up.
  2. In Excel go to Data –> External Data –> New Database Query. It will open up a data connection box. Choose new connection and OK. Then find the WebTrends ODBC driver on the list and select it.
  3. Next you’ll have to fill out your connection details. If you’re using On Demand they should be filled in, if you’re using your own software then you can fill in the default login URL and your username and password to connect. Click “refresh” and choose your profile and template from the dropdowns. Click OK to save.
  4. Now Excel will present you with a list of the available reports. The names will approximate the names you are familiar with from the normal interface, but they’ll be a bit different. Choose the report you want and click next. (NOTE: you can only pick ONE report. Sadly, the ODBC connection doesn’t do Joins.)
  5. Continue through the wizard. You can filter and sort on pretty much whatever you want.
  6. When you finish the wizard Excel will crunch for a while (the ODBC connection is not super speedy) and then it will ask you where you want to place the data in your spreadsheet. Choose a cell and the data will automagically appear. TaDa!
  7. If you click in the data area and then click on the exclamation point in the External Data toolbar (or click on Data –> Refresh Data) Excel will reach back out to WebTrends and pull down fresh data. Neato!

So now the bad news. This is cool and all, but WebTrends has practically snatched defeat from the jaws of victory. The fatal flaw is that the ODBC driver doesn’t recognize “parameters” as used by Excel. Excel Parameters allow you to use values in a cell on the spreadsheet in the query itself. This really comes in handy when you’re trying to change dates. Luckily, there is a workaround of sorts in the form of Relative Date Macros. Relative date macros allow you to query for data for “yesterday”, or “last week” or “last month”. Here is how to use them in Excel:

  1. Perform the steps 1-5 above. On the last screen of the wizard choose “edit this query in Microsoft Query”. If you’ve already retrieved the data go to Data –> Import External Data –> Edit Query.
  2. You’ll get a Microsoft Query window. The easiest thing to do is press the “SQL” button in the toolbar and type in a “WHERE” clause after the existing SQL query. Here is an example query with a where clause that uses a relative date macro:

    SELECT Browsers_0.Browser, Browsers_0.Hits, Browsers_0.Visits, Browsers_0.TimePeriod, Browsers_0.StartDate, Browsers_0.EndDate FROM Browsers Browsers_0
    WHERE (Browsers_0.TimePeriod='%start=day-5;duration=day+1%')

    This query gets the browsers report for a single day 5 days ago. To see all the relative date macros search for “relative date macro” in WT help, it’s in the first link.
  3. After you modify the SQL and close the little SQL window, close the Microsoft Query window as well. This will bring the data back into Excel. Format and you’re done!
This opens up a lot of possibilities. For example, you can actually create a bounce rate report (imagine that!) by pulling down the entry pages report in one tab and the single page visits report in another and using VLOOKUP in Excel to match up the URL fields. By pulling down reports into a bunch of different tabs you can also create nice, easy to update and distribute Excel dashboards for all your reporting needs.