Happy Business Starts Here

Import/Export Report Definition examples - How to migrate reports from one Zuora tenant to another

Highlighted
Zuora Staff

Import/Export Report Definition examples - How to migrate reports from one Zuora tenant to another

Thought I'd share some Import/Export report definition examples - How to migrate reports from one Zuora tenant to another?

1 REPLY 1
Zuora Staff

Re: Import/Export Report Definition examples - How to migrate reports from one Zuora tenant to anoth

Reporting has a Limited Availablity (LA) API which includes the ability to make REST API calls to:

  • Invoke an existing report
  • Export the report results
  • Query and find reports by name
  • Export a report definition to a file
  • Import a report definition from a file

While this is documented in our Knowledge Center this posting has some example calls embedded in simple shell scripts that allow you to query for reports, export a report definition and import a report definition.

 

But before proceeding please note the following:

  1. The reporting API is in LA - while it remains in LA the reporting API may change, which in turn means these examples in this post will stop working! If that happens feel free to report that here, but...
  2. These examples are not official Zuora products - you can't call support to say the script doesn't work, you can report API call problems, but the script isn't supported.
  3. The examples are hardcoded to use Sandbox tenants NOT production. Don't even think about running these scripts against a Zuora production tenant unless you're willing to take responsibility for the results, both good and bad.
  4. These scripts are Bash (terminal) scripts written on a Mac running MacOS 10.12.6 - there are no guarantee's any or all of these scripts will run on your computer, especially if it's a Windows computer. You need to find a colleague who has a Mac or Unix box to try these out. These might run under Windows with cygwin installed or if you use the newish bash support in Windows 10, but they've never been tested. If get these scripts to run on other environments please share the good news or any fixes you used to get them to run.
  5. In other words DO NOT PROCEED with the rest of this post unless you are comfortable with bash and command line utilities such as grep. If you aren't but want the ablity to migrate reports from one tenant to another our services team will be happy to help.

Here's script that will allow you to search by report name, it'll return a list of matches (if any) displaying first the report id (which you need for the other two scripts) and the associated report name. Here's the script (later there's an example invocation of the script and sample results):

 

 

#!/bin/bash
##
echo "============ Query reports ==========="
if [[ "$#" -ne "2" ]] ; then
	echo
	echo "Wrong number of parameters! "
	echo
	echo
	echo "  ./ListAllReports.sh Username@where.com SearchString"
    exit 1
fi

read -s -p "Password: " password

echo curl  -k -H "apiAccessKeyId:$1" -H "apiSecretAccessKey:NOPW" -H "Content-Type:application/json" -H "Accept:application/json"  -X GET https://zconnectsandbox.zuora.com/api/rest/v1/reports/search?query=$2 
curl  -k -H "apiAccessKeyId:$1" -H "apiSecretAccessKey:$password" -H "Content-Type:application/json" -H "Accept:application/json"  -X GET https://zconnectsandbox.zuora.com/api/rest/v1/reports/search?query=$2 | grep -e '"id" :' -e '"name" :'
echo
echo "============ Finished! ==========="
echo

You should save the above to 'ListAllReports.sh', then make it executable (chmod u+x ListAllReports.sh) and then invoke it. The first parameter is your sandbox login, the second is a text string to search for. This text string is subject to the restrictions enumerated on this KC page:

 

https://knowledgecenter.zuora.com/CD_Reporting/E_API_Reference/BA_Search_by_Report_Name

The most import restrictions are:

  • Search is not case-sensitive.
  • Spaces in the query parameter must be replaced by %20.
  • Words smaller than three letters are excluded from matching.
  • Search results include matches made from the beginning of the word only. For example a search on "count" will not trigger a match for the word "account" in the description.

Here's an example invocation and results:

 

 

./ListAllReports.sh HIDDEN@zuora.com Subscription          
============ Query reports ===========
Password: curl -k -H apiAccessKeyId:HIDDEN@zuora.com -H apiSecretAccessKey:NOPW -H Content-Type:application/json -H Accept:application/json -X GET https://zconnectsandbox.zuora.com/api/rest/v1/reports/search?query=Subscription
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
"id" : "ff80808152aeee960152d6f5a2b400f7", "name" : "Accounts with Subscriptions up for renewal in next 30 days", "id" : "ff80808152aef47c0152d6fe3fe200c3", "name" : "Cancelled subscriptions", "id" : "ff80808155e321ad0155e321e0190011", "name" : "Cancelled CMRR", "id" : "ff80808155e321ad0155e321e8950013", "name" : "New CMRR", "id" : "ff80808155e321ad0155e321e8ab0014", "name" : "New subscriptions", "id" : "ff80808155e321ad0155e321e8c10015", "name" : "Subscription changes", "id" : "ff80808155e321ad0155e321e8d50016", "name" : "Subscription retention by cohort", "id" : "ff80808155e321ad0155e321e8e70017", "name" : "Subscription term length", "id" : "ff80808155e321ad0155e321e8f80018", "name" : "Accounts with subscriptions to be cancelled in the next 30 days", "id" : "ff8080815c6c7c5a015cb1f93baf1fd1", "name" : "Subscription changes by account", "id" : "ff8080815c6c7c5a015cb205b98a2004", "name" : "Subscription term length and dates", "id" : "ff8080815ced8474015ceffce59a0681", "name" : "Subscription Details", "id" : "ff8080815d7b8478015d8032604e1446", "name" : "Subscription Changes for all Accounts", ============ Finished! ===========

