Recently I posted a blog entry with a Sample Excel/VBA Application.  The idea behind this application was to provide any developer who is interested a baseline set of code for using the REST API for extracting data from Webtrends Analytics.  The latest version of that application is v4 and it supports the v1.1 REST URL API.  This new version is attached to this blog.  Please note that the attachment is a Microsoft Word document.  The application which is and Excel spreadsheet with VBA driving the action is included in that document as an embedded file.

 

I have decided to follow up that posting with more detail on how that application works.  This follow up work will come in a series of postings that sort of start at the beginning of the application and works its way through to the end.  The focus will be on the process of building the REST URLs and then parsing the resultant data from each call.

 

The sample application assumes that there are no REST URLs to begin with and that the user of the application will need to create them interactively.  This aspect of the application builds off the idea of the REST Generator application at http://generator.webtrends.com.  To make this work requires a series of steps and calls to Webtrends to fetch specific data.  I am assuming for purposes of this discussion that the reader has a fair knowledge of VBA with Excel and I will, therefore, not go into detail on every line of code in this application.  The code is documented on a line by line basis.

 

So, let’s start with the sequence in which the application gets its data and what is does with it.  To dynamically, interactively, generate a REST URL that requests report data we need to do the following:

 

1.       Get a list of the profiles available to us.

2.       Get a list of the reports that exist in the desired profile and the time periods that might exist for the reports.

3.       Get the report data.

 

There’s a bit more detail involved in these and I will cover that as I cover each of the mail points in the list above.  This posting will cover just the process involved in requesting a list of profiles.

 

I will warn you now that I am a brute force programmer and you may have better ideas than I do about specific functions and how they work.  That’s good and I welcome any feedback you have.

 

Let’s begin by looking at the general format for a valid REST URL.  For more detail on this I refer you to the Webtrends developer site at http://product.webtrends.com/dxapi/api_description.html.  The general REST URL has the following format:

 

https://ws.webtrends.com/v1.3.1/ReportService.svc/profiles/faj38jaFH/reports/J893kIL34/?period=2009&format=xml

where,

https: = protocol

ws.webtrends.com = site where data request is sent

v1.3.1 = API version number

ReportService.svc = web service called

Faj38jaFH = profile id

J389kIL34 = report id

period=2009 = report period

format=xml = result data format

 

 

The actual format of the REST URL you will use will depend heavily on what the request is targeted to return.  For a list of profiles the REST URL will look like the following:

https://ws.webtrends.com/v1_1/ReportService.svc/profiles/?format=xml

or,

https://ws.webtrends.com/v1_1/ReportService.svc/profiles/?format=json

 

The difference here is the format in which the result information is returned.  In the sample application I have used JSON.  So, this is the format I will reference in this post.

 

Using VBA to request web data in this form is a simple process.  In fact, in the sample application I have included a standard function to make this retrieval, no matter what data is being requested.  This function looks like this:

 

Private Function RunREST(url As String, ByRef username As String, ByRef password As String)

 

    ' Function will execute a REST url along with the UserName and Password passed as parameters

    Application.Cursor = xlWait                                  ' Set the Wait cursor while executing the REST Call

   

    ' Set up the object needed for the web service request

    Set g_objHttp = CreateObject("MSXML2.XMLHTTP")

   

    ' Open our connection and pass the user credentials

    g_objHttp.Open "GET", url, False, username, password          ' False indicates the call is synchronous - wait for URL Result

    g_objHttp.send                                                ' Send the request

    g_strResult = g_objHttp.responseText                          ' Get the URL Result

   

    ' Check to see if our credentials passed - display error when they don't

    If g_strResult = "Must authenticate" Then

        Application.Cursor = xlNorthwestArrow                     ' Reset the application cursor to normal (arrow)

        MsgBox "Authentication Failed", vbCritical, "Authentication Error"

        username = ""                                             ' Reset User Name

        password = ""                                             ' Reset Password

        RunREST = False                                           ' Set return value for failure

        Exit Function                                             ' Exit - we're done here

    End If

   

    RunREST = True                                                ' Set return value for success

   

End Function

 

 

The key statements in this function really are those that do the actual call of the REST URL.  They are:

 

 

g_objHttp.Open "GET", url, False, username, password

g_objHttp.send

g_strResult = g_objHttp.responseText

 

 

