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

Fetch NEWUSERDATA and count occurences in ModifyJson

sdey_2023
Regular Contributor
Regular Contributor

Hi team,

We are storing the concat of firstname and lastname in CP41 for all users. We have a requirement to update the count of the occurrences of same firstname, lastname in CP42 across all the User database of Saviynt, we are using the below query in the ModifyJson:

"update NEWUSERDATA as NU set NU.CUSTOMPROPERTY42 = select count(customproperty41)+1 from currentusers u1 where u1.customproperty41 like concat(NU.firstname,'.',NU.lastname)"

But the issue here is that if the same firstname and lastname user comes as part of same import, it is unable to correctly store the count as the above query is only considering the currentusers which was already imported.

So if as part of 1st import one user named John Doe comes and then in next import same named user comes, it is able to correctly store the count. But if as part of 1st import itself two users of name John Doe comes it is not able to store the count correctly.

Could you please advise on the query to be used so that we can fetch the current import users as well.

7 REPLIES 7

NM
Honored Contributor II
Honored Contributor II

Hi @sdey_2023 , try this

"update NEWUSERDATA as NU set NU.CUSTOMPROPERTY42 = select count(u1customproperty41)+count(NU.customproperty41)+1 from currentusers u1 where u1.customproperty41 like concat(NU.firstname,'.',NU.lastname)"

Raghu
All-Star
All-Star

@sdey_2023  try below

UPDATE NEWUSERDATA NU
SET NU.CUSTOMPROPERTY42 = (
SELECT COUNT(u1.customproperty41) + 1
FROM currentusers u1
WHERE u1.customproperty41 LIKE CONCAT(NU.firstname, '.', NU.lastname)
)


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

sdey_2023
Regular Contributor
Regular Contributor

Hi Raghu,

This is not considering the NEWUSERDATA users which are part of the current import but only the already existing users via the currentusers table.

 

@sdey_2023  try

UPDATE NEWUSERDATA NU
SET NU.CUSTOMPROPERTY42 = (
SELECT COUNT(u1.customproperty41) + 1
FROM currentusers u1
WHERE u1.customproperty41 LIKE CONCAT(NU.firstname, '.', NU.lastname)
)
WHERE NU.USERNAME IN (
SELECT NU2.USERNAME
FROM NEWUSERDATA NU2
LEFT JOIN currentusers u2 ON u2.customproperty41 LIKE CONCAT(NU2.firstname, '.', NU2.lastname)
WHERE u2.USERNAME IS NULL)


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

sdey_2023
Regular Contributor
Regular Contributor

Hi Raghu, Thanks for your efforts but unfortunately this also does not work.

@sdey_2023 

UPDATE NEWUSERDATA NU
SET NU.CUSTOMPROPERTY42 = (
    SELECT COUNT(*) + 1
    FROM currentusers u1
    WHERE u1.customproperty41 = CONCAT(NU.firstname, '.', NU.lastname)
)


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

NM
Honored Contributor II
Honored Contributor II

@sdey_2023 , did you got a chance to try this?

"update NEWUSERDATA as NU set NU.CUSTOMPROPERTY42 = select count(u1customproperty41)+count(NU.customproperty41)+1 from currentusers u1 where u1.customproperty41 like concat(NU.firstname,'.',NU.lastname)"