Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >
article

KB-33773:How to use the Python REST API to do SQL Queries to PAS

Privileged Access Service ,  

28 December,20 at 11:48 PM

Question: How is the Python REST API used to do SQL Queries to PAS?
Answer: Its possible to wrap SQL Queries in the code of the JSON Request and get a full response. The code that is here demonstrates how to do so:


#These modules are needed to have these modules installed for this to work
import json
import requests

tenant = 'TenantID'
bearer_token = 'BearerToekn'
#Anytihng Wildcard in SQL is %
Name = '%'
headers = {
    'X-CENTRIFY-NATIVE-CLIENT': 'true',
    'Content-Type': 'application/json',
    'Authorization': 'Bearer %s' % bearer_token
}
url = '%s/Redrock/query' % tenant
#SQL: search to get all server FQDNs, HealthStatus, and ID From the Server Table where the name is anyting
sql = """SELECT
Server.FQDN,
Server.HealthStatus,
Server.ID
FROM
Server
WHERE Server.Name LIKE  '%s%%'""" % Name
#giving the response a varialbe and to post the request
q = requests.post(url=url, headers=headers, json={"Script": sql}).json()
#setting the response object
responseobject = q
#this gatheres the info from the response
jsonlist = json.dumps(responseobject)
#this Makes it pretty and readable
parsed_json = (json.loads(jsonlist))
#this is the output
print(json.dumps(parsed_json, indent=4, sort_keys=True))


The response object should be sorted and contain all of the  information; further into the indentations. It should have entries like:
                "Entities": [
                    {
                        "IsForeignKey": false,
                        "Key": "512cf1ae-6e1e-4be6-981b-82df093a7391",
                        "Type": "Server"
                    }
                ],
                "Row": {
                    "FQDN": "c7gpo.test.net",
                    "HealthStatus": "Unreachable",
                    "ID": "512cf1ae-6e1e-4be6-981b-82df093a7391",#This is the unique ID string from PAS that will be used to make certain calls
                    "_TableName": "computer"


This is important because it highlights the capabilities of using the REST API to do native SQL queries and get information from the tenant without logging in. Most Centrify API calls need specific IDs or PVIDs that are unique to the tenant to make the calls. This helps by moving forward with API calls like this:
/ServerManage/DeleteResource "512cf1ae-6e1e-4be6-981b-82df093a7391" #this is the unique ID to delete the unreachable/bad system

**THIS IS A CUSTOM SCRIPT AND NOT SUPPORTED. IT IS AN EXAMPLE.**