Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Sebastiaan Draaisma
    A 'How to' on using regular expressions in custom...
    Topic posted May 21, 2018 by Sebastiaan DraaismaBlack Diamond: 60,000+ Points 
    483 Views, 9 Comments
    A 'How to' on using regular expressions in custom scripts for GDPR

    GDPR, how to find sensitive data in a report using regular expressions in custom scripts.

    This is a how to for those new to reporting and custom scripts :-)

    Customers can enter sensitive data about anything and write it in any field that allows for free text.

    • Incident subject
    • Message thread
    • Custom fields
    • Custom objects

    While it is possible to use a regular expressions in a business rule to search for sensitive data and set a custom field “Sensitive data Yes/No”, the use of regular expressions in report data is usually limited to the filters and variables only.

    In this post I try to explain the use of custom scripts to show a text in a report when a match is found or how to use it as a masking function.

    Custom scripts can be very useful and efficient when using regular expressions.
    The custom scripts we will be using are changing the data in a report column (not in the database).

    Each report column has a ID number, starting from 0

    Some of you know how to alter data using the IF syntax in a report
    if(incidents.last_resp IS NULL,'No reply has been sent','A reply has been sent on '||incidents.last_resp)

    Custom scripts allows you to do similar things but gives you the opportunity to use regular expressions. As mentioned before, columns have a ID number and it is this ID number that tells the script which column we want to play with.

    Custom scripts can be entered on the Level tab
    The ones we will use will go in the Process tab
    Create a custom script

    Let’s give it a go smiley

    Paste the following PHP code (script) in the Process tab and everything in your column will be replaced with the word ‘Test’

    $rows[0][2]->val = 'Test';

    Inspect the syntax in your column and you will see that this one has not been changed, the custom script has a higher priority and shows the data regardless of the used syntax.

    Let’s experiment a little more smiley

    Create a report with the following columns

    1. incidents.ref_no
    2. incidents.subject
    3. first(threads.note, threads.entered, threads.seq)
    4. first(threads.note, threads.entered, threads.seq)

    Lets give them the following headers

    1. Incident
    2. Subject
    3. Custom Script
    4. Original

    Open your format editor for column 3 & 4 and select the clear HTML tags checkbox
    Access column format options

    Masking with regular expressions

    This code will look for a specific number combination and will mask this with XXXXX.

    This code can be useful when allowing all agents to preview the thread note in a report and were only a specific profile is able to view the incident in the workspace without masking.

    We are going back to the custom scripts and enter the following script (remember to include the semi colon ‘ ; ‘  or your script will not work)

    $rows[0][2]->val = preg_replace("/\b[0-9]{6}-[0-9]{4}\b/", "XXXXX", $rows[0][2]->val);

    Save your report and create a test incident that contains some text and 6 digits followed by a dash followed by 4 digits (123456-1234)

    As we can see, the number combination has been replaced with XXXXX. When you open the incident you will be able to see the number. A workspace rule can be created to show an empty tab for profiles that should not be able to see the content.

    You may have noticed the   (non breaking space) character. We can easily remove this one by adding another line of code

    $rows[0][2]->val = str_replace(" ", "", $rows[0][2]->val);


    Show Match/No match

    When no preview is required but we simply want to show a Match/No match text we can do that as followed.
    Open the process tab again and replace the previous scripts with the one below

    if (preg_match("/\b[0-9]{6}-[0-9]{4}\b/", $rows[0][2]->val)) {

        $rows[0][2]->val = 'A match was found.';

    } else {

        $rows[0][2]->val = 'No match was not found.';


    Articles of interest for the ones new to Analytics & Custom Scripts

    I hope this post will give some ideas on how custom scripts can be used.
    Feel free to experiment and please upload your creative creations for others to learn from laugh



    • Erica (Leep) Anderson

      Thanks for taking the time to share this primer, especially as it relates to GDPR, with the Community, Sebastiaan! I have been getting waves for notifications from companies announcing changes to their privacy policies (GDPR-influenced, I'm sure!), so this is very timely!

    • Pavol Procka

      Love the report! Thanks!

    • Ivan Abaitey

      Great one there. Great stuff. We have made huge changes because of GPDR and yesterday we talked about dealing with reports. You just did my job for me as I have 4 free hours now to watch the world cup!!!. Thanks

    • Narendra Muttineni

      Good Stuff and definitely useful !! Thanks for sharing !


    • Dakoto Ayasemale

      That was really helpful

    • Dakoto Ayasemale

      Hi all, I am trying to remove all the .invalid email inj my report but I am having trouble accessing the contacts.email_address table. any Help?

      • Sebastiaan Draaisma

        This can be done with a custom script. I assume it's only for reporting purpose and that you do not require a change in the database?

        If so, this could be done by using
        $rows[0][2]->val = str_replace(".invalid", "", $rows[0][2]->val);

        See attached report example

    • Dakoto Ayasemale

      Thanks for the prompt reply I will try it within my report, all I want to is to remove the .invalid on the contact email signature. I am new to the Rightnow family I just want to get up the speed and I think that the email field is a bit complication as when I run ROQL query I get nothing.Thanks Sebatian

      • Sebastiaan Draaisma

        Best to create a new topic for this. It will generate more exposure so others can help out. Normally the .invalid is only visible on test & upgrade sites and this is done to prevent live data from coming into your test & upgrade area which you otherwise should have gotten into your production environment. So for this reason it is not adviced to change this. I'm more than happy to assist in what I know but please create a new topic for this :-)