Extracting Reporting Data with Python

Overview

If you like the idea of using the Seismic reporting APIs to get data into files or a database, but aren't quite sure how to go about doing that, the script below may be able to help!
The Python script below can extract data from any of the reporting APIs and save that data into JSON files or a SQL database. The script also provides a useful foundation to extend for other custom data extraction needs.
This script is intended to be used with the password authentication flow or the refresh token flow.

Before You Begin

Python and pip setup

In order to run this script you will need Python installed on your machine. We recommend using Python 3.7.1 or later, but the script will also work with Python 2.7.x.
You can download the latest version of python here.
In addition to Python, we strongly recommend you have pip, the Python package manager. If you have Python 3.7.1 or later, pip comes with your Python install. If you are using an older 2.7 version of Python, you may need to manually install pip from https://pypi.org/project/pip/
We also recommend occasionally updating pip using python -m pip install --upgrade pip

Installing dependencies

Once you have Python and pip installed on your machine, you should make sure you have all of the necessary dependencies installed. If you are using Python 3, many of these are already included, if you are using Python 2, you will need to install several manually.

For Python 3, run the following:

pip install pymssql

For Python 2, run the following:

pip install pymssql
pip install urllib
pip install json
pip install argparse

Usage

This script has a set of constants which need to be added directly to the script as described in the table below

Constant

Description

TENANT

The name of your tenant. If your Seismic url is acme.seismic.com, then acme is your tenant.

CLIENT_ID

Your client_id for a password flow client. See Get Started for more info.

CLIENT_SECRET

Your client_secret for a password flow client. See Get Started for more info.

TENANT_USERNAME

The username of a user in your tenant you will use to run the APIs

TENANT_PASSWORD

The direct login password for the user (note, this is not the same as the SSO password). This user typically requires direct login privileges in the tenant. Contact your customer success team for assistance if needed.

REFRESH_TOKEN

If you would like to use a refresh token flow rather than password flow, provide your refresh token here. Note, you will need a multi-use refresh token. Leave this field blank if you want to use password flow.

SQL_SERVER

The host for your SQL server if you want to replicate the data to a SQL server. Note, this script assumes the destination is a Microsoft SQL server using port 1433, although the script can easily be modified for use with other forms of SQL servers.

SQL_DB

The name of the database to add tables to

SQL_USERNAME

The username of the user in the SQL server

SQL_PASSWORD

The password of the user in the SQL server

Once these constants are added to the Python script, the script can be executed from the command line in a tool such as Windows PowerShell.
The typical way to run the script would be to run to following command python myscriptname.py --all --json which will place all reports into your local folder in JSON format.
We also support the following arguments if you would like a bit more control over what the script runs

Command Line Argument

Description

-r or --reports

A comma separated list of reports to run from any of the reports listed on in the reporting APIs. This option should not be used if --all is used.

--all

Runs all reports and saves the data to individual JSON or SQL tables

-s or --startdate

The start date for the report such as 2017-01-01. Exclude this parameter if you want all data for the reports.

-e or --enddate

The enddate for the report such as 2019-01-01. Exclude this parameter if you want all data for the reports.

--sql

Include this parameter if you want data exported to SQL

--json

Include this parameter if you want data exported to JSON

--csv

Include this parameter if you want data exported to CSV

--prefix

Optional prefix to add to JSON filename or SQL table names

Script

  • The main() function has the primary functional code for the script taking in arguments, authenticating, calling the APIs and saving the data to JSON and SQL.
  • The getDataFromAPI() function includes the code necessary for extracting data from the APIs including the use and propagation of the Continuation header.
  • The getBearerToken() function includes the code necessary to exchange credentials to get a bearer token
  • The remainder of the functions in the script are largely supporting functions
from __future__ import print_function
import os, sys
import urllib
import json
from datetime import datetime
import time
import argparse

try: #Try Python3
    from urllib.request import urlopen, Request
    from urllib.error import HTTPError
    from urllib.parse import urlencode, quote_plus
except ImportError: # Fallback to Python 2
    from urllib2 import urlopen, Request
    from urllib import urlencode
    from urllib2 import HTTPError