Please not the REST query report call searches not only report names but their descripton also, this is why 'New CMRR' was returned for example, the report description included the word, 'subscription'.

 

Here's an example of a search string that produced no results:

 

./ListAllReports.sh HIDDEN Subscription/20%History
============ List reports ===========
Password: curl -k -H apiAccessKeyId:HIDDEN@zuora.com -H apiSecretAccessKey:NOPW -H Content-Type:application/json -H Accept:application/json -X GET https://zconnectsandbox.zuora.com/api/rest/v1/reports/search?query=Subscription/20%History
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   274  100   274    0     0    464      0 --:--:-- --:--:-- --:--:--   463

============ Finished! ===========

Note that '%20' is used instead of a space between Subscription and History, if you run the script and replace the %20 with a space you'll end up with the earlier results as only the word Subscription will be searched for. History will be interpreted as a third parameter to the script and ignored! Bash is great that way.

 

 

Hopefully you now have a list of ids and report names and you've found the id, 'ff8080815ced8474015ceffce59a0681' for example.

 

This next script will export the report definition, but you can only export by report id hence the id is the second parameter after your login:

 

 

#!/bin/bash
# 
echo
echo "============ Export Report by Report Id ==========="
echo
if [[ "$#" -ne "2" ]] ; then
	echo
	echo "Wrong number of parameters! "
	echo
	echo "  ./ExportReportById.sh Username@where.com ReportId"
    exit 1
fi

read -s -p "Password: " password

echo curl  -k -H "apiAccessKeyId:$1" -H "apiSecretAccessKey:NOPW" -H "Content-Type:application/json" -H "Accept:application/json"  -X GET https://zconnectsandbox.zuora.com/api/rest/v1/reports/report/$2 
curl  -k -H "apiAccessKeyId:$1" -H "apiSecretAccessKey:$password" -H "Content-Type:application/json" -H "Accept:application/json"  -X GET https://zconnectsandbox.zuora.com/api/rest/v1/reports/report/$2 > $2_temp.json
echo "{" > $2.json
cat $2_temp.json | grep -e \"name\" -e \"dsName\" -e \"datasource\" -e \"definition\" >> $2.json
echo "\"lableIds\": \"\"" >> $2.json
echo "}" >> $2.json
cat $2.json
echo
echo "============ Finished! ==========="
echo
rm $2_temp.json

Here's an example invocation:

 

./ExportReportById.sh HIDDEN ff8080815ced8474015ceffce59a0681

============ Export Report by Report Id ===========

Password: curl -k -H apiAccessKeyId:HIDDEN -H apiSecretAccessKey:NOPW -H Content-Type:application/json -H Accept:application/json -X GET https://zconnectsandbox.zuora.com/api/rest/v1/reports/report/ff8080815ced8474015ceffce59a0681
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 23205  100 23205    0     0  26408      0 --:--:-- --:--:-- --:--:-- 26399
{
    "name" : "Subscription Details",
    "datasource" : "Rate Plan Charge",
    "dsName" : "RatePlanCharge",
    "definition" : "{\"rowFields\":[{\"name\":\"Currency\",\"id\":\"Account.Currency\",\"label\":\"Currency\",\"type\":\"picklist\",\"order\":14,\"dataSourceName\":\"Account\",\"dataSourceLabel\":\"Account\",\"dataSourceType\":\"Account\",\"searchKey\":\"Account Currency\",\"options\":[{\"value\":\"AED\",\"label\":\"AED\"},{\"value\":\"AFN\",\"label\":\"AFN\"},{\"value\":\"ALL\",\"label\":\"ALL\"},{\"value\":\"AMD\",\"label\":\"AMD\"},{\"value\":\"ANG\",\"label\":\"ANG\"},{\"value\":\"AOA\",\"label\":\"AOA\"},{\"value\":\"ARS\",\"label\":\"ARS\"},{\"value\":\"AUD\",\"label\":\"AUD\"},{\"value\":\"AWG\",\"label\":\"AWG\"},{\"value\":\"AZN\",\"label\":\"AZN\"},

..... snippet of text removed due to report definition length .....

,\"operator\":{\"name\":\"is equal to\",\"value\":\"=\"},\"askUser\":true}]}],\"advanced\":false,\"rollingTotals\":false}",
"lableIds": ""
}

