Oracle DRM (Data Relationship Management) Application Logs

To access Oracle DRM (Data Relationship Management) application related activities log, there are two methods:

1. Auditing Transactions, Jobs, and Requests through DRM Web Client
2. Extracting DRM (Data Relationship Management) application log from backend DRM application database schema

Auditing Transactions, Jobs, and Requests through DRM (Data Relationship Management) Web Client

Oracle Data Relationship Management records a history of different activities performed within an application for audit purposes. 

• The transaction history logs all operations performed including changes to version data, application metadata, and user security. 
• The job history tracks the completion of long running processes such as imports, blenders, and exports. 
• The request history maintains a record of change requests submitted from external applications.
  

For further details you can read DRM (Oracle Data Relationship Management) Suite User's Guide.

You can query and view transactions, requests, and job history using the Audit task wizard in DRM Web Client navigating through Home --> Audit as shown below:

The audit wizard is used to define the parameters and criteria for the query. The wizard content is organized across Source, Filters, and Columns tabs to guide input. Although the wizard tabs are ordered to gather input in a sequence, you can navigate between any tabs.

Extracting DRM (Data Relationship Management) application log from backend DRM application database schema

Extracting DRM Transaction History:

In DRM application database schema, there is a table name RM_TRANSACTION_HISTORY that contains a row for each transactional update made to your DRM repository.

Below is all the columns listed what RM_TRANSACTION_HISTORY table consists of: 

Name                                       Null                     Type            

-------------------------- -------- --------                    ------- 

I_TRANSACTION_ID           NOT NULL         NUMBER(38)      

I_DETAIL_ID                         NOT NULL         NUMBER(38)      

D_TIMESTAMP                                                 TIMESTAMP(6)    

I_SESSION_ID                                                   NUMBER(38)      

C_USER_NAME                    NOT NULL        NVARCHAR2(100)  

C_ACTION                                                         NVARCHAR2(40)   

C_ACTION_LEVEL                                          NVARCHAR2(40)   

C_VERSION_ABBREV                                    NVARCHAR2(255)  

I_VERSION_ID                                                  NUMBER(38)      

C_HIERARCHY_ABBREV                              NVARCHAR2(255)  

I_HIERARCHY_ID                                           NUMBER(38)      

C_NODE_ABBREV                                           NVARCHAR2(255)  

I_NODE_ID                                                       NUMBER(38)      

C_NODE_DESCR                                             NVARCHAR2(255)  

C_PROPERTY_ABBREV                                  NVARCHAR2(255)  

I_PROPERTY_ID                                               NUMBER(38)      

B_LEAF                                     NOT NULL     NUMBER(38)      

C_ACTION_DESCRIPTION                             NVARCHAR2(1024) 

X_FROM_VALUE                                                NCLOB           

C_FROM_ORIGIN                                               NVARCHAR2(1024) 

X_TO_VALUE                                                      NCLOB           

C_TO_ORIGIN                                                     NVARCHAR2(1024) 

I_ORIGINATING_ID                                             NUMBER(38)      

I_ORIGINATING_DETAIL_ID                             NUMBER(38)      

C_OBJECT_NAME                                                NVARCHAR2(255)  

I_DOMAIN_ID                                                       NUMBER(38)      

C_DOMAIN_ABBREV                                          NVARCHAR2(255)  

I_WORKFLOW_REQUEST_ID                              NUMBER(38)      

I_WORKFLOW_REQUEST_ITEM_ID                  NUMBER(38)      

C_NODE_TYPE                                                       NVARCHAR2(255)  

As we see, RM_TRANSACTION_HISTORY columns include: Action (e.g. Add Node, Insert Node, Delete Node, and ChangeProp), Node, Hierarchy, Version and Property Name values as well as Node Description, Leaf flag, to and from Property values for ChangeProp operations.

You can query this RM_TRANSACTION_HISTORY table to extract DRM application related logs for a particular time period.

