Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon
No ratings
Darshanjain
Saviynt Employee
Saviynt Employee

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:

1c-spH_ud3tYlq5zeSrwXx6ScL2PvLN6pA.png

dHBBhrYadbq9NHtMQ8ZeXI4TRsVDy2d6ew.png

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.

Comments
RajeshA
Regular Contributor
Regular Contributor

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

RajeshA_0-1694468256129.png

 

Darshanjain
Saviynt Employee
Saviynt Employee

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
All-Star
All-Star

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

Darshanjain
Saviynt Employee
Saviynt Employee

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

Version history
Last update:
‎04/04/2024 07:11 AM
Updated by: