Building table in sql server with API help

[mirandamo]

I’m trying to build a python script and extract information such as “user email”, and “last log in” time from my Auth0 account, and place them in my sql server. Im wondering how i could extract such information with the help of API

[dan.woda]

Hi @mirandamo,

Welcome to the Auth0 Community Forum!

If you are attempting to export a large group of users (or all users) then the export extension is a great tool.

https://auth0.com/docs/users/guides/bulk-user-exports

Otherwise you can use the user endpoints of the management api to gather specific information about individual users. You can even try simple requests and queries directly in your browser using our explorer.

Auth0 Management API v2

Hope this helps!

Thanks,
Dan

[mirandamo]

Hi there, i did exactly how it is in the bulk user exports. and this is what i received:

Error

Cannot POST /lqdfinance.auth0.com/api/v2/jobs/users-exports

[dan.woda]

It sounds like your post request is not formatted correctly. Can you post the code you are using to make the request. Please omit any sensitive data (tokens, IDs, names, secrets, etc.).

[mirandamo]

import sys
sys.path.append(“D:\home\site\wwwroot\env\Lib\site-packages”)

import quickbase
import urllib2
import pypyodbc
import timestring
import datetime
import time
import math
import sys
import locale
import smtplib
import pypyodbc
import httplib
import json
import urllib
import urllib2

from dateutil.relativedelta import relativedelta

Activate Webhook with Campaign is added or modified.

0.1 establish connection to SQL server

connection = pypyodbc.connect(‘Driver={SQL Server Native Client 11.0};’
‘Server=tcp:xxxxe.database.windows.net,1433;’
‘Database=LQDC;’
‘uid=Admin@lxx;pwd=xxxx’)
cursor = connection.cursor()

0.2 establish connection to Auth0

def main():

Configuration Values

AUDIENCE = “https://xxe.auth0.com/api/v2/”
DOMAIN = “lxxx.auth0.com”
CLIENT_ID = “xxxxxL”
CLIENT_SECRET = “xxxxxxr”
GRANT_TYPE = “client_credentials” # OAuth 2.0 flow to use

Get an Access Token from Auth0

base_url = “https://{domain}”.format(domain=DOMAIN)
data = urllib.urlencode([(‘client_id’, CLIENT_ID),
(‘client_secret’, CLIENT_SECRET),
(‘audience’, AUDIENCE),
(‘grant_type’, GRANT_TYPE)])
req = urllib2.Request(base_url + “/oauth/token”, data)
response = urllib2.urlopen(req)
oauth = json.loads(response.read())
access_token = oauth[‘access_token’]

Get all Applications using the token

req = urllib2.Request(base_url + “/api/v2/clients”)
req.add_header(‘Authorization’, 'Bearer ’ + access_token)
req.add_header(‘Content-Type’, ‘application/json’)

try:
response = urllib2.urlopen(req)
res = json.loads(response.read())
print res

conn = httplib.HTTPSConnection(DOMAIN)
payload = "{\"connection_id\":\"not sure which API/token/code to put here\",\"format\": \"csv\", \"limit\": 4, \"fields\": [{ \"name\": \"email\"},{\"name\": \"last_ip\"}, { \"name\": \"created_at\"}, { \"name\": \"last_login\"}]}"
headers = {
    'authorization': "Bearer "+access_token,
    'content-type': "application/json"
    }
conn.request("POST", "/xxxx.auth0.com/api/v2/jobs/users-exports", payload, headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))

except urllib2.HTTPError, e:
print 'HTTPError = ’ + str(e.code) + ’ ’ + str(e.reason)
except urllib2.URLError, e:
print 'URLError = ’ + str(e.reason)
except urllib2.HTTPException, e:
print ‘HTTPException’
except Exception:
print ‘Generic Exception’

Standard boilerplate to call the main() function.

if name == ‘main’:
main()

the first part works fine, second part returns the error stated above

[dan.woda]

You may be better off trying the user export extension, unless you need to do it programatically.

User Import / Export Extension.

You could also try our python library.

GitHub - auth0/auth0-python: Auth0 SDK for Python

The auth0.jobs.export_users() function would remove some of the headaches associated with making the vanilla POST request.

Let me know.

Dan