Delphix Products

Expand all | Collapse all

Getting wrong value for the same name using secure lookup algorithm

Jump to Best Answer

Rajesh -03-07-2018 11:56:00 AM

  • 1.  Getting wrong value for the same name using secure lookup algorithm

    Posted 03-07-2018 07:48:00 AM
    Hello,

    We have two columns in DB
    first name and display namem first name is in small case and display name is in Upper case when we applying the same algorithm on both column different values are getting populated how we can generate the same name for both fields?


    Example;
    firstname= marc venu
    Displayname is = MARC VENU

    apply same algorithm domain is first name and algorithm is FULL NAME SL
    run masking job 
    Check first name and display name after masking job completion
    first name= getu hans
    Display name = marcin jhon

    Expected that we need is:

    first name= getu hans
    Display name = GETU HANS

    Please let me know the solution



    #Masking


  • 2.  RE: Getting wrong value for the same name using secure lookup algorithm
    Best Answer

    Posted 03-07-2018 07:59:00 AM
    Hi Pankay,

    you can use a custom SQL in your Ruleset in order to use UPPER sql function to your column.

    Best Regards.
    Gianpiero


  • 3.  RE: Getting wrong value for the same name using secure lookup algorithm

    Posted 03-07-2018 08:20:00 AM
    Thanks a lot for your replay ,Could you please give me the steps how we can do it using delphix ...
    We have tried but may be doing something wrong m it will be great help if you share steps 


  • 4.  RE: Getting wrong value for the same name using secure lookup algorithm
    Best Answer



  • 5.  RE: Getting wrong value for the same name using secure lookup algorithm

    Posted 03-07-2018 09:36:00 AM
    i want to make the column name in uppercase  hence i  have written in custom sql 

    select upper(col_name) from table name 

    Getting error after running the masking job 


  • 6.  RE: Getting wrong value for the same name using secure lookup algorithm

    Posted 03-07-2018 09:47:00 AM
    Do not rewrite the custom SQL. Please, push the delete button and then paste here the custom sql. I'll try to modify it without rewrite it entirely.
    Thanks.
    Gianpiero


  • 7.  RE: Getting wrong value for the same name using secure lookup algorithm

    Posted 03-07-2018 09:56:00 AM
    Could you please suggest me what exactly i have to write ?
    to make the column name in uppercase or to perfrom operation on any specific column please let us know we are blocked 


  • 8.  RE: Getting wrong value for the same name using secure lookup algorithm
    Best Answer

    Posted 03-07-2018 10:19:00 AM
    Without original custom sql, it's difficult for me. I suppose you're using Oracle.
    Let's try:
    Try to delete your custom sql clicking the delete button.
    Go back to the custom sql tab to retrieve the original sql.
    Wrap only the column "firstname" with upper function and give it back the original name with alias "AS":
    select rowid, "C1","C2",UPPER("C3") AS "C3" FROM ....


  • 9.  RE: Getting wrong value for the same name using secure lookup algorithm

    Posted 03-07-2018 11:00:00 AM
    Steps :
    1. create env and application
    2. Create connection
    3. Create profiler and Rule set 
    4. Run profiler and assign the requird algorithm
    5. Go to Rule Edit > Set add filter > Give any name > save it 
    6. Click on Edit pencil icon on rule set screen
    7. Click on custom SQL radio button and add below query in this 
     SELECT Upper(BO_NAME_DISP_UPPER) AS test, PREF_FIRST_NAME, FROM TEST_Table

    8. Run masking Job for that table
    Job is getting failed and nothing is happen  
    Now please tell me the solution 


  • 10.  RE: Getting wrong value for the same name using secure lookup algorithm

    Posted 03-07-2018 11:05:00 AM
    This is the error m getting :
    Mar 07, 2018 12:03:23 PM com.dmsuite.common.utils.JobExecutionHelper executeJob
    INFO: Job started at Wed Mar 07 12:03:23 UTC 2018.
    Mar 07, 2018 12:03:23 PM com.dmsuite.dmsApplicator.masking.XMLGenerator executeMarshalling
    INFO: Generate request xml started successfully.
    Mar 07, 2018 12:03:23 PM com.dmsuite.dmsApplicator.masking.XMLGenerator executeMarshalling
    INFO: Generate Request xml done successfully.
    Mar 07, 2018 12:03:23 PM com.dmsuite.dmsApplicator.masking.transformation.MaskingMarshalling createKettleXML
    INFO: Generate Transformation XML started successfully
    Mar 07, 2018 12:03:24 PM com.dmsuite.dmsApplicator.masking.transformation.MaskingMarshalling createKettleXML
    INFO: Generate Transformation XML done successfully
    Mar 07, 2018 12:03:24 PM com.dmsuite.dmsApplicator.job.transformation.JobXMLGenerator createMaskingKettleJobXML
    INFO: Generate job xml started successfully.
    Mar 07, 2018 12:03:24 PM com.dmsuite.dmsApplicator.job.transformation.JobXMLGenerator createMaskingKettleJobXML
    INFO: Generate job xml done successfully.
    Mar 07, 2018 12:03:29 PM com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl createMaskingResponseXML
    INFO: Job Status : FAIL. 
    Mar 07, 2018 12:03:29 PM com.dmsuite.dmsApplicator.masking.MaskResponseXMLUnmarshaller saveExecution
    INFO: save execution started successfully.
    Mar 07, 2018 12:03:29 PM com.dmsuite.dmsApplicator.masking.MaskResponseXMLUnmarshaller saveExecution
    INFO: save execution done successfully.
    Mar 07, 2018 12:03:29 PM com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl run
    INFO: Job Ended at Wed Mar 07 12:03:29 UTC 2018.



  • 11.  RE: Getting wrong value for the same name using secure lookup algorithm
    Best Answer

    Posted 03-07-2018 11:08:00 AM
    Hi Pankaj,

    the query seems to be wrong:
    SELECT Upper(BO_NAME_DISP_UPPER) AS test, PREF_FIRST_NAME, FROM TEST_Table

    there is a comma between the last column name and FROM keywork.
    You changed the name of the originl column with the alias AS test, you should have called AS BO_NAME_DISP_UPPER

    Please, leave the original query unchanged (delete the current modified by you) and paste here.


  • 12.  RE: Getting wrong value for the same name using secure lookup algorithm

    Posted 03-07-2018 11:22:00 AM
    SELECT ROWID, "BO_NAME_DISP_UPPER" , "PREF_FIRST_NAME" , "NAME_TYPE" , "BO_ID" , "BO_NAME_SHORT" , "LAST_NAME" , "FIRST_NAME" , "NAME_ROYAL_SUFFIX" , "BGC_PHONETIC" , "SYNCDTTM" , "PRIMARY_IND" , "NAME_INITIALS" , "ROW_LASTMANT_DTTM" , "NAME_ROYAL_PREFIX" , "MIDDLE_NAME" , "SYNCID" , "SEQ_NBR" , "ROW_LASTMANT_OPRID" , "ROW_ADDED_DTTM" , "NAME_TITLE" , "SOURCE_IND" , "SECOND_LAST_SRCH" , "LAST_NAME_SRCH" , "ROW_ADDED_OPRID" , "SECOND_LAST_NAME" , "BGC_PHONETIC_SRCH" , "COUNTRY_NM_FORMAT" , "BO_NAME_AC" , "NAME" , "NAME_PREFIX" , "FIRST_NAME_SRCH" , "NAME_AC" , "BO_NAME_DISPLAY" , "NAME_SUFFIX" , "BO_NAME" ,upper( BO_NAME_UPPER) as "BO_NAME_UPPER" FROM "CII_SCHE"."TEST_BCI"





    Job is getting succeeded but when queering the table not able to see the  BO_NAME_UPPER in upper case

    Aslo we have Firstname SL masking algorithm for this column how to do it 


  • 13.  RE: Getting wrong value for the same name using secure lookup algorithm

    Posted 03-07-2018 11:41:00 AM
    Could you please give me any suggestion please 


  • 14.  RE: Getting wrong value for the same name using secure lookup algorithm

    Posted 03-07-2018 11:56:00 AM
    Could you please help me on this 


  • 15.  RE: Getting wrong value for the same name using secure lookup algorithm
    Best Answer

    Posted 03-07-2018 12:22:00 PM
    There are two problems:

    ***INPUT VALUE PROBLEM***
    how to assure that two input values that differ only for upper/lower case generate the same output value? "Mario" -> "Sergio" and "MARIO" -> "Sergio" ?

    Below the solution:

    All case different columns are to be upperized. Below I suppose that FIRST_NAME can contain lower case characters and so we are going to upperize it:

    SELECT ROWID, "BO_NAME_DISP_UPPER" , "PREF_FIRST_NAME" , "NAME_TYPE" , "BO_ID" , "BO_NAME_SHORT" , "LAST_NAME" , UPPER("FIRST_NAME") AS "FIRST_NAME", "NAME_ROYAL_SUFFIX" , "BGC_PHONETIC" , "SYNCDTTM" , "PRIMARY_IND" , "NAME_INITIALS" , "ROW_LASTMANT_DTTM" , "NAME_ROYAL_PREFIX" , "MIDDLE_NAME" , "SYNCID" , "SEQ_NBR" , "ROW_LASTMANT_OPRID" , "ROW_ADDED_DTTM" , "NAME_TITLE" , "SOURCE_IND" , "SECOND_LAST_SRCH" , "LAST_NAME_SRCH" , "ROW_ADDED_OPRID" , "SECOND_LAST_NAME" , "BGC_PHONETIC_SRCH" , "COUNTRY_NM_FORMAT" , "BO_NAME_AC" , "NAME" , "NAME_PREFIX" , "FIRST_NAME_SRCH" , "NAME_AC" , "BO_NAME_DISPLAY" , "NAME_SUFFIX" , "BO_NAME" ,upper( BO_NAME_UPPER) as "BO_NAME_UPPER" FROM "CII_SCHE"."TEST_BCI"

    ***OUTPUT VALUE PROBLEM***

    How to get output value always in uppercase form?
    You have to create a SL Algorithm uploading a txt file with all charcters in upper case.
    If you need in some cases upper case output and other cases lower case output, you can simply build two SL Algorithms with the same row order and values but one file with lower case chars and the other one with upper case chars.

    ----------

    I hope that I undestood your problems. If I got them, this surely can solve them.

    Best Regards.
    Gianpiero


  • 16.  RE: Getting wrong value for the same name using secure lookup algorithm
    Best Answer

    Posted 03-09-2018 05:56:00 AM
    Hi Pankaj, If I got your case right you have first name column with data in it and want to get the same masked value on this column to be propagated to displayname but in upper case. One way for achieving that is relying on post-hooks on masking job; Use algorithm to mask first name (all lower case) and call a post-script that updates displayname column with the masked value of firstname (using upper clause) to get values in upper case Hope this answer your need. Regards, Mouhssine