Saviynt and MYSQL string functions with REGEXP_SUBSTR()

[Volker]

Hello everyone,

I would like to know if there is any way to being able to use REGEXP_SUBSTR() and this kind of string functions in saviynt. 

Need to interpret an complex string for account matching, which becomes easier with regexp options available.

{value=2222daf4-2222b-222d-a222-cd2fe222d222, type=custom, customType=}, {value=E123456, type=organization}

For matching the account I need to have the "E12345" value extracted.

Regards 
Volker

[AmitM]

@Volker , have you tried it in analytics ? I have used reges and substring separately and those works.

Not sure on REGEX_SUBSTR(). If it doesn't work you can achieve above via case, regex and substring combo.

Thanks,

Amit 

[Volker]

@AmitM , nope it is not supported in analytics as well. Have now worked around the question and split it into 2 queries, as I can not ensure that the order in the json will stay stable:

lower(concat('abcdef','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(SUBSTRING_INDEX(accounts.customproperty27,'value=',-1),', type=organization}',1))#lower(concat('abcdef','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(SUBSTRING_INDEX(accounts.customproperty27,'value=',-2),', type=organization}',1))

This statement works in data analyzer, and extracts me the wanted information "123456" from the provided JSON String.

Unfortunately even if the result should match the account adoption does not work so far.

[rushikeshvartak]

Can you explain requirement with example and input data

[Volker]

Hi @rushikeshvartak ,

please excuse the late response. 

Here the situation and what should be archived 

For the account with E123456 all is working. On the other hand the account admins123456 gets not adopted to the user E123456.

The full adoption rule looks like the following:

lower(username)=lower(accounts.customproperty27)#lower(customproperty1)=lower(accounts.customproperty29)#lower(email)=lower(accounts.customproperty29)#lower(concat('admin','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(accounts.customproperty29 ,'@',1))#lower(concat('admin','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(SUBSTRING_INDEX(accounts.customproperty27,'value=',-1),', type=organization}',1))#lower(concat('admin','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(SUBSTRING_INDEX(accounts.customproperty27,'value=',-2),', type=organization}',1))

I walked trough each single SQL query separately in the data analyzer and found that for the admins123456 account two matches will happen, but they do not result in an adoption. 

Here my steps

• Rule part to be  checked:
  

  lower(concat('admin','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(accounts.customproperty29 ,'@',1))

• User object side of rule 4:
  • User with attribute username='E123456' was used for this step 
  • lower(concat('admin','s',substring(lower(username),2)))
  • The resolution will start from the innermost part
    
    • lower(username)
      
      • lower('E123456')
      • 'e123456'
        
        
    • substring(lower(username),2)
      • substring('e123456',2)
      • '123456'
        
        
    • concat('admin','s',substring(lower(username),2))
      • concat('admin','s','123456')
      • 'admins123456'
        
        
  • Final result: admins123456
    
    

• Account side of the rule 4:
  • running it for account with name='admins123456'
  • accounts.customproperty29
  • 'admins123456'@**********.com' 
    
    
  • lower(SUBSTRING_INDEX(accounts.customproperty29 ,'@',1))
    • SUBSTRING_INDEX(accounts.customproperty29 ,'@',1)
      • SUBSTRING_INDEX('admins117625@********.com','@',1)
      • 'admins123456''
        
        
  • Final result: admins123456

• Final Result of part 4 of correlation query is:
  • 'admins123456' = user side = account side = 'admins123456'' 

From my understanding it is an match of that account to the specific user and should result in an successful adoption.

Do I miss something?

Regards
Volker

[This message has been edited by moderator to mask email]