AnsweredAssumed Answered

CSV Entitlement Data Collection

Question asked by shakti nayak on Feb 14, 2018
Latest reply on Mar 5, 2018 by Pradeep Kadambar

Like • Show 0 Likes0
Comment • 8

We have an user-entitlement relationship csv file for an application.

This file has two columns userid and accessdata where "accessdata" is a multivalued list of entitlements separated by comma.

Ex- userid,accessdata

"abc","ent1,ent2,ent3"

"xyz","ent4,ent2"

We want to parse this data on collection to represent the below one to one user-entitlement relationship

for ex- userid,accessdata

"abc","ent1"

"abc","ent2"

"abc","ent3"

"xyz","ent4" and so on.

But we are unable to do so. The same can be parsed easily if it is a oracle, mysql or ms sql db table/view.

But for csv the parsing fails everytime.

Any suggestions.

1 person has this question

Outcomes

Jason Shepard Feb 14, 2018 1:53 PM

I asked a similar question a little while back and was given some suggestions to review but no one has said if it is possible with G&L.

Actions

Jason Shepard Feb 14, 2018 1:54 PM

Here was my thread. SQL Query EDC

Actions

Boris Lekumovich

Feb 15, 2018 4:55 AM

The csv collector is using a hxtt driver

To test the capabilities and limitations of the driver, you can use Squirrel

RSA Identity G&L - Using SQL Squirrel v4.docx

How do I setup Squirrel SQL?

Check my reply to a similar post - https://community.rsa.com/thread/189762#comment-884570

Actions

Mostafa Helmy

Feb 15, 2018 7:01 AM

It's possible using GROUP_CONCAT function in HXTT (ref: HXTT Docs ). Here's an example:

Input file:

User,Value
u1,v1
u1,v2
u2,v2
u2,v3

Query:

select User, GROUP_CONCAT(Value SEPARATOR ',') as Value from test group by user

Output:

User,Value
"u1","v1,v2"
"u2","v2,v3"

Actions

Boris Lekumovich

@ Mostafa Helmy on Feb 15, 2018 7:47 AM

I think the requirement is the other way around

Input file:

User,Value
"u1","v1,v2"
"u2","v2,v3"

Output:

User,Value
u1,v1
u1,v2
u2,v2
u2,v3

Actions

Mostafa Helmy

@ Mostafa Helmy on Feb 15, 2018 8:38 AM

Sorry my mistake ..

Actions

Mostafa Helmy

@ Mostafa Helmy on Mar 5, 2018 7:41 PM

This seems to be a new feature just added to the latest version of the HXTT driver (reference HXTT Syntaxt - Split Multivalue Column Into Rows). Had this been included in the product, it would basically work like this.

Input file.csv:

userid,accessdata

"abc","ent1,ent2,ent3"

"xyz","ent4,ent2"

Query:

select file.userid, tmp.accessdata from file, (select split(file.accessdata,',') as accessdata) tmp

Output:

userid,accessdata

"abc","ent1"

"abc","ent2"

"abc","ent3"

"xyz","ent4"

"xyz","ent2"

You should either open a support case to request adding the latest version of HXTT to RSA Identity Governance & Lifecycle. Or you can directly submit your idea to the new RSA Ideas for RSA Identity Governance & Lifecycle space.

Actions

Pradeep Kadambar

@ Mostafa Helmy on Mar 5, 2018 8:25 PM

I have opened an idea for supporting SFTP which essentially involves using the latest HXTT driver.

Upgrade CSV Driver : Support CSV collections from SFTP

Actions

8 Replies

Jason Shepard Feb 14, 2018 1:53 PM
Mark Correct
Correct Answer
I asked a similar question a little while back and was given some suggestions to review but no one has said if it is possible with G&L.
Like • Show 0 Likes0
Actions
Jason Shepard Feb 14, 2018 1:54 PM
Mark Correct
Correct Answer
Here was my thread. SQL Query EDC
Like • Show 0 Likes0
Actions
Boris Lekumovich Feb 15, 2018 4:55 AM
Mark Correct
Correct Answer
The csv collector is using a hxtt driver
To test the capabilities and limitations of the driver, you can use Squirrel
RSA Identity G&L - Using SQL Squirrel v4.docx
How do I setup Squirrel SQL?

Check my reply to a similar post - https://community.rsa.com/thread/189762#comment-884570
Like • Show 0 Likes0
Actions
Mostafa Helmy Feb 15, 2018 7:01 AM
Mark Correct
Correct Answer
It's possible using GROUP_CONCAT function in HXTT (ref: HXTT Docs ). Here's an example:

Input file:
```
User,Value
u1,v1
u1,v2
u2,v2
u2,v3
```
Query:
```
select User, GROUP_CONCAT(Value SEPARATOR ',') as Value from test group by user
```
Output:
```
User,Value
"u1","v1,v2"
"u2","v2,v3"
```
Like • Show 1 Like1
Actions
- Boris Lekumovich @ Mostafa Helmy on Feb 15, 2018 7:47 AM
  Mark Correct
  Correct Answer
  I think the requirement is the other way around
  
  Input file:
  User,Value
  "u1","v1,v2"
  "u2","v2,v3"
  Output:
  User,Value
  u1,v1
  u1,v2
  u2,v2
  u2,v3
  Like • Show 0 Likes0
  Actions
- Mostafa Helmy @ Mostafa Helmy on Feb 15, 2018 8:38 AM
  Mark Correct
  Correct Answer
  Sorry my mistake ..
  Like • Show 0 Likes0
  Actions
- Mostafa Helmy @ Mostafa Helmy on Mar 5, 2018 7:41 PM
  Mark Correct
  Correct Answer
  This seems to be a new feature just added to the latest version of the HXTT driver (reference HXTT Syntaxt - Split Multivalue Column Into Rows). Had this been included in the product, it would basically work like this.
  
  Input file.csv:
  userid,accessdata
  "abc","ent1,ent2,ent3"
  "xyz","ent4,ent2"
  
  Query:
  select file.userid, tmp.accessdata from file, (select split(file.accessdata,',') as accessdata) tmp
  
  Output:
  userid,accessdata
  "abc","ent1"
  "abc","ent2"
  "abc","ent3"
  "xyz","ent4"
  "xyz","ent2"
  
  You should either open a support case to request adding the latest version of HXTT to RSA Identity Governance & Lifecycle. Or you can directly submit your idea to the new RSA Ideas for RSA Identity Governance & Lifecycle space.
  Like • Show 1 Like1
  Actions
  - Pradeep Kadambar @ Mostafa Helmy on Mar 5, 2018 8:25 PM
    Mark Correct
    Correct Answer
    I have opened an idea for supporting SFTP which essentially involves using the latest HXTT driver.
    
    Upgrade CSV Driver : Support CSV collections from SFTP
    Like • Show 2 Likes2
    Actions

CSV Entitlement Data Collection

Outcomes

Related Content

Recommended Content