Webclient Caching with PowerShell

In Advanced Event 7 of the 2011 Microsoft Scripting Games, the goal was to map names to Twitter screen names. The information was to be gathered from the SQLSaturday site, but I wanted to add the functionality to query the actual Twitter API for more information.

Much of the Twitter API functionality requires authentication through oAuth, but I didn’t want to add that level of complexity. Fortunately, there are some useful things that can be done without authentication. The users/show API accepts a Twitter screen name and returns a lot of information about that user in an xml or json format. This is what I wanted to use to add some functionality to my PowerShell script.

While this particular API can be accessed anonymously, it is also subject to more restrictive rate limiting. This means I was only going to be able to make 150 requests per hour. This can be a real bummer when trying to test a script. Since I was retrieving the same information over and over, it seemed like a good idea to cache the data locally.

First, I generated a path to the CSV file to be used for the local cache. I wanted to use a specific name for the file, so that it would be found on subsequent calls to the function.

$cachePath = [System.IO.Path]::GetTempPath() + 'Get-TwitterUserCache.csv'

With the path to the cache file determined, its existence is checked and the file is imported if found.

      if (Test-Path $cachePath)
      {
        try
        {
          $twitterCache = @(Import-Csv $cachePath)
        }
        catch
        {
          Write-Warning "Unable to import Twitter Cache."
          $twitterCache = @()
        }
      }
      else
      {
        $twitterCache = @()
      }

With the cache loaded into the twitterCache variable, I check to see if it contains the screen name I’m looking for.

$result = $twitterCache | Where-Object {$_.ScreenName -eq $screen_name}

