Try TT Now

Advanced Data Retrieval

Overview

TT User Setup provides a command line interface that can be scripted to automate the following:

  • File Retrieval - You can write scripts that automate the retrieval of specific files (i.e. the audit trail) from the TT User Setup server’s logfiles directory. For more information refer to Retrieving Individual Files using the TT User Setup Command Line Client Application.
  • Data Retrieval - You can write scripts that automate the retrieval of data from the TT User Setup Server database. Retrieved data is saved in csv format.
    • If you cannot access the Master TT User Setup Server’s local file system, use the TT User Setup Client application to retrieve data from the current TT User Setup database. For more information refer to Retrieving Data from the TT User Setup Database (Client Side).
    • If you have access to the Master TT User Setup Server’s local file system, use the ttus_run_sql.vbs script to retrieve data from the current or saved TT User Setup database. This method is preferred over the TT User Setup Command Line Client method as it does not require data to be transferred over the network. For more information refer to Retrieving Data from the TT User Setup Database (Server Side).
Note

The TTUSCommandLineClient.exe, TTUSCommandLineClient_Sample.ini, and sample .vbs files are located in the ~tt/tt_user_setup directory.

Retrieving Individual Files using the TT User Setup Command Line Client Application

The TT User Setup Command Line Client application allows TT User Setup Super Administrators to retrieve specific files from the TT User Setup server’s logfiles folder. For example, use this application to retrieve the audit trail.

Note

Refer to the TTUSCommandLineClient_README.TXT file for additional information. For an example script, refer to the TTUSCommandLineClient_Sample_file.vbs file.

To use the TT User Setup Command Line Client application to retrieve files from the logfiles directory:

  1. On a TT User Setup client machine, create the .ini file that specifies the data you want to retrieve. Use the TTUSCommandLineClient_Sample.ini file as an example.
    1. Set the RequestType to file.
    2. For the Payload, specify the file you want to fetch. For example, to retrieve the audit trail, enter the UserSetupDbUpdates html file as the payload. Payload=UserSetupDbUpdates_2011-12-31.html.To specify an output directory other than the default (~ttlolgfiles), edit the OutputDir as needed. The output directory name must include the trailing slash ().
    3. For the OutputFile, specify the name of the output file.
  2. Execute the TTUSCommandLineClient.exe command:

    <path> TTUSCommandLineClient.exe <username> <password> <inifile>

    Where <path> is the path to TTUSCommandLineClient.exe, <username> is the a TT User Setup Super Administrator username, <password> is the username’s password, and <inifile> is the full path to the .ini file created in step 1. For example:

    C:tttt_user_setupTTUSCommandLineClient.exe TTSYSTEM 12345678 C:myTTUSiniFilesGetLogfile.ini

Retrieving Data from the TT User Setup Database (Client Side)

The TT User Setup Command Line Client application allows TT User Setup Super Administrators to retrieve data from the current TT User Setup server database. Data is transferred across the network from the Master TT User Setup server machine to the TT User Setup client machine.

Note

Refer to the TTUSCommandLineClient_README.TXT file for additional information. For example scripts, refer to the TTUSCommandLineClient_Sample_msg.vbs and TTUSCommandLineClient_Sample_sql.vbs files.

To use the TT User Setup Command Line Client application to retrieve TT User Setup Server data:

  1. From a TT User Setup Client machine, create the .ini file that specifies the data you want to retrieve. Use the TTUSCommandLineClient_Sample.ini file as an example.
    1. For the RequestType, specify one of the following:
      • msg - run sql or a report that is built into the TT User Setup Server. Results are saved to a csv file.
      • sql - run custom sql that you will specify. Results are saved to a csv file.
    2. For the Payload, specify one of the following:
      • If RequestType= msg, specify the name of the built in sql or msg. For example, Payload=get_report_most_recent_x_trader_version.
      • If RequestType= sql, enter a custom sql command. For example, Payload=SELECT user_login, user_display_name, user_most_recent_login_datetime FROM tt_user WHERE user_status = 1 AND user_most_recent_login_datetime > DateAdd('d', -30, now) ORDER BY user_login.
      To specify an output directory other than the default (~ttlolgfiles), edit the OutputDir as needed. The output directory name must include the trailing slash ().
    3. For the OutputFile, specify the name of the output file.
  2. Execute the TTUSCommandLineClient.exe command:

    <path> TTUSCommandLineClient.exe <username> <password> <inifile>

    Where <path> is the path to TTUSCommandLineClient.exe, <username> is the a TT User Setup Super Administrator username, <password> is the username’s password, and <inifile> is the full path to the .ini file created in step 1. For example:

    C:tttt_user_setupTTUSCommandLineClient.exe TTSYSTEM 12345678 C:myTTUSiniFilesGetLogins.ini

Retrieving Data from the TT User Setup Database (Server Side)

If you want to retrieve data from the TT User Setup database and have access to the Master TT User Setup Server’s local file system, TT recommends running the ttus_run_sql.vbs script locally. This eliminates unnecessary network traffic. The ttus_run_sql.vbs script runs sql statements from a specified text file and writes the output to the console or to a specified file (in csv format).

Note

Refer to the ttus_run_sql_README.TXT file for additional information.

To run the ttus_run_sql VBS script:

  1. From the Master TT User Setup machine, create the text file that specifies the sql commands to be executed.
  2. Execute the following command:

    cscript ttus_run_sql.vbs <SQL SCRIPT FILE> [db] [file]

    Where:

    <SQL SCRIPT FILE> is the text file that contains your sql commands

    [db] is the name of the database to run the command against (when omitted, the default is the database configured in ttusersetupserver.ini)

    [file] is the name of an output file (when omitted, the default is to write to console).

    For example:

    cscript ttus_run_sql.vbs sql.txtcscript ttus_run_sql.vbs sql.txt other_db.mdb > output.txt

Note

To run this command on a 64-bit Windows operating system, prepend the command with the path the syswow64 folder (typically C:Windowssyswow64). For example: C:Windowssyswow64cscript ttus_run_sql.vbs sql.txt