By using SAP's OpenSQL Tool, administrators can complement the reporting that SAP Access Control already provides. The SQL queries predefined by SAP and the examples I provide give you insight into many different areas in the system, uncovering important information, such as:

• Who are my delinquent approvers for all my current open access requests?
• What are my service times and approver type durations and percentages for my closed requests?
• Which of my role approvers has the most access requests in his or her queue?

By having access to this type of reporting, administrators can optimize the system and provide key information to those who need it, when they need it. SAP provides predefined SQL queries covering most of the modules. The tool also enables you to create custom SQL queries to directly access the database tables.

Note
Before using the tool, refer to “Technical Tips to Consider Before Using SAP's OpenSQL Tool for SAP Access Control 5.3” to determine if your company’s system is properly upgraded to the correct Support Package and whether it has the proper authorization to run it.

Predefined SQL Queries

Use the steps and URL provided in the tool titled “Tips to Consider Before Using SAP's OpenSQL Tool” to access the interface. After you access the SAP Access Control 5.3 OpenSQL Tool, use the drop-down list under the title OPENSQL Test to see the predefined SQL queries that SAP provides (Figures 1). 

Figure 1

The initial OpenSQL tool screen

The predefined SAP SQL queries are listed with limited descriptions, and many contain important information about how to change the query to work for your system as it queries specific data, such as system name and approvers (Figure 2). The predefined queries cover all application modules except Superuser Privilege Management.

Figure 2

The initial OpenSQL tool screen: Predefined SQL query drop-down list

This application tool is for access to the Java table database only and does not allow access to the ABAP database. If improved reporting is required on the Superuser Privilege Management application, work with your ABAP reporting team.

To use one of the predefined SQL queries, choose it. The tool then posts the selected query to the SQL Query box. Make any changes required to the query at this time (Figure 3). Review it for any possible data changes you might need to make.

Figure 3

A predefined SQL query statement in the SQL Query screen

To use the query you have chosen in Figure 3, click the Submit Query button. The tool runs the query you have chosen, retrieves the data, and posts it under the Result header (Figure 4). To download the data, copy it to another word-processing application, such as Microsoft Word.

Figure 4

Predefined SQL query statement processed with result

SQL Query Statements

In addition to using the predefined SQL queries, you can also create user-defined query statements and copy them into the box to be submitted. Use caution, however, because a large database table results in the tool attempting to retrieve all the data if the query is not restricted properly. Another risk, as mentioned earlier, is application data integrity. A user of this tool can actually make changes to the data in the database tables using a SQL statement.

Here are my top 10 SQL query statements that were developed for additional application reporting.

Many SQL statements are already predefined and delivered by SAP. They provide some key data access, but do not cover some of the missing reporting needs on current access requests and master data. I have listed the ones I have used most frequently and some sample results.

1. Who Are My “Delinquent” Approvers for All My Current ‘OPEN’ Access Requests?

This SQL query determines which role approvers have not yet approved the request, for each line item, for every  access request that is currently in OPEN  status. This is a good report to determine which role approvers have not acted upon the requests that are in their queue. If your company’s access request table is too large, limit it by one of the fields contained in the table VIRSA_AE_RQD_WPTRN as shown in the next two variant examples.

The code for this query, which I refer to as SQL query 1, is as follows: Select REQNO, STAGE_NAME, APPROVERID, ALTAPPROVER, STATUS, LASTUPDATE from VIRSA_AE_RQD_WPTRN where STATUS = 'OPEN'

Table 1 shows an example of the results of this query.

Table 1

Results of SQL query 1

To determine the missing approver for one request, use the following variant of SQL query 1 (SQL query 2) by inserting a WHERE clause for REQNO:

Select REQNO, STAGE_NAME, APPROVERID, STATUS, LASTUPDATE, ALTAPPROVER, LASTREMONDERDATE, ESC_STATUS from VIRSA_AE_RQD_WPTRN where REQNO = '148505' and STATUS = 'OPEN'

Table 2 shows the results of this query.

Table 2

Results of SQL query 2

To determine the missing approvers for a limited range of requests, use another variant of SQL query 1 (SQL query 3) by inserting a range (BETWEEN) clause for REQNO:

Select REQNO, STAGE_NAME, APPROVERID, STATUS, LASTUPDATE, ALTAPPROVER, LASTREMINDERDATE, ESC_STATUS from VIRSA_AE_RQD_WPTRN where STATUS = 'OPEN' and REQNO BETWEEN '148519' AND '148525' order by REQNO

Table 3 shows the results of this query.

Table 3

Results of SQL query 3

2. What Are My Service Times and Approver Type Durations/Percentages for My Closed Requests?

This SQL query (SQL query 4) uses an inner join and an access request range to build a report that can be used for request service times. With further manipulation outside of the SAP application, approver duration and percentage are customizable to fit your reporting requirements. This example compares approval duration or percentage of SAP user administrators to actual role approvers.

Here is SQL query 4: Select A.REQNO, HST_ACTION, ACTIONDATE, PATHNAME, STAGE_NAME, A.USERID, B.COMPANY, ACTION_VALUE, DESCRIPTION from VIRSA_AE_RQ_HST A inner join VT_AE_REQD_HDR1 B on A.REQNO = B.REQNO where A.REQNO BETWEEN '97000' AND '97050' and (HST_ACTION = 'OPEN' or HST_ACTION = 'APPROVE' or HST_ACTION = 'CLOSED' or HST_ACTION = 'SUBMIT_FOR_APPROVAL' or HST_ACTION = 'DELAGATE_APPROVE' or HST_ACTION = 'SUBMIT_FOR_REJECT') group by A.REQNO, HST_ACTION, ACTIONDATE, PATHNAME, STAGE_NAME, A.USERID, ACTION_VALUE, DESCRIPTION, B.COMPANY order by REQNO, ACTIONDATE

Table 4 shows the results of this query.

Table 4

Results of SQL query 4

By using Excel post processing, you can develop a report from SQL query 4 that compares your total request duration time, processing time, and percentage by administration users (if applicable) to processing time and percentage by approvers (Table 5). With this additional reporting information to detect where access request bottlenecks are, you can report on and improve service times.

Table 5

Results of a report developed with Excel from SQL query 4

3. Which of My Approvers Has Been Forwarding Access Requests?

Use this query (SQL query 5) to receive a report of all forwarded access requests, including current status and if they were returned back to the approver. If your company’s access request forwarding table is too large, try limiting it by one of the fields contained in the table VIRSA_AE_RQD_WPFWD.

Here is SQL query 5: Select REQNO, STAGE_NAME, FWDED_BY, APRVRID, FORWARD_TYPE, STATUS, LASTUPDATE, LASTREMINDERDATE from VIRSA_AE_RQD_WPFWD order by REQNO

Table 6 shows the results of this query.

Table 6

Results of SQL query 5

4. Which One of My Role Approvers Has the Most Access Requests in His or Her Queue?

This SQL query (SQL query 6) assists in identifying which one of your role approvers has too many access requests in his or her queue by counting the number of requests to approve (i.e., how many requests are sitting in the queue right now).

Here is SQL query 6: Select APPROVERID, count(Distinct REQNO) TOTAL_APPROVER_QUEUE_REQUESTS from VIRSA_AE_RQD_WPTRN where STATUS = 'OPEN' group by APPROVERID

Table 7 shows the results of this query.

Table 7

Results of SQL query 6

5. What Is the Action Taken by My Role Approvers on Their Requests?

The data returned on this query (SQL query 7) is designed to provide a list of access request role approvers’ actions that have been taken on each line item assigned. It is ordered by approver, workflow stage, and status of action. It reports the number of times the approver performs an action.

Here is SQL query 7: Select APPROVERID, STAGE_NAME, STATUS, count (distinct REQNO) total from VIRSA_AE_RQD_WPTRN group by APPROVERID, STAGE_NAME, status order by APPROVERID, STAGE_NAME, STATUS

Table 8 shows the results of this query.

Table 8

Results of SQL query 7

6. Do I Have Any Current Open Access Requests That Do Not Have ‘Company’ Selected?

This SQL query (SQL query 8) uses an inner join and an access request range to build a list of Role Approver ID ‘OPEN’ Requests that have not selected the Company field. The field contains null (i.e., EMPTY) with Stage Name and Request and Requestor user information. This is important if your application must use that field for reporting or workflow requirements.

Here is SQL query 8: Select A.REQNO, B.COMPANY, A.STAGE_NAME, A.STATUS, B.REQUESTOR, B.USERID, B.CREATORUSERID, B.REQDATE, B.REQAPPDATE, B.REQTYPE, B.PRIORITY, B.EMPTYPE, B.WFTYPE from VIRSA_AE_RQD_WPTRN A inner join VT_AE_REQD_HDR1 B on A.REQNO = B.REQNO where B.COMPANY is null and A.STATUS = 'OPEN' group by A.REQNO, A.STAGE_NAME, B.COMPANY, A.STATUS, B.REQUESTOR, B.USERID, B.CREATORUSERID, B.REQDATE, B.REQAPPDATE, B.REQTYPE, B.PRIORITY, B.EMPTYPE, B.WFTYPE order by COMPANY desc, REQNO

Table 9 shows the results of this query.

Table 9

Results of SQL query 8

7. How Many Access Requests Are Open by Company, and What Workflow Stage Are They In?

This SQL query (SQL query 9) counts the number of  OPEN requests by company and identifies their current workflow stages.

Here is SQL query 9: Select B.COMPANY, A.STAGE_NAME, count(Distinct A.REQNO) TOTAL_REQUESTS from VIRSA_AE_RQD_WPTRN A inner join VT_AE_REQD_HDR1 B on A.REQNO = B.REQNO where A.STATUS = 'OPEN' group by A.STAGE_NAME, B.COMPANY order by COMPANY

Table 10 shows the results of this query.

Table 10

Results of SQL query 9

8. How Many Access Requests Are Open, and What Workflow Stage Are They In?

Using this SQL query (SQL query 10) produces a count of all OPEN requests across the application and identifies their current workflow stages.

Here is SQL query 10: Select STAGE_NAME, STATUS, count(Distinct REQNO) TOTAL_REQUESTS from VIRSA_AE_RQD_WPTRN where STATUS = 'OPEN' group by STAGE_NAME, STATUS order by STAGE_NAME

Table 11 shows the results of this query.

Table 11

Results of SQL query 10

9. How Many Access Request Roles Are Loaded by Connected System?

This query (SQL query 11) returns the current roles by system ID, counting only for current connected systems and is ordered by system.

Here is SQL query 11: Select B.SYSTEM, count(*) total FROM VIRSA_AE_ROLDTLS A INNER JOIN VIRSA_AE_SYSROLE B on A.ROLEPROFNAME = B.ROLEPROFNAME group by B.SYSTEM order by SYSTEM

Table 12 shows the results of this query.

Table 12

Results of SQL query 11

Use a variant of SQL query 11 (SQL query 12) only for a total count of roles:

Select count(*) TOTAL_ROLES_ALL_SYSTEMS from VIRSA_AE_ROLDTLS A INNER JOIN VIRSA_AE_SYSROLE B on A.ROLEPROFNAME = B.ROLEPROFNAME

Table 13 shows the results of the variant of SQL query 12.

Table 13

Results of SQL query 12

10. Use a More Manageable SQL Query to Report Request Email Status (SAP Note 1591109)

Currently, there is no way to monitor specific email statuses via SAP Access Control 5.3. The only way to run a report on the current status is via the SQL query directly to the database tables. SAP Note 1591109 (Monitoring the Email status send from Email Dispatcher job), provides a SQL query (SQL query 13a) to review the status of emails being sent from the Compliant User Provisioning (CUP) Email Dispatcher job to troubleshoot possible emailing issues. However, the SQL query presented is not restrictive enough if you have a mature SAP Access Control environment. The SQL query returns every email the application ever sent out or attempted to send out. Running this SQL query could cause application performance and workstation issues. By restricting the SQL query using the field STATUS_FLAG (SQL query 13b) or by using REQID range (SQL query 13c), these performance issues can be mitigated.

Note
The STATUS_FLAG field has one of the following values:
CLOSED: The email is sent successfully
OPEN: The email is sent in the next job execution
REJECTED: The email cannot be sent owing to errors, such as an email server is down or an email address is wrong)

Here is SQL query 13a: Select REQID, EML_FROM, EML_TO, SUBJECT, STATUS_FLAG from VIRSA_AE_EMLLOG

Here is SQL query 13b: Select REQID, EML_FROM, EML_TO, SUBJECT, STATUS_FLAG from VIRSA_AE_EMLLOG where STATUS_FLAG = ‘OPEN’ order by REQID

Table 14 shows the results of SQL query 13b.

Table 14

Results of SQL query 13b

Here is an example of a more manageable SQL query that uses the REQID range (SQL query 13c): Select REQID, EML_FROM, EML_TO, SUBJECT, STATUS_FLAG from VIRSA_AE_EMLLOG where REQID BETWEEN '97000' AND '97005' order by REQID

Table 15 shows the results of SQL query 13c.

Table 15

Results of SQL query 13c