Delphix Products

Expand all | Collapse all

Exclude identifiable customer numbers from masking

Gianpiero Piccolo

Gianpiero Piccolo11-17-2017 01:02:00 PMBest Answer

Renee Greenley

Renee Greenley11-17-2017 01:49:00 PMBest Answer

  • 1.  Exclude identifiable customer numbers from masking

    Posted 11-17-2017 12:56:00 PM
    We have a customer ID field with customer numbers that start with M, but older customers have a social security number instead. We want to mask the ones that are social security numbers but leave the ones starting with an M alone. Is there a way to do that?

    Renee
    #Masking


  • 2.  RE: Exclude identifiable customer numbers from masking
    Best Answer

    Posted 11-17-2017 01:00:00 PM
    Hi Renee,

    sure, you can! 
    In the Ruleset page, enter in edit page and then click on edit table icon. In the popup, select "filter" tab and add your where clause in order to select only record with ID value starts with 'M%' (without WHERE word).
    Regards.
    Gianpiero


  • 3.  RE: Exclude identifiable customer numbers from masking
    Best Answer

    Posted 11-17-2017 01:01:00 PM
    Yes! Just use a filter in the Ruleset for that table: something like 'CUSTOMERID NOT LIKE 'M%'


  • 4.  RE: Exclude identifiable customer numbers from masking
    Best Answer

    Posted 11-17-2017 01:02:00 PM
    without WHERE word: "CUSTOMERID  NOT LIKE 'M%'"



  • 5.  RE: Exclude identifiable customer numbers from masking
    Best Answer

    Posted 11-17-2017 01:49:00 PM
    Worked like a charm! Thank you both so much!

    Renee


  • 6.  RE: Exclude identifiable customer numbers from masking

    Posted 02-20-2018 11:05:00 PM
    How would you write the filter if you had 2 fields in the same table that need the filter? For example, the table has a primary ID field and also a spouse ID field, and both need to be left alone if they start with an 'M%'. I have the filter statement saying: Primary_ID not like 'M%' but not sure how to add the second field to that statement. Not all of the records have this second 'spouse' field, and some records have a SSN in primary Id and an 'M%' in spouse ID (or vice versa)., so using an 'AND spouse_ID not like 'M%' won't work. Any suggestions?

    Thank you,
    Renee


  • 7.  RE: Exclude identifiable customer numbers from masking

    Posted 02-21-2018 06:25:00 AM
    Hi, Just change the « AND » in your where condition with « OR ». This because you want records that sweet on or the other condition not both at the same time (if I got it right). Regards Mouhssine


  • 8.  RE: Exclude identifiable customer numbers from masking

    Posted 02-21-2018 12:09:00 PM
    Thank you Mouhssine, but it seems like using the OR would allow the masking to change people's M#s if their spouse had an SSN, wouldn't it? For example:

    Primary_ID = M1234567
    spouse_ID =  333-22-4444

    If my filter says <<Primary_ID not like 'M%'  OR Spouse_ID not like 'M%' >> wouldn't the masking logic decide it was okay to mask both of these fields in the record since the filter was true (for the spouse)? Or does the masking know which field was true and which was not true in the filter? Seems like I need a filter that I can enter by field instead of by table. 

    Any thoughts? I may not be understanding the filters correctly.
    Renee


  • 9.  RE: Exclude identifiable customer numbers from masking

    Posted 02-21-2018 12:27:00 PM

    Hi Renee, I this situation you could just mask the table using two separate masking jobs - one job to mask each field.

    Mike



  • 10.  RE: Exclude identifiable customer numbers from masking

    Posted 02-21-2018 12:27:00 PM

    Hi Reene,

    Can you please explain with a sample example covering all cases your need, I'm afraid didn't get your need correctly.

    Regards,

    Mouhssine



  • 11.  RE: Exclude identifiable customer numbers from masking

    Posted 02-21-2018 06:32:00 PM
    Mike, I think that would work, although pretty messy when I have many instances of this happening, but thank you for the suggestion. I may have to resort to that.

    Renee


  • 12.  RE: Exclude identifiable customer numbers from masking

    Posted 02-21-2018 06:40:00 PM
    Mouhssine,

    These two fields are located in the same Customer_Info record. Here are the possible scenarios:

    Do not mask either field:
    Primary_ID = M1234567 
    Spouse_ID = M9876543

    Mask the Spouse_ID but not the Primary_ID:
    Primary_ID = M1234567
    Spouse_ID = 333-22-4444

    Mask the Primary_ID but not the Spouse_ID:
    Primary_ID = 999-88-7777
    Spouse_ID = M5678901

    Mask both fields:
    Primary_ID = 555-44-3333
    Spouse_ID = 111-22-8888

    How would you write a filter to get these two fields masked so that the M% numbers are preserved and the SSN numbers are masked?

    Thanks for your help,
    Renee



  • 13.  RE: Exclude identifiable customer numbers from masking

    Posted 02-21-2018 07:24:00 PM
    Hi Renee,

    it seems that you're falling into the case of the two separated rulesets:
    You have to create two separated rulesets:
    1. Ruleset_name: "Customer_Info_Primary_ID", Filter:  "Primary_ID  NOT LIKE 'M%'", Inventory: "Mask Primary_ID"
    2. Ruleset_name: "Customer_Info_Spouse_ID", Filter:  "Spouse_ID  NOT LIKE 'M%'", Inventory: "Mask Spouse_ID"

    Then create two separated masking jobs, one for each ruleset.
    Avoid to execute the two jobs in parallel, you could get dead lock error at the rdbms level.

    Hope this help you.

    Regards.
    Gianpiero



  • 14.  RE: Exclude identifiable customer numbers from masking

    Posted 02-21-2018 07:45:00 PM
    Gianpiero,

    I was beginning to suspect that. Thank you for confirming it.

    Renee


  • 15.  RE: Exclude identifiable customer numbers from masking
    Best Answer

    Posted 02-22-2018 07:05:00 AM
    Hi Renee,

    As per definition Delphix masking is a operating per column and have no builting relational capability.

    The only way you can achieve your need is to follow gainpiero’s recommandations hi own wide experience with masking ;).

    Regards,

    Mouhssine


  • 16.  RE: Exclude identifiable customer numbers from masking

    Posted 02-22-2018 08:08:00 AM
    Should be an idea to ask for new smart masking algorithms that mask with a condition...


  • 17.  RE: Exclude identifiable customer numbers from masking
    Best Answer

    Posted 02-22-2018 01:15:00 PM
    Yes! That would be very useful and make the tool much more powerful. I have setup the redundant rule sets and jobs. Now let's hope I can schedule these to not create deadlocks. I really do appreciate your help and insights very much.

    Renee


  • 18.  RE: Exclude identifiable customer numbers from masking

    Posted 02-26-2018 10:40:00 AM
    Another option is to create 2 algorithms for the 2 columns in the same table and since we only need to exclude at the column level, we could customize the created algorithm with an if-else to exclude those values from being masked. This resolves the conflict when >1 column in the same table has to be filtered.

    of course, customization is slightly complex and will need Delphix services to help with that, but if there aren't many such customisations, it can be done to avoid creating huge number of rulesets


  • 19.  RE: Exclude identifiable customer numbers from masking

    Posted 02-26-2018 12:40:00 PM
    Thank you Mayank. That sounds like an interesting option. I will keep that in mind as I progress with this issue.

    Thanks,
    Renee