Dataset Query Validation

[aarthianand93]

Hi Team,

We have a pre-processor query using Datasets. The syntax of the query is correct however it is not working. The objective is populate country based on the customproperty7 value. 

Below is the query: 

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, customproperty7 FROM USERS",
"DATASET_VALUES": "SELECT attribute1, attribute2, DATASETNAME FROM DATASET_VALUES"
},
"COMPUTEDCOLUMNS": ["country"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET country = (SELECT DV.attribute2 FROM DATASET_VALUES DV, USERS U WHERE DV.attribute1 = U.customproperty7 AND DV.DATASETNAME = 'REPLACE_MAPPING')"
]
}

Taken from forum post: 

https://forums.saviynt.com/t5/identity-governance/pre-processor-does-not-work-while-creating-a-user-...

The query is mentioned to be working but we are facing issues. Please let us know if the query needs any other modifications. 

Regards,

Aarthi Anand

[aarthianand93]

Hi Team, 

We tried a different query as well: (however, the individual select queries are working in data analyzer but the attribute is not getting populated.)

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, customproperty7 FROM USERS",
"DATASET_VALUES": "SELECT attribute1, attribute2 FROM DATASET_VALUES WHERE DATASETNAME = 'REPLACE_MAPPING'"
},
"COMPUTEDCOLUMNS": ["country"],
"PREPROCESSQUERIES": [
"UPDATE USERS SET country = (SELECT DV.attribute2 FROM DATASET_VALUES DV JOIN USERS U ON DV.attribute1 = U.customproperty7)"
]
}

[rushikeshvartak]

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, customproperty7 FROM USERS",
"DATASET_VALUES": "SELECT attribute1, attribute2 FROM DATASET_VALUES WHERE DATASETNAME = 'REPLACE_MAPPING'"
},
"COMPUTEDCOLUMNS": ["country"],
"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA SET country = (SELECT DV.attribute2 FROM DATASET_VALUES DV JOIN CURRENTUSERS  U ON DV.attribute1 = U.customproperty7)"
]
}

Refer https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter03-User-Management/User-Impo... 

[aarthianand93]

Hi @rushikeshvartak,

I tried with the above query you have provided, we upload a csv and place the query in the pre-processor query field. However, the user is not inserted into Saviynt. In the logs we see an error similar to this.

Error: https://forums.saviynt.com/t5/identity-governance/preprocessor-error-quot-table-ssminlp-currentusers...

I tried using the exact code you have provided in the above link and modified it slightly based on the attributes I am using, also without space or new line. But still the user is not inserted nor is the attribute populated. 

Please help on this. 

Regards,

Aarthi Anand

[rushikeshvartak]

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, customproperty7 FROM USERS",
"DATASET_VALUES": "SELECT attribute1, attribute2 FROM DATASET_VALUES WHERE DATASETNAME = 'REPLACE_MAPPING'"
},
"COMPUTEDCOLUMNS": ["country"],
"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA SET country = (SELECT DV.attribute2 FROM CURRENTDATASET_VALUES DV JOIN CURRENTUSERS  U ON DV.attribute1 = U.customproperty7)"
]
}

[aarthianand93]

Hi @rushikeshvartak,

I tried with your query, however, there is a subquery returns more than 1 row error. 

I checked a couple of forums posts and modified the query with DISTINCT but it is not working. The thing is when I run the subquery in Data Analyzer it is not returning results. 

Please assist. Thank you. 

Regards,

Aarthi Anand

[rushikeshvartak]

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, customproperty7 FROM USERS",
"DATASET_VALUES": "SELECT attribute1, attribute2 FROM DATASET_VALUES WHERE DATASETNAME = 'REPLACE_MAPPING'"
},
"COMPUTEDCOLUMNS": ["country"],
"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA NU
JOIN CURRENTUSERS CU ON NU.customproperty7 = CU.customproperty7
JOIN CURRENTDATASET_VALUES DV ON DV.attribute1 = CU.customproperty7
SET NU.country = DV.attribute2"
]
}

I am unsure about common attribute hence update logic as per your requirement

[aarthianand93]

Hi @rushikeshvartak,

We are using the same attributes as mentioned and just testing this dataset feature to populate country attribute based on the customproperty7. For example if customproperty7 is IND, then country attribute should be populated as India. We have added the attribute_key and attribute_value in the REPLACE_MAPPING dataset. However, this query is not working. 

When we upload the csv there is no specific error in log viewer, but the user is not getting inserted into Saviynt. 

Please let us know what could be the possible reasons for this and how we can modify this query.

Regards,

Aarthi Anand

[rushikeshvartak]

Does query works in data analyzer?

[itinjic]

The pre-processor query provided seems to have a syntax error in the subquery of the UPDATE statement. The subquery lacks the JOIN condition between the DATASET_VALUES table and the USERS table.

To fix this issue, you can modify the query as follows:

JSON
```
{ "ADDITIONALTABLES": { "USERS": "SELECT username, customproperty7 FROM USERS", "DATASET_VALUES": "SELECT attribute1, attribute2 FROM DATASET_VALUES WHERE DATASETNAME = 'REPLACE_MAPPING'" }, "COMPUTEDCOLUMNS": ["country"], "PREPROCESSQUERIES": [ "UPDATE NEWUSERDATA SET country = (SELECT DV.attribute2 FROM DATASET_VALUES DV, USERS U WHERE DV.attribute1 = U.customproperty7 AND DV.DATASETNAME = 'REPLACE_MAPPING' AND NEWUSERDATA.username = U.username)" ] }
```

Here's what has been changed:

1. The DATASET_VALUES table is now directly referenced in the ADDITIONALTABLES section without including the DATASETNAME column.

2. The subquery in the UPDATE statement now performs the join between the DATASET_VALUES and USERS tables using the username column.

Make sure to replace "REPLACE_MAPPING" with the actual name of your dataset. Also, verify that the column names and table names match your environment. This modified query should help populate the country column based on the customproperty7 value.

[Shubhamjain27]

Hi @aarthianand93  were you able to resolve this?