0 Replies Latest reply: Mar 4, 2012 10:18 AM by Michael Powe RSS

Retrieve Report Data via PowerShell

Michael Powe Newbie



In response to the need to pull data from the API and finding that Excel is still one of the worst-performing tools in the known universe, I created the following simple PS function to retrieve data. 


It returns an object with two properties:  Count, which is the content length or length of the data string returned; and Data, which holds the string.


Export the retrieved data to a file:


$data = Get-RestData -start 2011m01d01 -end 2011m01d31 -conf $restConfigHash

$data.Data | Set-Content .\data.html 



As observable in the example, create a hash with the values to be used to retrieve the data.  Use Get-Help Get-RestData -full to see all the configuration information to put into the hash.  This function is designed only for returning report data and not any other of the kinds of data available through the API.


function Get-RestData{

  Retrieve report data from WebTrends via REST.
  A simple retrieval mechanism for REST data from WebTrends API.  Pass in 
  the start and end dates in proper format.   Use a hashtable for the other 
  essential values.  This function builds the data request for report data 
  The data is collected in an object, which is returned from the function.
  The object contains a property 'Count', which can be used to see the size
  of the returned data string.  The property 'Data' contains the full string
  returned in response to the request.
 .Parameter Start
  The start date in proper format, e.g. 2011m01d01.
 .Parameter End
  The end date in proper format.
 .Parameter Conf
  A hashtable containing configuration information for the data pull.  The 
  hashtable should contain the following elements:
  version,profile ID, report GUID, type (agg,indv or trend), totals 
  (all, none or only), format (html, json, xml or xml2),  measures 
  (measures=0*1*3 or measures=99 for all measures), username (acct\login), 
  $restConfig = @{version="v3";
  An object that contains the data retrieved and a count indicating the length of the data.
  This function uses the StreamReader method ReadToEnd(), so a huge data response may break.
  The usual breakage is that the server closes the stream.  I assume that this happens 
  because the server has a built-in limit to the amount of data it will return in a single
  request. The solution is to chunk up the requests and then reassemble the chunks after
  they've all been retrieved.
  Some trivial exception handling is included, which may prevent falling into an infinite
  loop of errors if something goes wrong.

     param (
     [parameter(Mandatory = $true)] [string]$Start,
     [parameter(Mandatory = $true)] [string]$End,
     [parameter(Mandatory = $true)] [hashtable]$Conf)
     $version = $Conf.version
     $profile = $Conf.profile
     $report = $Conf.report
     $type = $Conf.type
     $totals = $Conf.totals
     $format = $Conf.format
     # put in a nonsense number to include all measures
     $measures = $Conf.measures
     $URL = "https://ws.webtrends.com/$version/Reporting/profiles/$profile/reports/$report/?totals=$totals&start_period=$start&end_period=$end&period_type=$type&$measures&format=$format&suppress_error_codes=true"

     $Username = $Conf.user
     $Password = $Conf.pass
     $UserAgent = $env:USERNAME+":"+$env:COMPUTERNAME

     $restData = "" | Select-Object Count,Data

     $URI = New-Object System.Uri($URL,$true)
     $request = [System.Net.HttpWebRequest]::Create($URI)

     $request.UserAgent = $(
     "{0} (PowerShell {1}; .NET CLR {2}; {3})" -f $UserAgent, 
     [Environment]::OSVersion.ToString().Replace("Microsoft Windows ", "Win"))

     #Establish the credentials for the request

     $creds = New-Object System.Net.NetworkCredential($Username,$Password)

     $request.Credentials = $creds
     # Thrown if the request times out
     trap [System.Net.WebException] { "A web exception was thrown, possibly caused by a timeout: $_"; break; }

     $response = $request.GetResponse()
     $reader = [IO.StreamReader] $response.GetResponseStream()
     # thrown by ReadToEnd()
     trap [System.IO.IOException] { "An IO exception occurred on StreamReader: $_"; break }
     trap [System.OutOfMemoryException] { "An OutOfMemoryException occurred on StreamReader: $_"; break }
     $responseHTML = $reader.ReadToEnd()
     $restData.Data = $responseHTML
     $restData.Count = ($restData.Data).Length
     if ($reader -ne $null){
     if ($response -ne $null){
     return $restData