============ Finished! ===========

The full report definition will also be in a file in the same directory as the script called, in this example, ff8080815ced8474015ceffce59a0681.json. You'll need this file for the next script:

 

 

#!/bin/bash
# 
##
##
echo
echo "============ Push a report from a json file to a tenant ==========="
echo
echo "This code has no warranty and Zuora takes no responsibility of any kind "
echo "for anything that happens. Nothing. Use at your own risk. "
echo
if [[ $# -eq 0 ]] ; then
	echo "sample run from command line:"
	echo "  ./PostReport.sh Username@where.com  Sandbox-Prod FileName"
	echo "  ./PostReport.sh me@where.com  Sandbox ff8080815bb3610b015bb5e08afb03f6.json"
	echo "  ./PostReport.sh me@where.com  Prod ff8080815bb3610b015bb5e08afb03f6.json"
	echo
	echo
    exit 1
fi
##
## Default to sandbox
TENANT=zconnectsandbox

if [[ "$2" == "Prod" ]] ; then
   TENANT=zconnect
   echo "Production it is, hit ctrl-c if this isn't what you want"
   echo "Otherwise enter your tenant password:"
fi
echo $TENANT

read -s -p "Password: " password


JSON_CALL=`cat $3`
# echo $JSON_CALL
# echo curl -i -k -H "apiAccessKeyId:$1" -H "apiSecretAccessKey:NOTTHEPW" -H "Content-Type:application/json" -H "Accept:application/json" -d "$JSON_CALL" -X POST https://$TENANT.zuora.com/api/rest/v1/reports  
curl -i -k -H "apiAccessKeyId:$1" -H "apiSecretAccessKey:$password" -H "Content-Type:application/json" -H "Accept:application/json" -d "$JSON_CALL" -X POST https://$TENANT.zuora.com/api/rest/v1/reports 

echo
echo "============ Finished! ==========="
echo
echo

So for an example run:

 

 

 

 

./PostReport.sh HIDDEN Sandbox ff8080815ced8474015ceffce59a0681.json 

============ Push a report from a json file to a tenant ===========

This code has no warranty and Zuora takes no responsibility of any kind 
for anything that happens. Nothing. Use at your own risk. 

zconnectsandbox
Password: 

HTTP/1.1 200 OK
Content-Security-Policy: frame-ancestors self *.zuora.com apisandbox.zuora.com apisandbox.zuora.com
Content-Type: application/json;charset=UTF-8
Date: Thu, 03 Aug 2017 17:21:59 GMT
Server: Apache Tomcat
Content-Length: 23202
Connection: keep-alive

{
  "success" : true,
  "response" : {
    "createdBy" : "2c92c0f94820b51601483d4f3ab90a1e",
    "updatedBy" : "2c92c0f94820b51601483d4f3ab90a1e",
    "createdOn" : "2017-08-03T17:21:59+0000",
    "updatedOn" : "2017-08-03T17:21:59+0000",
    "id" : "ff8080815d9b8c7c015da91e430626db",
    "deleted" : false,
    "name" : "Subscription Details",
    "number" : "REP0000047",
    "type" : "Common",
    "datasource" : "Rate Plan Charge",
    "dsName" : "RatePlanCharge",
    "namespace" : "BASIC",
    "definition" : "{\"rowFields\":[{\"name\":\"Currency\",\"id\":\"Account.Currency\",\"label\":\"Currency\",\"type\":\"picklist\",\"order\":14,\"dataSourceName\":\"Account\",\"dataSourceLabel\":\"Account\",\"dataSourceType\":\"Account\",\"searchKey\":\

----- snippet of report detail removed due to it's length ----

{\"name\":\"is equal to\",\"value\":\"=\"},\"askUser\":true}]}],\"advanced\":false,\"rollingTotals\":false}",
 "userId" : "2c92c0f94820b51601483d4f3ab90a1e",
 "creator" : "HIDDEN",
 "updater" : "HIDDEN,
 "shared" : false,
 "zuora" : false,
 "hidden" : false
 }
}
============ Finished! ===========


This script will fail spectatularly if you include a space or other special characters in the json file name. You can code around this in bash but that's not the point of this post.