If the screen name wasn’t found in the cache, the Twitter API is queried and the returned information is added to twitterCache.

  if ($result -eq $null)
  {
    Write-Verbose "Screen Name $screen_name was NOT found in cache"
    $query = "screen_name=$screen_name"
    
    try
    {
      Write-Verbose "Query String: $twitterUrl$query"
      [xml]$xml = $webClient.DownloadString("$twitterUrl$query")
      $result = New-Object PSObject -Property @{
        Name=$xml.user.name
        ScreenName=$xml.user.screen_name
        Location=$xml.user.location
        Description=$xml.user.description
        Url=$xml.user.url
        Id=$xml.user.id}
        
      $twitterCache += $result
    }
    catch
    {
      Write-Warning "There was a problem attempting to query Twitter."
      
      if ($Error[0].exception.message -like "*(400) Bad Request*")
      {
        Write-Warning "400 Bad Request response from Twitter."
        Write-Warning "This may indicate the maximum number of API queries has been reached."
      }
    }

The updated cache is then written back to the CSV file.

$twitterCache | Export-Csv -Path $cachePath -NoTypeInformation

I wrapped this functionality into the Invoke-TwitterQuery function, so that the rest of my script wouldn’t care if the data was cached or not. It just calls the function and doesn’t have to worry about how the information is being retrieved. It also means I could make adjustments to Invoke-TwitterQuery, like storing information in a database instead of a CSV file, and the rest of the script wouldn’t need to be changed.

All of my entries for the 2011 Scripting Games can be found at PoshCode.

Complete Script:

# -----------------------------------------------------------------------------
# Script: Get-TwitterUser
# Author: Jason Hofferle
# Date: 04/13/2011
# Version: 1.0.0
# Comments: The Get-TwitterUser function accesses the networking page for the 
# specified SQL Saturday event and returns a list of all twitter users that can 
# be exported to a CSV file. This CSV can then be used to resolve Names to 
# twitter screen names or vice versa. The function can also access the Twitter 
# API, allowing twitter to be queried for additional information on SQL Saturday
# attendees, or for retrieving information about any Twitter screen name. 
# Information returned from twitter is cached locally in a separate csv to 
# eliminate the need to repeatedly request the same information.
#  
# -----------------------------------------------------------------------------

Function Get-TwitterUser
{
  [CmdletBinding(DefaultParameterSetname='QuerySQLSat')]
  Param
  (
    [Parameter(
    ValueFromPipeLine=$true,
    Mandatory=$true,
    ParameterSetName='QuerySQLSat')]
    [int]
    $EventNumber,
    
    [Parameter()]
    [ValidateScript({Test-Path $_ -IsValid})]
    [string]
    $Path,
    
    [Parameter(
    Mandatory=$true,
    ParameterSetName='TranslateName')]
    [string]
    $Name,
    
    [Parameter(
    Mandatory=$true,
    ParameterSetName='TranslateScreenName')]
    [string]
    $ScreenName,
    
    [switch]
    $UseTwitterAPI
  )
    
  Begin
  {
    $webClient = New-Object System.Net.WebClient
    
    # #########################################################################
    # Function for querying Twitter API
    # http://dev.twitter.com/doc/get/users/show
    Function Invoke-TwitterQuery
    {
      Param
      (
        [string]$screen_name
      )
        
      # Twitter has a query limit based on IP Address, so a CSV is
      # setup to cache results locally. It is written to the Temp Path,
      # but given a standard name so it can be retrieved each time the
      # function is called.
      $cachePath = [System.IO.Path]::GetTempPath() + 'Get-TwitterUserCache.csv'
      Write-Verbose "Twitter cache: $cachePath"
        
      # Base URL for the twitter API being used.
      $twitterURL = 'http://api.twitter.com/1/users/show.xml?'
        
      # Import the cache file if it exists.
      if (Test-Path $cachePath)
      {
        try
        {
          $twitterCache = @(Import-Csv $cachePath)
        }
        catch
        {
          Write-Warning "Unable to import Twitter Cache."
          $twitterCache = @()
        }
      }
      else
      {
        $twitterCache = @()
      }
        
      # Check the cache for the screen name being searched for.
      $result = $twitterCache | Where-Object {$_.ScreenName -eq $screen_name}
        
      # Query twitter if the screen name was not found in the cache.
      if ($result -eq $null)
      {
        Write-Verbose "Screen Name $screen_name was NOT found in cache"
        $query = "screen_name=$screen_name"
        
        try
        {
          Write-Verbose "Query String: $twitterUrl$query"
          [xml]$xml = $webClient.DownloadString("$twitterUrl$query")
          $result = New-Object PSObject -Property @{
            Name=$xml.user.name
            ScreenName=$xml.user.screen_name
            Location=$xml.user.location
            Description=$xml.user.description
            Url=$xml.user.url
            Id=$xml.user.id}
            
          $twitterCache += $result
        }
        catch
        {
          Write-Warning "There was a problem attempting to query Twitter."
          
          if ($Error[0].exception.message -like "*(400) Bad Request*")
          {
            Write-Warning "400 Bad Request response from Twitter."
            Write-Warning "This may indicate the maximum number of API queries has been reached."
          }
        }
      }
      else
      {
        Write-Verbose "Screen Name $screen_name was found in local cache"
      }
        
      # Export the updated cache to a new CSV
      try
      {
        $twitterCache | Export-Csv -Path $cachePath -NoTypeInformation
      }
      catch
      {
        Write-Warning "Unable to export new cache to $cachePath"
      }
        
      Write-Output $result
    }
  }
    
  Process
  {
    # Run code specific to the specified paramters.
    Switch ($PsCmdlet.ParameterSetname)
    {
      # #######################################################################
      # Switch block for querying the SQL Saturday website.
      'QuerySQLSat'
      {
        $userCollection = @()
        $url = "http://www.sqlsaturday.com/$EventNumber/networking.aspx"
        $pattern = '<a href="http://www.twitter.com/'
        
        # Download the text for the Networking page, and split by
        # newline characters.
        try
        {
          Write-Verbose "Query string: $url"
          $result = $webClient.DownloadString($url).split("`n")
        }
        catch
        {
          Write-Warning "There was an error accessing the SQL Saturday site"
        }
        
        # Get an array of strings that contain the twitter URL pattern.
        [string[]]$matches = $result | Select-String -Pattern $pattern
        
        # Look at each string, extract the Name and ScreenName and
        # create a custom object with those properties.
        
        for ($i = 0; $i -lt $matches.Count; $i++)
        {
          Write-Progress -Activity "Query in Progress" `
                         -Status "$i of $($matches.Count)" `
                         -PercentComplete ($i / $matches.Count * 100)
          
          try
          {
            $start = ($matches[$i] -split $pattern)[0]
            $end = ($matches[$i] -split $pattern)[1]
            
            $name = $start.substring($start.lastindexof('>')+1)
            $screenName = $end.substring(0,$end.indexof('"'))
          }
          catch
          {
            Write-Warning "There was a problem parsing the HTML"
          }
            
          # Some of the twitter screen names are not correct on the
          # networking webpage, so we try to clean it up.
          try
          {
            $screenName = $screenName.substring($screenName.lastindexof('/')+1)
          }
          catch {}
          
          # If UseTwitterAPI parameter was specified, query twitter for
          # each screen name found on SQL Saturday page to get additional
          # information.
          if ($UseTwitterAPI)
          {
            $userInfo = Invoke-TwitterQuery $ScreenName
          }
          else
          { 
            $userInfo = New-Object PSObject -Property @{
              Name=$name
              ScreenName=$screenName
              Location=''
              Description=''
              Url=''
              Id=''}
          }
            
          # Collect our information into an array so it can be
          # exported to a CSV in the End block.  
          $userCollection += $userInfo
        }
      }
        
      # #######################################################################  
      # Switch block for resolving a Name from the CSV file to the
      # corresponding twitter screen name. The twitter API being used
      # does not allow searching by Name, so we are limited to querying
      # the CSV specified by the Path parameter.
      'TranslateName'
      {
        # If the CSV was specified and exists, import and search it.
        if ( ($Path) -and (Test-Path $Path) )
        {
          try
          {
            $csv = Import-Csv $Path
          }
          catch
          {
            Write-Warning "Unable to import CSV $Path"
          }
            
          Write-Output $csv | Where-Object {$_.Name -like "*$Name*"}
        }
        else
        {
          Write-Warning "Path was not specified or file could not be found."
        }
      }
      
      # #######################################################################  
      # Switch block for resolving a twitter screen name to a Name.
      # The twitter API being used allows us to query by screen name, which
      # gives us the option to resolve screen names that are not listed
      # on the SQL Saturday networking page.
      'TranslateScreenName'
      {                
        if ($UseTwitterAPI)
        {
          Write-Output (Invoke-TwitterQuery $ScreenName)
        }
        else
        {
          # If the UserTwitterAPI parameter was not specified, just
          # query the CSV specified in the Path parameter.
          if ($Path)
          {
            try
            {
              $csv = Import-Csv $Path
            }
            catch
            {
              Write-Warning "Unable to import CSV $Path"
            }
            
            Write-Output $csv | Where-Object {$_.ScreenName -like "*$ScreenName*"}
          }
          else
          {
            Write-Warning "Neither Path or UseTwitterAPI were not specified. No results will be returned."
          }
        }
      }
    }
  }
    
  End
  {
    # Run code specific to the parameter set.
    Switch ($PsCmdlet.ParameterSetname)
    {
      'QuerySQLSat'
      {        
        # Export results to a CSV if Path parameter was specified.
        if ($Path)
        {
          try
          {
            $userCollection | Export-Csv -Path $Path -ErrorAction STOP -NoTypeInformation
          }
          catch
          {
            "Error writing to $Path"
          }
        }
        
        # Write Screen Output.
        Write-Output $userCollection
      }
    }
  }
  
  <#
  
  .Synopsis
  Retrieves twitter users from the SQL Saturday website or from the twitter API.
  
  .Description
  The Get-TwitterUser function accesses the networking page for the 
  specified SQL Saturday event and returns a list of all twitter users with
  names, and can export that information to a CSV file for later reference.
  
  The function can also access the Twitter Users/Show API, allowing twitter
  to be queried for additional information on SQL Saturday attendees, or for
  retrieving additional information about any Twitter screen name.
       
  .parameter EventNumber
  Gets usernames for the specified Event Number.
  This parameter is mandatory if Name or ScreenName are not provided.
    
  .parameter Path
  Path to a CSV file used to export information to, or import data from.
  
  When used with EventNumber, the twitter information is exported to the 
  specified CSV file.
  
  When used with Name or ScreenName, the CSV file is loaded and searched for
  the name or screen name.
  
  .parameter Name
  Searches the CSV file specified in Path to resolve a User Name to a twitter
  username (screen name).
  
  .parameter ScreenName
  Searches the CSV file specified in Path to resolve a twitter username
  (screen name) to a User Name.
  
  When used with the UseTwitterAPI parameter, the CSV file is not queried in
  favor of directly querying twitter for the information associated with the
  screen name.
  
  .parameter UseTwitterAPI
  Uses the twitter Users/Show API when possible to resolve screen names or
  retrieve additional information.
  
  Due to API limits on the number of number of queries per IP Address, any
  results returned are cached in a CSV file created in the temp directory. The
  name and location of this file is displayed with the Verbose parameter.
  
  If the information is not found in the cache, twitter is queried and the
  cache is updated with the new information. The file can be manually deleted
  to reset the cache.
  
  When used with EventNumber, twitter names that cannot be resolved with the
  API will not be exported to the CSV.
    
  .Example
  Get-TwitterUser -EventNumber 70 -Path twitter.csv
    
  Description
  -----------
  This command gets twitter information for SQL Saturday event #70 and
  exports that information to the twitter.csv file.
  
  .Example
  Get-TwitterUser -EventNumber 70 -Path twitter.csv -UseTwitterAPI
    
  Description
  -----------
  This command gets twitter screen names from SQL Saturday event #70, queries
  twitter for additional information about those screen names, and exports
  that information to the twitter.csv file.
  
  .Example
  Get-TwitterUser -Name "Ed Wilson" -Path twitter.csv
    
  Description
  -----------
  This command queries the twitter.csv file for entries where the Name
  property matches "Ed Wilson" and displays that information, which 
  includes the twitter screen name.
  
  .Example
  Get-TwitterUser -screenName scriptingwife -Path twitter.csv
    
  Description
  -----------
  This command queries the twitter.csv file for entries where the ScreenName
  property matches "scriptingwife" and displays that information, which
  includes the actual name.
  
  .Example
  PS C:\> Get-TwitterUser -ScreenName scriptingguys -UseTwitterAPI -Verbose
  VERBOSE: Twitter cache:
  C:\Users\Jason\AppData\Local\Temp\Get-TwitterUserCache.csv
  VERBOSE: Screen Name scriptingguys was found in local cache


  ScreenName  : ScriptingGuys
  Name        : MSFT Scripting Guys
  Url         : http://www.scriptingguys.com
  Id          : 21238450
  Description : Ed Wilson is the Microsoft Scripting Guy. He is an expert on scri
                pting technology such as PowerShell, VBScript and WMI. He is the
                author of over a dozen books. 
  Location    : Redmond, Washington, United St
  
  Description
  -----------
  This command queries twitter for the screen name "scriptingguys" and
  displays information about that twitter user with verbose output.
  
  #>
}