TENANT=''
CLIENT_ID=''
CLIENT_SECRET=''
TENANT_USERNAME=''
TENANT_PASSWORD=''
REFRESH_TOKEN='' # Set this only if you want to use a refresh_token flow instead of password flow


SQL_SERVER=''
SQL_DB=''
SQL_USERNAME=''
SQL_PASSWORD=''


reportList = [
    'contentProfileAssignments',
    'contentProfiles',
    'contentProperties',
    'contentPropertyAssignments',
    'contentUsageHistory',
    'contentViewHistory',
    'contents',
    'externalUsers',
    'generatedLivedocComponents',
    'generatedLivedocFields',
    'generatedLivedocOutputFormats',
    'generatedLivedocSlides',
    'generatedLivedocs',
    'groupMembers',
    'groups',
    'libraryContentVersions',
    'libraryContents',
    'livesendLinkContents',
    'livesendLinkMembers',
    'livesendLinks',
    'livesendPageViews',
    'livesendViewingSessions',
    'searchHistory',
    'searchWords',
    'teamsites',
    'userActivity',
    'userProperties',
    'userPropertyAssignments',
    'users',
    'workspaceContentVersions',
    'workspaceContents'
]



###################################################################################
############  MAIN FUNCTION TO AUTH, RUN API, SAVE DATA TO JSON + SQL  ############
###################################################################################
def main():
    args = parse_args()

    ##############  LOGIN USING DIRECT LOGON CREDENTIALS TO GET AN ACCESS TOKEN  ##############
    bearer = getBearerToken(TENANT)
    
    ##############  CONNECT TO SQL IF SPECIFIED  ##############
    if(args.sql):    
        try:
            import pymssql
        except:
            print("You do not have pymssql installed.  Please try to run the following command: pip install pymssql")
            quit()
        try:
            dbCon = pymssql.connect(server=SQL_SERVER, database=SQL_DB, user=SQL_USERNAME, password=SQL_PASSWORD,  tds_version='8.0', port='1433', charset="UTF-8")
            dbCur = dbCon.cursor()            
        except Exception as e:
            print('Error trying to connect to database.  SERVER:' + SQL_SERVER + ' DATABASE:' + SQL_DB + ' USER:' + SQL_USERNAME + ' PASSWORD:' + SQL_PASSWORD)
            print(str(e))
            quit()

    ##############  GET THE LIST OF REPORTS TO RUN  ##############
    try:
        if(args.all):
            reports = reportList
        else:
            reports = args.reports.split(',')
    except Exception as e:
        print('You must specify a comma separated list of reports using --report or specify --all to run all reports')

    
    ##############  RUN EACH OF THE REPORTS  ##############
    for report in reports:        
        url = 'https://api.seismic.com/reporting/v2/' + report + '?occuredAtStartTime=' + args.startdate + '&occuredAtEndTime=' + args.enddate
        print( "Getting data from url: " + url)
        if(args.csv):
            format = 'csv'
        else:
            format = 'json'
            
        data = getDataFromAPI(url, bearer, '', format)
        print('')
        if(args.json):
            print('Creating json file ' + args.prefix + report + '.json')
            dataFile = open(args.prefix + report + '.json', "w")
            json.dump(data,datafile, indent=4)
        if(args.csv):
            print('Creating CSV file ' + args.prefix + report + '.csv')
            dataFile = open(args.prefix + report + '.csv', "w")
            dataFile.write(data.encode('utf-8'))
        if(args.sql):
            ##############  GET THE LIST OF FIELDS IN THE DATA  ##############
            tableName = args.prefix + report
            print('Creating SQL table ' + tableName)
            fields = {}
            for record in data:
                for key in record:
                    if(type(record[key]) is not dict and key not in fields):
                        fields[key] = type(record[key]).__name__
                        if(fields[key] == 'unicode'):
                            ### Try to parse it as a date, if it passes, it is a datetime field.  There is a possibility this incorrectly classifies a field as a date, but it is quite low.
                            try:
                                date = datetime.strptime(record[key][0:10], '%Y-%m-%d')
                                fields[key] = 'datetime'
                            except Exception as e:
                                pass
                
                
            
            ##############  DROP & CREATE THE TABLE IN SQL  ##############
            sql = 'DROP TABLE IF EXISTS ' + tableName
            execSql(dbCur, dbCon, sql)
            
            sqlFieldListString = ','.join('[' + k + ']' for k in fields)
            sqlFieldDefinitionString = ','.join('[' + k + '] ' + pythonToSqlType(fields[k]) for k in fields)
            
            sql = 'CREATE TABLE ' + tableName + ' ('
            sql += sqlFieldDefinitionString
            sql += ')'
            execSql(dbCur, dbCon, sql)

            ##############  ADD THE DATA TO THE TABLE  ##############
            print('Adding data to SQL . . .'),
            i=0
            for record in data:
                fieldValueList = []
                for field in fields:
                    try:
                        fieldValueList.append("'" + cleanStr(record[field]) + "'")
                    except Exception as e:
                        # If the field is not found in the given record, put a null value in SQL
                        fieldValueList.append('null')
                        pass
                    
                fieldValueListString = ','.join(fieldValueList) 

                sql = 'INSERT INTO ' + tableName + ' ( ' + sqlFieldListString + ' ) '
                sql += ' VALUES (' + fieldValueListString + ')'
                i = i + 1
                if(i == 100):
                    i = 0
                    print (".", end="")      
                    sys.stdout.flush()
                execSql(dbCur, dbCon, sql)
            print("\n\n")
            
        
    


