Using cURL to automate Project Tracker queries and reports

When you need to run a saved Project Tracker query or report repeatedly, you can write a shell script using the cURL utility to automate the process of running the query or report and exporting it to a spreadsheet format, from which it can be extracted for analysis by an external tool, such as a spreadsheet.

cURL is a command line tool for getting or sending files using URL syntax. It supports a range of common Internet protocols, such as HTTP, HTTPS, FTP, and FTPS. For more information about cURL, see the cURL project web site.

Note: To use this example code, you must have cURL installed locally. You will also need a working knowledge of Project Tracker, HTML, HTTP, and shell scripting.

To create a script, follow these steps.

1. If you do not already have saved reports or queries that you wish to execute through a script, you must create them and record the URL for their output. To create a report of query and get the URL, follow these steps:
   1. Log into the project through the web interface.
   2. Create and save the queries and reports that you want your script to execute.
      • The scope of the saved queries/reports should be "Personal."
      • Configure the saved queries/reports so that the results are exported to a MS Excel or TSV file.
   3. Go to the appropriate page to see your saved query or report.
      • 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.
   4. Use your browser's "View page source" function to pull up the raw HTML source view of the page and locate the name of your query in the HTML source page.

      Note: 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 example, suppose the query returns this URL:

      https://project.example.com/servlets/Scarab/curmodule/
2491/remcurreport/true/template/IssueList.vm?queryId=2025
&action=Search&eventSubmit_doSearch=Search&searchsp=asc&
resultsperpage=-1&attv=__11&attv=__188&attv=__3&attv=__16&attv=
__178&attv=__173&attv=__179&attv=__4&attv=__7&attv=__177&attv=
__174&attv=__22&attv=__5&attv=__175&attv=__19&attv=__176&
searchscfoi=0&searchscai=3&format=excel&intake-grp
=mv&intake-grp=attv&intake-grp=query&intake-grp=search&
searchsctoi=0&curmitlistid=1245

      • The portion in bold is your URL.
      • The portion in italics is used as an argument in the command.

      You will use these to build the command.

2. Use this cURL command to log into the domain:

   curl -c cookies.txt -d 'loginID=[Login]&password=[password]&Login=Login' 'http://[domain]/servlets/Login?Button=Login'

Notes:

• If your site uses SSL, consider using the -k option, which suppresses certificate validation and allows cURL to connect to SSL sites without certificates. For help defining the commands for SSL-enabled sites, see the cURL user documentation.
• The -k option to suppress certificate verification should only be used if you can verify the server's authenticity in some other way.
• To convert your badge to pem, use this command: openssl pkcs12 -in /director/folder/hp.p12 -clcertificates -out private.pem
• When you log into the domain through a script, the login information is passed via URL. The username and password must conform to the rules for a URL: no unencoded spaces, underscores, or other prohibited 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.
• Your password (marked here by [password]) will be visible to anyone who can see your scripts.
3. Query results: Use this command to execute the saved query and save the response to the file you specify.

   curl -b cookies.txt -d 'queryId=125&action=Search&eventSubmit_doRunstoredquery=Search&curmitlistid=140' 'http://[project name].[domainname]/servlets/Scarab/remcurreport/true/template/IssueList.vm'>[query].xls

4. Report results: Use this command to execute a saved report and save the response to the file you specify.

   curl -b cookies.txt -d 'report_id=100&eventsubmit_dogeneratereport=foo&action=ConfigureReport' 'http://[project name].[domain name]/servlets/Scarab/template/reports%2CReportList.vm'>[report].xls