000023289 - A custom query which obtains information from multiple tables

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 5Show Document
  • View in full screen mode

Article Content

Article Number000023289
Applies ToRSA Authentication Manager 6.1
Authentication Manager 6.0
RSA ACE/Server 5.2
RSA ACE/Server 5.1
Custom SQL Query
IssueCustom Query which obtains information from multiple tables
Invalid select statement.
Cause

A customer desires to create a report which lists information about a user including custom user extension data and token information, for example:

                 Login                       Last                   First                     Token                     Personal data
                 --------------            ----------------     -------------           ---------------          ----------------------------
                 jdoe                        Doe                    John                    7034598273           Home phone       945 555 23495
                 hsmith02                 Smith                  Harry                  7034827488           Mobile                  +61 324 09765

The SQL statement they have tried generates an "Invalid select statement." when they press the Check Syntax or Next> buttons


The query needs to be able to extend across three different tables in the database since not all the information is held in a single table.  This means that a query must be devised with obtains data from each place and then combines it as required.

An example of a valid query could be:

                    SELECT chDefaultLogin, chLastName, chFirstName, chSerialNum, chKey, chData
                    FROM SDUser
                         JOIN CustUserExtension 
                               ON SDUser.iUserNum = CustUserExtension.iUserNum 
                        JOIN SDToken 
                              ON SDUser.iUserNum = SDToken.iusernum

Note that this will actually generate multiple lines if a user has been assigned more than one token; also there are many other way to use SQL to obtain the desried ouput - this simply demonstrates one particular way.

In general it is important to consider how you use SQL; on large databases it is possible to write a badly formed SQL statement which is valid but will consume all the memory of a computer when executed.  SQL specialists (not part of RSA) will  know many ways in which SQL statements should be optimized for maximum efficiency.

RSA will be happy to assist in the design of specific custom queries to meet customer requirements; however this is a chargeable service from RSA Professional Services who will be able to quote charges for the desired ad-hoc work.

Other useful knowledgebase articles on the subject may be found at:

                      A custom query which obtains information from multiple tables         A custom query which obtains information from multiple tables
                      How tolist RSA SecurID tokensupdated andassignedin last Xdays using custom SQL query         How tolist RSA SecurID tokensupdated andassignedin last Xdays using custom SQL query
                      How to perform a custom query for old users whohave not authenticated in the past 90 days         How to perform a custom query for old users whohave not authenticated in the past 90 days
                      How to write acustom query to list active users who have used their token         How to write acustom query to list active users who have used their token
                      Custom Query that will show group members last login activity         Custom Query that will show group members last login activity
                      Custom query error:  "SYSTEM ERROR: -s exceeded. Raising STOP condition and attempting to write stack trace to file 'procore'. Consider increasing the -s startup parameter. (5635)"  The query does not successfully complete         Custom query error:  "SYSTEM ERROR: -s exceeded. Raising STOP condition and attempting to write stack trace to file 'procore'. Consider increasing the -s startup parameter. (5635)"  The query does not successfully complete

A variety of manuals are referenced in this solution, copies are supplied with the software and online copies are available in RSA SecurCare Online.

 

Authentication Manager 6.1 Administration Toolkit Reference Guide

https://knowledge.rsasecurity.com/docs/rsa_securid/rsa_auth_mgr/61/authmgr_admin_toolkit.pdf

 

Authentication Manager 6.0 Administration Toolkit Reference Guide

https://knowledge.rsasecurity.com/docs/rsa_securid/rsa_auth_mgr/60/ace_admin_toolkit_60.pdf

https://knowledge.rsasecurity.com/docs/rsa_securid/rsa_auth_mgr/61/authmgr_admin_toolkit.pdf 

ACE/Server 5.2 Administration Toolkit Reference Guide

https://knowledge.rsasecurity.com/docs/rsa_securid/rsa_ace_server/52/ace_admin_toolkit_52.pdf

 

ACE/Server 5.1 Administration Toolkit Reference Guide

https://knowledge.rsasecurity.com/docs/rsa_securid/rsa_ace_server/51/ace_admin_toolkit_51.pdf 

 

Legacy Article IDa33139

Attachments

    Outcomes