regexp_extract is not working- expression is incomplete

[Amyexplorer]

Hello Looker community, Greetings,

For my chronicle custom dashboard, Im using looker function named regexp_extract() but it throws me expression is incomplete error. Below is my requirement, i need to extract only ticket value from the query string.
Query text:

//TC123

target.application=pubsub.googleapis.come OR metadata.logtype=generic_event

from the above query text, i need to extract only TC123 and store it in ticket_id field.

REGEXP_EXTRACT(${events__about__labels.value},(TC.*)')

I kindly request someone to provide your valuable insights on this to help me resolve this.

[Amyexplorer]

@Jacqui_N , any suggestion on this, please?

[Jacqui_N]

Hi @Amyexplorer 

It seems like you are missing a single quotation mark at the start of the regular expression definition. Another thing I noticed is that your regular expression will also return all and any text following 'TC' but if you want just the ticket number you'll probably want to define the expression as below:

REGEXP_EXTRACT(${events__about__labels.value},'(TC[0-9]+)')

[Amyexplorer]

Hi @Jacqui_N ,Thank you very much for your input. Ive tried like you mentioned above but it says, "Expression is incomplete".I really have no idea how to resolve it. your expertise is highly appreciated.

[Jacqui_N · Accepted solution]

Hi @Amyexplorer 

Ah I see, I thought you were trying to create the calculation in LookML not in a Table Calculation. In Looker Table Calculations there is no REGEXP_EXTRACT function (you can see valid string operators here). Another thing to note is that Table Calculations do not accept strings in single quotations, only double quotations; this is why you're getting the error around the regular expression. However, even if you changed it to double quotations you will still get an error because there is no REGEXP_EXTRACT function.

One workaround I can see for you is using the substring operator if the ticket number is always at the beginning of the string and the ticket number is always the same length. For the string example "//TC123......" you could use the below Table Calculation where we are extracting the substring of the string starting at position 3 (T) and ending 5 characters later.

substring(${events__about__labels.value},3,5)

[Amyexplorer]

Thank you so much for your assistance, @Jacqui_N . This solved my problem. I'm curious to know if there are any plans to implement string extraction functions, similar to Looker's, in Google Chronicle. This feature would be highly beneficial for us in extracting specific data we require.

Many Thanks

Emmie