For example, if you want to extract DRM Application log from 13-Mar-2021 to 19-Mar-2021, run following query:

select * from RM_Transaction_History where D_TIMESTAMP between to_date('2021-03-13 00:00:01', 'YYYY-MM-DD HH24:MI:SS') and to_date('2021-03-19 23:59:59', 'YYYY-MM-DD HH24:MI:SS') order by D_TIMESTAMP;

Output:

Note: Its preferable to use SQL Developer to extract DRM application logs from backend schema. Make sure in SQL Developer, you have set the date and time format same as mentioned in above query.

Extracting DRM Job History:

DRM Job history is stored in a table name RM_JOB_HISTORY that consists of following columns:

You can query this table RM_JOB_HISTORY to extract all job run details.

For example, if you want to extract DRM Job History log from 13-Mar-2021 to 19-Mar-2021, run following query:

select * from RM_Job_History where D_TIME_STARTED between to_date('2021-03-13 00:00:01', 'YYYY-MM-DD HH24:MI:SS') and to_date('2021-03-19 23:59:59', 'YYYY-MM-DD HH24:MI:SS') order by D_TIME_STARTED;

Output:

Extracting DRM Request History:

DRM Request history is stored in a table name RM_REQUEST that consists of following columns:

You can query this table RM_REQUEST to extract all change requests submitted from external applications.

select * from RM_REQUEST;

That's all for this post.
I hope this article has helped you.
Your suggestions/feedback are most welcome.
Keep learning and Have a great day!!!

Read More

How to Extract Oracle DRM (Data Relationship Management) Users List and Access Attributes

Tracking changes regarding application users and their accesses in the Production environment is a very important part of your Hyperion application auditing. DRM is one such application where the Business team performs regular user auditing because your critical Hyperion metadata is managed here.

Here we are going to explore some of the very handy and important Oracle database queries using which you can check your Data Relationship Management (DRM) users, their assigned roles, roles permissions, Property Categories access, Node Access Groups Access, Object Access Groups Access etc.

These queries are also helpful for your DRM Licensing based on the DRM user base.

Using these queries, you can automate Data Relationship Management (DRM) users tracking process in your Production environment. For automation, you just need to put your chosen query in a .sql file, add SQL output formatting options as per your need, call that .sql file from a batch script and send the formatted output to the recipients using an email script.

In DRM_DB schema/repository where your Oracle Data Relationship Management (DRM) application is configured, there are many tables that contain information regarding your DRM application users, their roles, etc. but in a distributed manner, i.e. there is no single table, which consists of everything. Therefore, you need to join multiple tables to fetch the required users' information.

What will you learn in this ebook?:

• 30+ very handy and important DRM database queries with outputs and actual screenshots that you can directly use not only to check your Oracle DRM Users and their access tracking but also to automate them. 
• Various DRM database tables, which consist of information regarding DRM users and their various access levels.
• DRM Security concept and different levels of  DRM security options under the following headings:

Queries to extract DRM Users and their assigned DRM Roles:  

• Query to list out all the DRM users including those users who do not have any DRM ROLE assigned.
• Query to list out all your DRM business users (Active Directory users) who are externally authenticated through Hyperion Shared Services.
• Query to list out all the DRM Roles and their full list of permissions.
• Query to list out all the DRM users with all of their assigned roles (concatenated in one row), who have been assigned at least one DRM role. 
• Query to list out only those DRM users with all their assigned roles who are externally authenticated through Hyperion Shared Services.
• Query to list out all those DRM users who have been assigned 'Application Administrator' role in your DRM application.
• Query to list out all those DRM users who have been assigned 'Application Administrator' role in your DRM application and who are externally authenticated through Hyperion Shared Services.
• Query to list out all those DRM users who have been assigned EITHER 'Interactive User' OR 'Application Administrator' role in your DRM application. 
• Query to list out all those DRM users who have been assigned EITHER 'Interactive User' OR 'Application Administrator' role in your DRM application and who are externally authenticated through Hyperion Shared Services. 
• Query to list out all those DRM users who have been assigned BOTH 'Access Manager' and 'Application Administrator' role in your DRM application. 
• Query to list out all those DRM users who have been assigned BOTH 'Access Manager' and 'Application Administrator' role in your DRM application and who are externally authenticated through Hyperion Shared Services. 

Queries to extract DRM Users and their assigned Property Categories Access:

• Query to list out all the DRM Users with their assigned Property Category names and Access Levels (Read/Edit).
• Query to list out only those DRM users with their assigned Property Category names who have READ level access.
• Query to list out only those DRM users with their assigned Property Category names who have EDIT level access.
• Query to list out only those DRM users with their assigned Property Category names and Access Levels (Read/Edit) who are externally authenticated through Hyperion Shared Services.
• Query to list out user detail with his/her assigned Property Category names and Access Levels (Read/Edit) for a particular DRM user. 

Queries to extract DRM Users and their assigned Node Access Groups:

• Query to list out all the DRM Users with their assigned Node Access Groups (in one row). 
• Query to list out only those DRM Users with their assigned Node Access Groups who are externally authenticated through Hyperion Shared Services.

Queries to extract DRM Users and their assigned Object Access Groups:

• Query to list out all the DRM Users with their assigned Object Access Group and Object access level.
• Query to list out only those DRM Users with their assigned Object Access Group and Object access level who are externally authenticated through Hyperion Shared Services.

To purchase the ebook, choose any of the below given payment options:

Option-1 : Buy on PayPal ( Price = $4 ):

Option-2 : Buy in INR on Instamojo ( Price = INR 295 ):

Option-3 : To pay in INR via UPI (GooglePay or Paytm), please use below given links. Post payment, drop an email to skp.1world@gmail.com with payment screenshot. After verifying the payment, we will share the pdf ebook with you on the same email.

Paytm Payment Link --> Paytm (INR 295)

# For any query/issue regarding the ebook purchase, please feel free to drop an email at skp.1world@gmail.com. Thank you!

Read More

DRM: How to check DRM User creation date

Topic:  How to find out 'Users creation date' in Oracle Data Relationship Management (DRM)

Hi Friends,

Auditing of users is an important part of any Oracle Hyperion Production system. You might have faced a scenario where you need to find out when a particular business user was added or assigned access into your Hyperion applications to cross-check whether there was a corresponding change request or incident number raised for record or not. These are some pertinent questions, which you have to find answers to, during Oracle Hyperion applications auditing.  

In this post, we will explore how to find out when was a new user created/added into your Oracle Data Relationship Management (DRM) system.

Important Note:

• This post has been written and associated activities have been demonstrated on Oracle Data Relationship Management (DRM) version 11.1.2.4.344.
• The demonstrating Hyperion environment has Oracle database server 12.2.0.2 (18c) as backend database. 

When you log in to Oracle Data Relationship Management (DRM) Web Client with DRM Administrator account and navigate to: Administer—Security—Users, you will see your DRM users list as shown below:

A new user is added/created to Oracle Data Relationship Management (DRM) system either directly from DRM Web Client (Administer—Security—Users) or synchronized from Hyperion Shared services when ‘Common User Provisioning’ is enabled in your Oracle DRM system.

Adding/creating a new user in Oracle DRM is a kind of transaction that is recorded in a table name RM_TRANSACTION_HISTORY in DRM_DB relational repository schema.

RM_TRANSACTION_HISTORY table contains a row for each transactional update to your DRM repository (DRM_DB schema).

There is a column name C_ACTION in the table RM_TRANSACTION_HISTORY that records activities/actions like Add User, Add Node, Insert Node, Delete Node, ChangeProp, etc. 

In order to extract Oracle DRM users creation date along with who created that user, perform below steps:

1- Login to your DRM_DB relational repository schema (using SQL developer).

2- Run the below query:

select C_USER_NAME as USER_NAME, C_ACTION as ACTION, C_ACTION_DESCRIPTION as ACTION_DESCRIPTION, D_TIMESTAMP as Date_Time from RM_Transaction_History where C_ACTION = 'Add User' order by Date_Time desc;

3- Your output will be generated as shown below:

Description of the output:

• USER_NAME = The already existing DRM user who has created/added the new user.
• ACTION = Add User activity is performed
• ACTION_DESCRIPTION = User ‘NEW USERNAME’ added
• DATE_TIME = Date and time when the NEW USERNAME was created/added into Oracle DRM system in descending order (Newest user creation date first--oldest user creation date in last).

Optionally, if you would like to display DATE_TIME in 'YYYY-MM-DD HH24:MM:SS' format:

Run the below query:

select C_USER_NAME as USER_NAME, C_ACTION as ACTION, C_ACTION_DESCRIPTION as ACTION_DESCRIPTION, TO_CHAR(D_TIMESTAMP, 'YYYY-MM-DD HH24:MM:SS') as Date_Time from RM_Transaction_History where C_ACTION = 'Add User' order by Date_Time desc;

Note: In the above two examples, USER_NAME column signifies different kinds of DRM users who can create/add new users into Oracle DRM system. Below is the detail of the same:

• USER_NAME = ‘ADMIN’ implies that the new user has been created/added by DRM Default Administrator
• USER_NAME = ‘@@PROCESS’ implies that the new user has been created/added by synchronization process from Hyperion Shared Services  in a ‘Common User Provisioning’ enabled Oracle DRM application. '@@PROCESS' is a default internal user in Oracle DRM set up to handle inter-process communication between server components.
• USER_NAME = ‘Any existing application user’ implies that the new user has been created/added by an existing DRM business user who has been assigned privileges (Application Administrator privilege) to create/add a new user into Oracle DRM application. 

That's all for this post.

I hope this article has helped you. 

Your suggestions/feedback are most welcome.

Keep learning and Have a great day!!!

Read More

DRM: There are currently no applications available for login

Hi Friends,

Most of you would have faced Oracle Data Relationship Management (DRM) Error: There are currently no applications available for login error while logging to Data Relationship Management (DRM) Web-client. No exception to that, I too faced one such instance in recent times.

In this post, we will see what are things to check in Oracle Data Relationship Management (DRM) when you encounter this error and how this issue got fixed for me.

Note:

• This post has been written and associated activities have been demonstrated on Oracle Data Relationship Management (DRM) version 11.1.2.4.344.
• The demonstrating Hyperion environment has Oracle database server 12.2.0.2 (18c) as backend database. 

One fine morning, one of our DRM users had reported that he is not able to login to DRM Web Client encountering the following error message:

Error: There are currently no applications available for login 

Immediately I opened DRM Web Client to confirm whether it's working with ADMIN credentials or not. Like the user, I too faced the same error even with Data Relationship Management (DRM) ADMIN account.

When I checked the DRM Web services status in the service panel, they were running perfectly fine.

To cross-check the Data Relationship Management (DRM) Web Services status, I connected to Oracle Data Relationship Management (DRM) Configuration Console (as an Administrator):

In Configuration Console, I noticed that, though DRM Web services were up and running, Data Relationship Management (DRM) application was stopped.

I thought why not first validate the database connection here and did the same. 

Database connection was successful.

Its time to start the application.

But starting application did not work. I could see, the application was still in STOPPED state.

Alright, let's search for some errors now.

Open the Windows Event Viewer and Explore Windows Logs-->Application

I noticed total 3 DRM error messages recorded in Event viewer:

Error-1:

Error-2:

Error-3:

Going through the above DRM Error messages, we come to know that DRM application engine could not be started because of:

DRM-63015: Read/Write engine for Application on DRM Server will not start because another Application is currently using the same repository.

Generally, 'DRM-63015' error message indicates that your DRM schema might be locked because some other DRM application has already connected to it i.e. when two DRM applications try to connect to the same DRM schema at the same time, we face 'DRM-63015' error.

This happens when you have two DRM application servers in your Hyperion environment i.e. two DRM Configuration consoles and the secondary DRM application server has been configured to connect to the same DRM schema as the primary DRM application server and you try to start the DRM application on both the servers at the same time.

Ideally, you need to ensure that only one DRM console has been configured to connect to any given DRM schema.

In my Hyperion environment, there is only one DRM Server and only one DRM application. So the above scenario is not applicable at least in my case.

Also when I checked, my DRM Schema was not Locked:

So let's proceed further and try to list out possible causes along with troubleshooting steps to tackle this error. You can try these options in the given sequence and any of these may work for you. 

1- In a particular EPM environment, you might have DRM installed on two servers for load balancing. But you need to make sure that only one DRM configuration console (DRM installation) has been configured to connect to your DRM schema. Sometimes what happens that by mistake we configure the secondary DRM application server too to connect to the same DRM application schema as the primary DRM application server (i.e. connection details to the same DRM schema have been set up on two separate DRM servers) and make an attempt to start the DRM application on both the servers. When you configure two different DRM configuration consoles of your EPM environment try to connect to the same DRM schema at the same time, we face 'DRM-63015' error. Therefore make sure its not the case in your DRM setup. 

2- Make sure your DRM service 'startup type' is set to Manual and not Automatic on all the DRM servers. This will prevent the auto-start of your DRM service during any unplanned server reboot. This is important especially when you have CUP (Common User Provisioning) enabled in your DRM application.

Also, change your DRM service 'log on as' account from Local system to EPM Admin domain account (which was used to install and configure DRM) on all the DRM servers.

3- Do 'iisreset' on Oracle Data Relationship Management (DRM) application server.

As DRM's primary web interface runs through IIS (Microsoft Internet Information Service), there's a chance bouncing IIS will free up whatever rogue session might have been locking things up for you.

To bounce IIS service, open a command prompt on your DRM application server and type "iisreset":

If this workaround works for you, it's well and good, If not, move to the next troubleshooting step 

4- 'Apply Updates' in Oracle Data Relationship Management (DRM) Configuration console.

To 'Apply Updates' follow the below steps: 

1- Open Windows service panel and stop DRM Web services. 

2- Check the DRM Configuration console to ensure DRM services are stopped.

3- Now goto DRM Configuration console and select Application-->Apply Updates

4- Click OK on the below message while Applying updates:

5- You will see below CMD window opened. As there were actually no updates to apply so we see the message: No updates were applied. 

Press SPACE key to continue. CMD window will be closed then. 

6- Goto the service panel and start DRM Web services on all the servers. 

7- Check DRM Web services status in DRM Configuration console. Both DRM Web services are up and running fine. Also notice, our DRM application too has been successfully started this time.

8- When I opened DRM web-client, my DRM application was available to login.

9- And application login too was working as expected.

So for me, the issue got fixed following the 3rd workaround. :-)

We know that Applying Updates to Oracle Data Relationship Management (DRM) application refreshes the database connection apart from making any changes like patch upgrade, config changes, etc. in your DRM application, effective. 

But exactly what has caused this issue is still not known. This workaround has fixed this issue for me in one go so it can be used as a temporary fix.

Please note, this DRM error "There are currently no applications available for login" can occur due to many reasons, and above mentioned workarounds reflect only a few of those scenarios. 

You need to check the Windows Event Viewer on your DRM server in order to identify the exact root cause of the problem as Event Viewer will most likely have an error message recorded related to your DRM issue. 

That's all for this post.

I hope this article has helped you. 

Your suggestions/feedback are most welcome.

Keep learning and Have a great day!!!

Read More