Store and Read blob data from user update history using External jar

[Darshanjain]

1.Requirement

We have a requirement to publish a report for all the user updates for the day. This should include all the attributes that have been updated (old + new values) along with other user attributes. We try to read the blob data from the userhistroy table through sql query we are unable to convert blob data to normal state like user readable format.

2.Solution

We came with an approach to use custom jar for achieving the above requirement. custom jar used to update store and read blob data from user history. We can then run analytics query against the converted data to provide required reports Below is flow of the custom jar and various objects/comments involved in the solution.

3. Applicable Version(s)

Only 3.x 

For EIC you can follow the below doc ( https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter17-EIC-Analytics/Managing-An... )

4.Solution Steps 

     A).Custom Jar Deployment

Perform the following steps to configure the external JAR job which will fetch the user update history data, convert the blob to text & store it to temp table.

     B).Pre-requisites

 After reading the blob data, need to put that data in to one table to see the data in readable format so we have created this table.

• Create the following temp table.

CREATE TABLE `usershistory_plain` (

  `USERKEY` bigint(20) NOT NULL,

  `USERNAME` varchar(255) DEFAULT NULL,

  `DATA` TEXT DEFAULT NULL,

  `UPDATEDATE` datetime NOT NULL,

  `UPDATEDBY` varchar(255) NOT NULL,

  INDEX (`USERKEY`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• Make the following entries in externalconfig.properties file (as configured in your environment):

DB_URL = jdbc:mysql://xxx.x.x.x:xxxx/saviynt_dev_db(As per your DB URL)

DB_USER = root(As Per your DB)

DB_PASSWORD = xxxxxxxxxxxxxxxxx

JDBC_DRIVER = com.mysql.jdbc.Driver

• The password needs to be encrypted using the custom jar same as attached the article 
• Place the Jar file in ECM/WEB-INF/lib folder same as attached the article

NOTE: 

Please reach out to your TAM, CPS team if you want to get details regarding building this code.

C).External Jar job configuration

Go to Admin -> Job Control Panel -> External jar & create a Trigger with following:

 

 

Class name with package: com.saviynt.blob.readBlob

Method Name: blobToText

Arguments: {"username":"7304735","numberofdays":"10","startdate":"2018-08-20","enddate":"2018-08-28"}

D).Possible values for arguments:

Argument	Mandatory	Values
Username	Yes	Individual username or “all”
numberofdays (from current day)	Yes	Any integer
Startdate	No	Any date in yyyy-mm-dd or “”
Enddate	No	Any date in yyyy-mm-dd or “”

F).Usage

The parameters can be passed in different ways to get different results:

1. Username & numberof days:

{"username":"7304735","numberofdays":"60","startdate":"","enddate":""}

1. All users’ history & numberofdays:

{"username":"all","numberofdays":"10","startdate":"","enddate":""}

1. Username & date range (numberofdays has to be 0 when giving date range:

{"username":"7304735","numberofdays":"0","startdate":"2018-08-20","enddate":"2018-08-28"}

1. All users and date range (numberofdays has to be 0 when giving date range):

{"username":"all","numberofdays":"0","startdate":"2018-08-20","enddate":"2018-08-28"}

G).Parameter restrictions

Due to the possibility of updatehistory containing large amount of data & attempting to convert and store it to temp table might result in performance issue on DB there is the following check in place:

If username=all then numberofdays should be = 1

If username=all and numberofdays =0 then the days difference between startdate and enddate should be =1

Recommended to retrieve small amounts of data at a time.

The usershistory_plain table is truncated after every run on the job.

[RajeshA]

Hello @Darshanjain 

Thank you for taking time and posting this valuable information.

Before I reach out to TAM and fresh desk for making this changes in database can you let us know if this approach is supported for v5.5sp3.20

I didn't quite get what this 3.x means

[Darshanjain]

Hi @RajeshA 

It is supported in 3.20 however i would advice against it as this is no longer supported in EIC versions. ( All customers will be moved to EIC soon )

From EIC you can create a  analytics report to get this data decoded which i have already provided you the link.

Thanks

Darshan

[rushikeshvartak]

This post should to be hidden as its misleading and not long term solution 

[Darshanjain]

Hi @rushikeshvartak 

This post is mentioned specially for 5.5 versions as that is only the option. As long as we are supporting 5.5versions we will keep this and then we will remove this.

For EIC versions how it needs to be handled as been already mentioned.

Thanks

Darshan