###################################################################################
################  FUNCTION TO GET DATA FROM API WITH CONTINUATION  ################
###################################################################################
def getDataFromAPI(url, authorization, continuation, format):
    trial = 10
    while(trial >= 0):
        trial = trial - 1
        if(trial <= 0):
            print('Unable to get data from ' + url)         
            raise
        else:
            try:
                req = Request(url)
                req.add_header('Python-Script-Version', '1.1')
                if(authorization != ''):
                    req.add_header('Authorization', authorization)
                if(continuation != ''):
                    req.add_header('Continuation', continuation)
                if(format == 'csv'):
                    req.add_header('Accept', 'text/csv')
                    
                response = urlopen(req)                
                if(response.getcode() == 200):
                
                    print (".", end="")      
                    sys.stdout.flush()
                    
                    if(format == 'json'):
                        data = json.loads(response.read().decode('utf-8'))
                    else:
                        data = (response.read().decode('utf-8'))
                    headers = response.info()
                    continuationHeader = ''
                    try:
                        # If there is a continuation header, there is more data, so call this again to get more data
                        continuationHeader = headers['Continuation']
                        data = data + getDataFromAPI(url, authorization, continuationHeader, format)
                    except Exception as e:
                        pass
                else:
                    data = None
                return data
            except HTTPError as e:
                print( 'Failure(' + str(e.code) + ') on attempt ' + str(10-trial) + ' to get data from ' + url)
                continue

       
###################################################################################
#########################  FUNCTION TO RUN A SQL COMMAND  #########################
###################################################################################    
def execSql(cursor, conn, sql):
    trial = 10
    while(trial >= 0):
        if(trial == 0):
            print('Major error trying to reconnect to database')
        
        trial = trial - 1
        try:
            cursor.execute(sql)
            conn.commit()
            return
        except pymssql.DatabaseError as e:
            try:
                print( 'Error with SQL: ' + sql)
                print(  str(e))
                print( 'Attempting to reconnect to database')
                time.sleep(3)
                dbCon = pymssql.connect(server=SQL_SERVER, user=SQL_USERNAME, password=SQL_PASSWORD, database=SQL_DB, tds_version='8.0', port='1433', charset="UTF-8")                
                print( 'connected')
                dbCur = dbCon.cursor()
                conn = dbCon
                cursor = dbCur    
                print( 'continuing')  
            except Exception as e:
                print( 'Failed to reconnect')
                print( str(e))
                pass
            pass
            
            

####################################################################################
####  FUNCTION TO EXCHANGE USERNAME+PASSWORD OR REFRESH TOKEN FOR BEARER TOKEN  ####
####################################################################################   
def getBearerToken(tenant):
    if(REFRESH_TOKEN != ''):
        try:
            url = 'https://auth.seismic.com/tenants/' + tenant + '/connect/token'

            data = {'client_id': CLIENT_ID,
            'client_secret' : CLIENT_SECRET,
            'grant_type' : 'refresh_token',
            'refresh_token' : REFRESH_TOKEN
            }
            response = urlopen(url, urlencode(data).encode("utf-8"))

            d = json.loads(response.read().decode('utf-8'))
            
            return 'Bearer ' + d['access_token']
        except HTTPError as e:
            print(str(e))
            print(e.read())
            quit()
        except Exception as e:
            print('Unable to get bearer token from tenant: [' + tenant + '] using refresh token flow')
            print(str(e))
            quit()
    else:
        try:
            url = 'https://auth.seismic.com/tenants/' + tenant + '/connect/token'

            data = {'client_id': CLIENT_ID,
            'client_secret' : CLIENT_SECRET,
            'grant_type' : 'password',
            'scope' : 'seismic.reporting',
            'username' : TENANT_USERNAME,
            'password' : TENANT_PASSWORD
            }
            response = urlopen(url, urlencode(data).encode("utf-8"))

            d = json.loads(response.read().decode('utf-8'))
            return 'Bearer ' + d['access_token']
        except HTTPError as e:
            print(str(e))
            print(e.read())
            quit()
        except Exception as e:
            print('Unable to get bearer token from tenant: [' + tenant + '] using password flow')
            print(str(e))
            quit()


        
            
            
def parse_args():

    parser = argparse.ArgumentParser(
        description='Get data from the Seismic reporting APIs into JSON, CSV and/or SQL.  An example to get all data into both SQL and JSON would be getSeismicData.py --all --sql --json',
    )

    parser.add_argument(
        '-r',
        '--reports',
        help='A comma separated list of reports such as ' + ','.join(reportList[0:2]) + '. Note, these are case sensitive',
        type=str
    )
    parser.add_argument(
        '-s',
        '--startdate',
        help='The start date for the report such as 2017-01-01',
        default='',
        type=str
    )
    parser.add_argument(
        '-e',
        '--enddate',
        help='The end date for the report such as 2019-01-01',
        default='',
        type=str
    )
    parser.add_argument(
        '--prefix',
        help='Optional prefix to add to JSON filename or SQL table names',
        default='',
        type=str
    )

    parser.add_argument(
        '--all',
        action='store_true',
        help='Runs all reports to individual JSON or SQL tables'
    )    
    
    parser.add_argument(
        '--sql',
        action='store_true',
        help='Output data to SQL'
    )
    parser.add_argument(
        '--json',
        action='store_true',
        help='Output data to JSON'
    )
    parser.add_argument(
        '--csv',
        action='store_true',
        help='Output data to JSON'
    )

    
    args = parser.parse_args()
    
    if(args.sql == False and args.json == False and args.csv == False):
        print( 'Please specify either --sql or --json or --csv as output formats')
        quit()
        
    if((args.sql == True or args.json == True) and args.csv == True):
        print('You cannot export to CSV at the same time as JSON or SQL')
        quit()
        
    return parser.parse_args()
   
    
def cleanStr(s):
    if s is None:
        return ''
    elif type(s) == bool:
        return str(s)   
    elif type(s) == list:
        if(len(s) > 0):
            return '|' + cleanStr('|'.join(s)) + '|'
        else:
            return 'null'
    return unicode(s).replace(u"\u2018", "'").replace(u"\u00e2\u20ac\u2122", "'").replace(u"\u2019", "'").replace("'", "''").replace("&", "\&").encode('ascii','ignore').strip()

 

def pythonToSqlType(t):
    return {
        'unicode' : 'nvarchar(max)',
        'int' : 'bigint',
        'list' : 'nvarchar(max)',
        'bool' : 'bit',
        'datetime' : 'datetime'
    }[t]

def module_exists(module_name):
    try:
        __import__(module_name)
    except ImportError:
        return False
    else:
        return True    

if __name__ == "__main__":
    main()

Did this page help you?