The g_objHttp.Open statement creates an open connection for the HTTP request.  Asscociated with that statement we pass the type of call, “GET”, the actual URL to be used with the call, and the user name and password.  The parameter shown as “False” indicates how this call is to be handled by the application.  The value “False” indicates that it is a synchronous call, meaning that the application will wait for the response before continuing.  Passing the user name and password prevents the user from being challenged when the call is made.  The g_objHttp.send statement does just what is implied – it sends the URL request via the web.  The last statement – g_strResult = g_objHttp.responseText – assigns whatever the result of the request is to our internal variable for further analysis.

 

 

The remaining statements in this function simply check to determine whether or not the authentication credentials had been accepted or rejected.  If either the user name or password were not valid, the result returned will simply state “Must authenticate”. That’s what this code checks for and displays an appropriate error and returns ‘False” as the function value.

 

Now that we have a result set it is time to do something with the data.  Remember that the data is returned in JSON format.  The JSON format for a list of profiles looks something like this:

 

 

[{"ID":"YcL4a5dufF6","name":"...sample: Zedesco 2009","AccountID":18079},

{"ID":"CxoSsHxKlF6","name":"..Sample: ExactTarget 2009","AccountID":18079},

{"ID":"1ZnDMawXD16","name":".sample: Z-2005","AccountID":18079},

{"ID":"1To2KI7wD16","name":"Sample: ET-2005","AccountID":18079}]

 

 

The paragraph formatting has been added to make this more readable.  The actual result is returned as one log string.  The entire string is bounded by square brackets – [].  Each individual set of profile data is bounded by curly brackets – {}.  Eand each of these is separated by a single comma.  Each set of profile data has three name/value pairs.  The names are: “ID”, “name”, and “AccountID”.  Not that these are all surrounded by double quotes and also separated by commas.  So parsing this data out should not be any serious challenge.  Using my brute force coding style I accomplish this with this set of statements:

 

 

' Display the JSON URL Result

    ' We need to parse all of the results to get what we want

    frmProfileList.lstProfiles.Clear                           ' Clear form we will display list on

    g_intProfileCnt = 0                                        ' Initialize the profile count to 0

    g_strResulta = Mid(g_strResult, 2, Len(g_strResult) - 2)   ' Strip the [] characters from each end

    g_blnMore = True

    Do While g_blnMore

        g_intSplit = InStr(1, g_strResulta, "},")                 ' See if there is more than one entry

        If g_intSplit = 0 Then

            g_strResultb = Mid(g_strResulta, 2, Len(g_strResulta) - 2)  ' Only one entry - grab it

        Else

            g_strResultb = Mid(g_strResulta, 2, g_intSplit - 2)   ' Grab the first set of data

        End If

        g_strResultb = Replace(g_strResultb, """", "")            ' Get rid of the extra " characters

        g_strArr = Split(g_strResultb, ",")                       ' Create an array with this set of data

       ReDim Preserve g_Profiles(g_intProfileCnt)

        g_Profiles(g_intProfileCnt).id = Mid(g_strArr(0), 4)        ' Save the Profile ID

        g_Profiles(g_intProfileCnt).name = Mid(g_strArr(1), 6)      ' Save the Profile Name

        g_Profiles(g_intProfileCnt).accountid = Mid(g_strArr(2), 11)    ' Save the Profile Account ID

       

       

        ' Check to see if we are done parsing

        If g_intSplit = 0 Then

            g_blnMore = False                               ' We're done

        End If

        g_strResulta = Right(g_strResulta, Len(g_strResulta) - g_intSplit - 1)   ' Set up for next profile

        g_intProfileCnt = g_intProfileCnt + 1               ' Bump the profile count

    Loop

   

    ' Sort the resulting array into sequence

    SortProfiles

   

    ' Now load the form with the Profile Names

    For i = LBound(g_Profiles) To UBound(g_Profiles)

        frmProfileList.lstProfiles.AddItem g_Profiles(i).name              ' Add name for selection from form

    Next

 

 

If you look for this code in the application, you will find it in the “GetProfiles” subroutine.  One of the methods I used to help store the profile data was to set up a specific data type for it.  That data type, found in the Data_Module module looks like this:

 

 

Public Type PROFILEDATA

    name As String

    id As String

    accountid As String

End Type

Public g_Profiles() As PROFILEDATA

 

 

Creating this data type makes it easy to keep all of the profile data for one profile together and then be able to sort the data into “name” sequence.  When we present the profile data to the end user we use the Profile Name.  When we use the profile data to later request a list of reports we will need the corresponding Profile ID.

 

The next blog post will cover creating a REST URL to retrieve a List of Reports and what do with that result.

 

Michael Love

Senior Solutions Engineer

Filter Blog

By date:
By tag: