Happy Business Starts Here

Re: Exporting User List using ZOQL

Highlighted
Tutor

Exporting User List using ZOQL

Hello,

 

I was wondering if there was a way to write an API query to select all users in my tenant similar to running queries for invoices and accounts.

In the UI, I see that I can select Export Data: "All Users List CSV", but when I try to do a select * from users and pass it into https://rest.apisandbox.zuora.com/v1/object/export, I get a "The requested data source could not be found" error.

 

I am trying to build something that will help me line up who created an invoice based on the CreatedById field on the invoice with the user ID, so it would be helpful if I could also get an extract into my system to automate this analysis without me having to do vlookups after pulling the extracts.

 

Thanks in advance!

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Zuora Staff
Zuora Staff

Re: Exporting User List using ZOQL

I'm not sure what your problem is.  I was able to run this successfully in python:

#!/usr/bin/env python3
import sys
import requests

if (len(sys.argv) != 3):
    sys.exit ('Oauth and Data Query Job Id are required required')

oauth = sys.argv[1]
job_id = sys.argv[2]

dataQueryUrl = 'https://rest.apisandbox.zuora.com/query/jobs'
header = {"Content-Type": "application/json", "Authorization": "Bearer " + oauth, "Accept": "*/*"};

job = requests.get(dataQueryUrl + '/' + job_id, headers=header)
data = job.json()

file = requests.get(data['data']['dataFile'])

print (file.text)

My data query job was submitted through the Data Query UI and looks like it was using CSV as the output format, so that's the only difference I saw.  The body string in your result also looks like base64, but I don't really know why that is.

View solution in original post

7 REPLIES 7
Highlighted
Zuora Staff
Zuora Staff

Re: Exporting User List using ZOQL

We're working on creating the User Management set of APIs that would make this straight forward.  Meanwhile, you can consider using Data Query - https://knowledgecenter.zuora.com/Central_Platform/Query/Data_Query/A_Overview_of_Data_Query.  Specifically, you can find reference to User and other available tables here https://knowledgecenter.zuora.com/Central_Platform/Query/Data_Query/BA_SQL_Queries_in_Data_Query#sec....

 

Take a look at the Data Query APIs https://www.zuora.com/developer/api-reference/#tag/Data-Queries.

 

I hope this helps.

 

Chi

Highlighted
Tutor

Re: Exporting User List using ZOQL

Hi Chi,

 

Thank you very much for your quick response. I looked over the documentation and tried what you suggested. I was able to submit a data query and get the data query based on the ID that the submit generated.

 

In Postman, after the GET call, I can see the dataFile that contains a jsonl URL with a lot of parameters appended to it. Clicking on it opens up a new tab and hitting send will output all of my users in a plain text format.

 

When I run this in the system I am planning to do all of my analysis in, passing the URL in the GET command only outputs what appears to be an encrypted string... Do you know if Postman decrypts the data while it's reading it or if there's something else I need to do to get the data out in my system?

 

Thanks again for your assistance!

Ivan

Highlighted
Zuora Staff
Zuora Staff

Re: Exporting User List using ZOQL

You can check a couple of things:

  • if you submitted the query with the encryptionKey parameter, the output file will be encrypted using that key
  • if you specify a compression parameter, the output file will be compressed with the specified algorithm and the data could look encrypted if you just try to view it

If neither of those work, you can try using a different output format in case your program doesn't support jsonl.  Can you share a snippet of your code that performs this interaction?

Highlighted
Tutor

Re: Exporting User List using ZOQL

Hello Chi,

 

I have compression as "none" and no encryption.

This is what I have so far (this is NetSuite using JavaScript):

 

var header = {"Content-Type": "application/json", "Authorization": "Bearer " + BEARER_TOKEN, "Accept": "*/*"};
var requestURL = "https://rest.apisandbox.zuora.com/query/jobs";

var body = JSON.stringify({"compression": "NONE", "output": {"target": "S3"}, "outputFormat": "JSON", "query": "select * from user"});
postResponse = https.post({url: requestURL, headers: header, body: body});
var responseBody = JSON.parse(postResponse.body);
var responseID = responseBody.data.id;
requestURL = requestURL + "/" + responseID;
var getResponse = https.get({url: requestURL, headers: header});
var getResponseBody = JSON.parse(getResponse.body);
var dataFileURL = JSON.parse(getResponse.body).data.dataFile;

var dataFileResponse = https.get({url: dataFileURL});

 

 

the dataFileURL comes back as:

https://owl-auw2-sbx01-query-result.s3.us-west-2.amazonaws.com/dd861ce5-e71c-4a2f-9c90-_7558915210100413.jsonl?X-Amz-Security-Token=LXdlc3QtMiJIMEYCIQCXpkMMleoYK1nRVR2PDbIudNTqMQj3b7wykRj2Rarj%2FAIhAKDIER%2FIFJZ9WlIJ7p%2F%2BQAIxAmqNgUrk6uA8X9rqCYAyKvUDCI3%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FwEQAhoMMDQ5NzUxNzE2Nzc0IgyMzeNv%2BCDGdpp%2FZp0qyQMcsZMBThdIJp1CRKp%2BvDL%2FlFtsdZlhwGNe8lt%2Fi6WQZB0u5bGCL75SV4h3tOGk8v7QHEQxpnq89qaVgmfu8R1C%2Be0V3Mpa9Qfvj0SX4qQVHy%2Fe%2BNTKrdtBhby%2BCSRSleOBxZE1EvJgxJUpqnMczYgNbGQRygYauH%2Fj9NAAnjP4mRtLXhicPRHoTb915IozZ7DTx9qBGs0doHnW9n55esIRfv3WF2JdF7d8RZRgEklnAsjSMrn%2BjY4EcZ652xoAhiegpG4EOeIKocM1oduvxpxKhGCI82yN7wfGSX%2ByeheCY%2FAGDnDyRtstP5DWjZ1bfUSlr0T5QwN%2BhznRhi7tvfDnjbTHk53Q9Xz3f2GHGZBmvbJytRZYxzimUUzR0bFcw4U3GWmTfez2hKksmW%2Bk5lzXP3Ly7MVJE82X%2F7s5RcBCKqSwCHyrSDuurYHTTnFI%2BV2zD%2FPLLVJ6HgX8wyReq1%2BFKT3Su%2FRaDB5n%2BZtvdIsj8vvhBttUgzi6mh8OMFmIdVGWa7S%2FWJtVAXhteCn4HijI8WXiq2KhSM%2FyISCUhzxmk9TswTuDJ0v0lZ9vA1u%2FVtaw3n10GALQoJqQXVCzLDGLr8CWvIcp7snLMNvzrvYFOu4B1AkN3ikf5RkxKbUfJTcieUXx7oWm7394wK4najXQAo4thCIC7k%2Bbttj1TzKpvRHqSO1wdp1zIKrmyzaM%2F11gTo%2B9Bh%2Fcr6TnbUn7VYn1RONpyFXQvn5j3KV8fcZqRWaFHnktJJ8UJbyeTuozE4oA4LvCAHYvXrVZ7qYkU6IiBgqUG3g6Nhamsh7uwLmMBWUqXDmqZj4%2Bbu4S6YyPTXXxMygMULn6RwbL7gdBoWuRy%2BLQlK2LoZh857WwWE37%2FWsbcp5yAPZAVCgm%2B5EbNo54gi5GPc4JTnYGxzPQUp3yvkUyxfi%2BFlc%2BjzOmUsFgfw%3D%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20200525T144528Z&X-Amz-SignedHeaders=host&X-Amz-Expires=21600&X-Amz-Credential=ASIAQXFLO6OTM6HDSOPN%2F20200525%2Fus-west-2%2Fs3%2Faws4_request&X-Amz-Signature=98df1f456d563fdd326e555a98d799d8fab3c248bcfa

(I've deleted bits of the url string, but that's essentially what the URL is).

Then I pull the dataFileResponse and this is the results:

{"type":"http.ClientResponse","code":200,"headers":{"Accept-Ranges":"bytes","Server":"AmazonS3","ETag":"\"360dd68f115f9a46116bbc0ec36c5960-1\"","x-amz-request-id":"764050654A3E7206","Last-Modified":"Mon, 25 May 2020 14:45:27 GMT","x-amz-id-2":"C59zjQD/No/IdpfRtzMrj7cdxZnv43PuQTiSUjsIcVITa0GTcmfVRC3cfi9kootbexEwonb/2w8=","x-amz-server-side-encryption":"AES256","Content-Length":"7489","Date":"Mon, 25 May 2020 14:45:34 GMT","Content-Type":"application/octet-stream","Via":"1.1 mono004"},"body":"eyJlbWFpbCI6Inpjb25uZWN0LmFwaS4yMDIyNUB6dW9yYS5jb20iLCJmaXJzdG5hbWmVjdC5hcGkiLCJpZCI6IjJjOTJjMDk0NWQ1OTVjMmMwMTVkNjc2YjMyYTgyYTgyIiwibGFzdG5hbWUiOiJ1c2VyIiwidXNlcm5hbWUiOiJ6Y29ubmVjdC5hcGkuMjAyMjVAenVvcmEuY29tIn0KeyJlbWFpbCI6ImRuZWFsQGJsYWNrYmVycnkuY29tIiwiZmlyc3RuYW1lIjoic2FuZGJveCB0ZXN0IiwiaWQiOiIyYzkyYzBmODVkNTk2NGRhMDE1ZDY3NmIyNmI1NDc2ZCIsImxhc3RuYW1lIjoidXNlciIsInVzZXJuYW1lIjoiZGRvaW1vQGN5bGFuY2UuY29tIn0KeyJlbWFpbCI6ImFyQGN5bGFuY2UuY29tIiwiZmlyc3RuYW1lIjoiQWNjb3VudHMiLCJpZCI6IjJjOTJjMGY4NWQ1OTY0ZGEwMTVkNjc2YmNlMTc0OTY2IiwibGFzdG5hbWUiOiJSZWNlaXZhYmxlIiwidXNlcm5hbWUiOiJhckBjeWxhbmNlLmNvbSJ9CnsiZW1haWwiOiJqZWZmcmV5LnRhbkB6dW9yYS5jb20iLCJmaXJzdG5hbWUiOiJKZWZmIiwiaWQiOiIyYzkyYzBmODVlNWU3ZDRmMDE1ZTYzZDU4ZWZiNjhjYiIsImxhc3RuYW1lIjoiVGFuIiwidXNlcm5hbWUiOiJqZWZmcmV5LnRhbi5jeWxhbmNlQHp1b3JhLmNvbSJ9CnsiZW1haWwiOiJhbGV4LnN1dHR5QHp1b3JhLmNvbSIsImZpcnN0bmFtZSI6IiIsImlkIjoiMmM5MmMwZjg1ZTdmODkwNzAxNWU4Y2I4ZGVmNTBhZjAiLCJsYXN0bmFtZSI6IiIsInVzZXJuYW1lIjoiYXBpQGN5bGFuY2UuZGV2In0KeyJlbWFpbCI6ImNsaGFsbEBjeWxhbmNlLmNvbSIsImZpcnN0bmFtZSI6IkxhbmllciIsImlkIjoiMmM5MmMwZjg1ZjZiYzBiNjAxNWY3OThlMzYwMTJjYzciLCJsYXN0bmFtZSI6IkhhbGwiLCJ1c2VybmFtZSI6ImNsaGFsbEBjeWxhbmNlLmRldiJ9CnsiZW1haWwiOiJ0cnlhbkBjeWxhbmNlLmNvbSIsImZpcnN0bmFtZSI6IlRyYXZpcyIsImlkIjoiMmM5MmMwZjg1ZjZiYzBiODAxNWY3OThjZTYyYjcwOWEiLCJsYXN0bmFtZSI6IiIsInVzZXJuYW1lIjoidHJ5YW5AY3lsYW5jZS5kZXYifQp7ImVtYWlsIjoibnNwcmVpdHplckBjeWxhbmNlLmNvbSIsImZpcnN0bmFtZSI6Ik5pY2siLCJpZCI6IjJjOTJjMGY4NWY2YmMxN2EwMTVmNzk4ZDFmZWM3YWQyIiwibGFzdG5hbWUiOiIiLCJ1c2VybmFtZSI6Im5zcHJlaXR6ZXJAY3lsYW5jZS5kZXYifQp7ImVtYWlsIjoiYWxleC5zdXR0eStjeWxhbmNlbmV0c3VpdGVAenVvcmEuY29tIiwiZmlyc3RuYW1lIjoiIiwiaWQiOiIyYzkyYzBmODVmOWY3ZmU0MDE1ZmExZmEwMGRkMGM5YiIsImxhc3RuYW1lIjoiIiwidXNlcm5hbWUiOiJhbGV4LnN1dHR5K2N5bGFuY2VuZXRzdWl0ZUB6dW9yYS5jb20ifQp7ImVtYWlsIjoiYWxleC5zdXR0eStjeWxhbmNlc2ZkY0B6dW9yYS5jb20iLCJmaXJzdG5hbWUiOiIiLCJpZCI6IjJjOTJjMGY4NWY5ZjgwYTkwMTVmYTFmYTNhZDA2ZTQzIiwibGFzdG5hbWUiOiIiLCJ1c2VybmFtZSI6ImFsZXguc3V0dHkrY3lsYW5jZXNmZGNAenVvcmEuY29tIn0KeyJlbWFpbCI6ImRuZWFsQGN5bGFuY2UuY29tIiwiZmlyc3RuYW1lIjoiIiwiaWQiOiIyYzkyYzBmODVmYTVmY2E4MDE1ZmI2OWJjMDAwNmVkYiIsImxhc3RuYW1lIjoiIiwidXNlcm5hbWUiOiJhbGV4LnN1dHR5K2N5bGFuY2V6c3VpdGVAenVvcmEuY29tIn0KeyJlbWFpbCI6ImdwZXJlemRlZGlhekBjeWxhbmNlLmNvbSIsImZpcnN0bmFtZSI6IkdhYmJ5IiwiaWQiOiIyYzkyYzBmODYwNzhjNGQ0MDE2MDdiYjQ1ZjRkNTk0MCIsImxhc3RuYW1lIjoiUGVyZXogZGUgRGlheiIsInVzZXJuYW1lIjoiZ3BlcmV6ZGVkaWF6QGN5bGFuY2UuY29tIn0KeyJlbWFpbCI6Im1icmlkZ2VzQGN5bGFuY2UuY29tIiwiZmlyc3RuYW1lIjoiTWljaGFlbCIsImlkIjoiMmM5MmMwZjg2MGMwZmM2MDAxNjBkZDZhMGNlOTM1MjEiLCJsYXN0bmFtZSI6IkJyaWRnZXMiLCJ1c2VybmFtZSI6Im1icmlkZ2VzQGN5bGFuY2UuZGV2In0KeyJlbWFpbCI6ImRuZWFsQGN5bGFuY2UuY29tIiwiZmlyc3RuYW1lIjoiQm9vbWkiLCJpZCI6IjJjOTJjMGY4NjE3ODllZGEwMTYxOWIxZmNlMmE2NDVmIiwibGFzdG5hbWUiOiJadW9yYSIsInVzZXJuYW1lIjoiYm9vbWl6dW9yYUBjeWxhbmNlLmNvbSJ9CnsiZW1haWwiOiJiYmF0dHllQHp1b3JhLmNvbSIsImZpcnN0bmFtZSI6IkJyaWFuIiwiaWQiOiIyYzkyYzBmODYzMWFmNjljMDE2MzFjYWUxMzQ3NmU3OSIsImxhc3RuYW1lIjoiQmF0dHllIiwidXNlcm5hbWUiOiJiYmF0dHllLmN5bGFuY2VAenVvcmEuY29tIn0KeyJlbWFpbCI6ImJiYXR0eWVAenVvcmEuY29tIiwiZmlyc3RuYW1lIjoiIiwiaWQiOiIyYzkyYzBmODY1NDE5YTI4MDE2NTVkZjJlZGY1NjkwOCIsImxhc3RuYW1lIjoiIiwidXNlcm5hbWUiOiJiYmF0dHllK2N5bGFuY2VBUElAenVvcmEuY29tIn0KeyJlbWFpbCI6InJzYXdobmV5QGN5bGFuY2UuY29tIiwiZmlyc3RuYW1lIjoiUmFtaXRhIiwiaWQiOiIyYzkyYzBmODY1NjVmNDAxMDE2NTY4YWY2YzYxNmQ4MCIsImxhc3RuYW1lIjoiU2F3aG5leSIsInVzZXJuYW1lIjoicnNhd2huZXlAY3lsYW5jZS5jb20ifQp7ImVtYWlsIjoiYWNvb2tAY3lsYW5jZS5jb20iLCJmaXJzdG5hbWUiOiJBbXkiLCJpZCI6IjJjOTJjMGY4NjgzMjAzZmQwMTY4M2E4NTU0MmUwNDkzIiwibGF

When I paste the dataFileURL into Postman, it outputs the objects in plaintext, and when I paste it into Chrome, it lets me download the decrypted jsonl file without providing any credentials or keys, so I'm not sure why NetSuite would compress the results.

 

Regards,

Ivan

Highlighted
Zuora Staff
Zuora Staff

Re: Exporting User List using ZOQL

I'm not sure what your problem is.  I was able to run this successfully in python:

#!/usr/bin/env python3
import sys
import requests

if (len(sys.argv) != 3):
    sys.exit ('Oauth and Data Query Job Id are required required')

oauth = sys.argv[1]
job_id = sys.argv[2]

dataQueryUrl = 'https://rest.apisandbox.zuora.com/query/jobs'
header = {"Content-Type": "application/json", "Authorization": "Bearer " + oauth, "Accept": "*/*"};

job = requests.get(dataQueryUrl + '/' + job_id, headers=header)
data = job.json()

file = requests.get(data['data']['dataFile'])

print (file.text)

My data query job was submitted through the Data Query UI and looks like it was using CSV as the output format, so that's the only difference I saw.  The body string in your result also looks like base64, but I don't really know why that is.

View solution in original post

Highlighted
Tutor

Re: Exporting User List using ZOQL

Hi Chi,

 

You are absolutely right! It was in base64. I am not sure why when NetSuite pulled the data in that it brought it in as base64, but once I converted it back to UTF 8, everything showed up as text that I could make sense.

 

Do you know when users will be part of the API scope?

 

Thank you very much for your help! Much appreciated 🙂

Warm regards,

Ivan

Zuora Staff
Zuora Staff

Re: Exporting User List using ZOQL

User Management APIs have been prioritized and the team is working on them.  I do expect us to take more time testing due to the nature of the domain, so I don't expect an update for a few months.