What should a data export script look like?

You can write scripts to automate the export of the results of Project Tracker queries and reports that you wish to run regularly.

Below we outline an example of using a shell script to drive the "wget" tool to do just that. To execute saved Project Tracker queries and reports from a script using this example code, "wget" must be installed locally. You will also need a working knowledge of Project Tracker, HTML, HTTP, and shell scripting.

If you do not already have saved queries that you wish to execute through a script, you will need to create them. Login to the project through the web interface, then create and save the queries and reports that you'd like to be executed by your script. The scope of the saved queries/reports should be "Personal", and configured such that the results are exported to a MS Excel or TSV file from which they can be easily extracted for analysis by external tools (i.e. a spreadsheet).

You will need the URL of the saved query or report to execute from the script. To get the URLs of saved queries, go to the Saved query list page. To get the URLs of saved reports, go to the Saved report list page. Use your browser's "View page source" function to pull up the raw HTML source view of the page. Then, use your browser's built-in text search function to locate the name of your query in the "source" page. The string follows the pattern <a href="[URL]">[query name]</a>. Copy only the [URL] portion of the string to use in your script for execution of that query or report.

Your script will need to contain commands for the following operations:

• Log in: When logging into the domain through a script powered by "wget", the login information is passed via URL. The username and password must conform to the rules for a URL, i.e., no un-encoded spaces, underscores, or other dis-allowed punctuation. While special characters are not generally found in usernames, the domain may require that they be used in passwords. If the password uses special characters, it would need to be properly encoded.
  
  • If your site does not use client certs, use the following "wget" command to log into the domain:
    
    • wget --header='Cookie: JSESSIONID=' '[domain name]/servlets/TLogin?Login=Login&loginID=[username]&password=[password]'
  • If your site uses client certs, use the following "wget" command to login to the domain:
    
    • wget --sslcertfile=./[domain name].crt --sslcertkey=./[domain name].key --header='Cookie: JSESSIONID=' 'https://[domain name]/servlets/CertLogin?Login=Login&password=[password]'
    .
• Execute the saved query/report: Using "wget", this command executes the query/report and saves the response to the file you specified in [filename]. The [project name] corresponds to the name of the project, where the saved query/report resides.
  
  • wget -O [filename] --header='Cookie: JSESSIONID=' 'http://[project name].[domain name]/[URL]'