Building table in sql server with API help

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

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.

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.

https://auth0.com/docs/api/management/v2#!/Users/get_users

Hope this helps!

Thanks,
Dan

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

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.).

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

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

https://auth0.com/docs/extensions/user-import-export#targetText=To%20export%20your%20existing%20Auth0,be%20included%20in%20the%20export.

You could also try our python library.

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

Let me know.

Dan

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.