topic Re: Regex extraction with different field delimiters in Getting Data In

Regex extraction with different field delimiters

michael_vi — Sun, 31 Dec 2023 09:44:51 GMT

Hi all,

I have a very specifc regex extraction (search time extraction)

_raw data example:

| union
[| makeresults
| eval _raw = "Dec-28-2023|12:05:46,836|10.150.6.118|148:|some branch|uswer_name|d168a8b9-5647-421b-97ba-f2aa3bceb69a|1:Creation page stack|Success|action_portfolio_forms_c_save.action|8970:PORTFOLIO ONBOARDING - FORMS CAPTURE||3065254228||||||| ~newType|||"]
[| makeresults
| eval _raw = "Dec-28-2023|12:05:46,836|10.150.6.118|148:|some branch|uswer_name|d168a8b9-5647-421b-97ba-f2aa3bceb69a|1:Creation page stack|Success|action_portfolio_forms_c_save.action|8970:PORTFOLIO ONBOARDING - FORMS CAPTURE||3065254228|||||||oldType~newType|||"]
[| makeresults
| eval _raw = "Dec-28-2023|12:05:46,836|10.150.6.118|148:|some branch|uswer_name|d168a8b9-5647-421b-97ba-f2aa3bceb69a|1:Creation page stack|Success|action_portfolio_forms_c_save.action|8970:PORTFOLIO ONBOARDING - FORMS CAPTURE||3065254228||||||||||"]

I want to extract 2 fields from position 19 of the pipe and until 20th position that include (or may not) 2 fields that need to be extracted

new: comes right after 19th | and before ~

old: comes after ~ and before 20th |

There are 3 option that the data may appear:

|<space>~newType|

|oldType~newType|

|<null><null>|

The problem that I have is when no data is presented (3rd option) , then the props.conf doesn't parse it

In the end I need to have 2 fields, based on the example above:

old	new
<space>	newType
oldType	newType
<space>	<space>

props.conf

[user_activity] REPORT-bb_extract = REPORT-bb_extract EXTRACT-oldAccountType = ^(?:[^|]*\|){19}(?<old>[^\~|\|]*) EXTRACT-newAccountType = (?:[^~]*\~){1}(?<new>[^|]*)

transforms.conf

[REPORT-bb_extract] KEEP_EMPTY_VALS = true DELIMS = "|" FIELDS = "DATE","TIME","ip","branch","appName","userName","actionID","actionType","actionStatus","actionName","action","srcPortfolioId","refID","currency","TotalAmount","secondPortfolioId","multiTransfer","field18","field19","id2","field21","new","old"

1. How can I extract the field that may or may not include some value

2. How can I fix the the second regex to start with ~ after 19th |

Thanks

Re: Regex extraction with different field delimiters

gcusello — Sun, 31 Dec 2023 09:54:42 GMT

Hi @michael_vi,

please try this:

| union [| makeresults | eval _raw = "Dec-28-2023|12:05:46,836|10.150.6.118|148:|some branch|uswer_name|d168a8b9-5647-421b-97ba-f2aa3bceb69a|1:Creation page stack|Success|action_portfolio_forms_c_save.action|8970:PORTFOLIO ONBOARDING - FORMS CAPTURE||3065254228||||||| ~newType|||"] [| makeresults | eval _raw = "Dec-28-2023|12:05:46,836|10.150.6.118|148:|some branch|uswer_name|d168a8b9-5647-421b-97ba-f2aa3bceb69a|1:Creation page stack|Success|action_portfolio_forms_c_save.action|8970:PORTFOLIO ONBOARDING - FORMS CAPTURE||3065254228|||||||oldType~newType|||"] [| makeresults | eval _raw = "Dec-28-2023|12:05:46,836|10.150.6.118|148:|some branch|uswer_name|d168a8b9-5647-421b-97ba-f2aa3bceb69a|1:Creation page stack|Success|action_portfolio_forms_c_save.action|8970:PORTFOLIO ONBOARDING - FORMS CAPTURE||3065254228||||||||||"] | rex "^([^\|]*\|){19}(?<OldType>[^\~]*)\~(?<NewType>[^\|]*)" | fillnull value=" " OldType | fillnull value=" " NewType | table OldType NewType

You can test the regex at https://regex101.com/r/gETm2b/1

Ciao.

Giuseppe

Re: Regex extraction with different field delimiters

michael_vi — Sun, 31 Dec 2023 10:16:40 GMT

Good idea to combine...

Is it possible to replace the NULL with space w/o using SPL? Within the porps.conf

Re: Regex extraction with different field delimiters

gcusello — Sun, 31 Dec 2023 10:33:15 GMT

Hi @michael_vi,

using my search, you replace the NULLs with space in SPL,

I'm not sure that's possible to do it in props.conf, but you could create a calculated field that automatically transforms null in space.

Ciao.

Giuseppe

Re: Regex extraction with different field delimiters

michael_vi — Sun, 31 Dec 2023 10:37:03 GMT

@gcusello

The problem is that, once props can't extract the field. it won't be shown in extracted fileds.

so.. | fillnull value=NULL will not work

Re: Regex extraction with different field delimiters

gcusello — Sun, 31 Dec 2023 10:41:04 GMT

Hi @michael_vi,

using this search, you have two evals (that you can save as calculated fields) that makes the same job:

| union [| makeresults | eval _raw = "Dec-28-2023|12:05:46,836|10.150.6.118|148:|some branch|uswer_name|d168a8b9-5647-421b-97ba-f2aa3bceb69a|1:Creation page stack|Success|action_portfolio_forms_c_save.action|8970:PORTFOLIO ONBOARDING - FORMS CAPTURE||3065254228||||||| ~newType|||"] [| makeresults | eval _raw = "Dec-28-2023|12:05:46,836|10.150.6.118|148:|some branch|uswer_name|d168a8b9-5647-421b-97ba-f2aa3bceb69a|1:Creation page stack|Success|action_portfolio_forms_c_save.action|8970:PORTFOLIO ONBOARDING - FORMS CAPTURE||3065254228|||||||oldType~newType|||"] [| makeresults | eval _raw = "Dec-28-2023|12:05:46,836|10.150.6.118|148:|some branch|uswer_name|d168a8b9-5647-421b-97ba-f2aa3bceb69a|1:Creation page stack|Success|action_portfolio_forms_c_save.action|8970:PORTFOLIO ONBOARDING - FORMS CAPTURE||3065254228||||||||||"] | rex "^([^\|]*\|){19}(?<OldType>[^\~]*)\~(?<NewType>[^\|]*)" | eval OldType=if(isnull(OldType)," ",OldType), NewType=if(isnull(NewType)," ",NewType) | table OldType NewType

So you can have them in interesting fields.

Ciao.

Giuseppe

Re: Regex extraction with different field delimiters

michael_vi — Sun, 31 Dec 2023 11:47:14 GMT

Something is missing.

The 'NewType' filed may or may not start with the ~, if no data then it will start with the |
In the attached picture the field 'NewType' is missing for the event that has only pipes.

Re: Regex extraction with different field delimiters

ITWhisperer — Sun, 31 Dec 2023 11:47:36 GMT

Try something like this

| rex "([^\|]*\|){19}(?<oldType>[^~\|]*)~?(?<newType>[^\|]*)"

Re: Regex extraction with different field delimiters

michael_vi — Sun, 31 Dec 2023 12:49:57 GMT

Managed to fix the old and new in props.conf:

| rex "^([^\|]*\|){19}(?<OldType>[^\~|\|]*)\~|\|" | rex "^([^\|]*\|){19}.+~(?<NewType>[^\|]*)\|"

Still having trouble with the || (null values)

Re: Regex extraction with different field delimiters

dtburrows3 — Sun, 31 Dec 2023 17:52:52 GMT

~~So the approach I took here is to use an EXTRACT in props.conf to target the entire value between pipe 19 and pipe 20.~~
~~And then use EVALS is props to parse out that extracted value depending on its format.~~
Edit:
Noticed that there is no need to add an additional EXTRACT to props to get the full value because there is a field already extracted named 'id2' doing the same thing. So an even simpler way of doing this would be,

props.conf entry for forcing empty string if they are null.

[user_activity] ... EVAL-oldType = if(NOT (match('id2', "^\s*$") OR isnull(id2)), mvindex(split(id2, "~"), 0), "") EVAL-newType = if(NOT (match('id2', "^\s*$") OR isnull(id2)), mvindex(split(id2, "~"), 1), "")

Evidence of the null values being forced to empty strings

props.conf entries for forcing to single whitespace if values are null from _raw

[user_activity] ... EVAL-oldType = if(NOT (match(id2, "^\s*$") OR isnull(id2)), if(mvindex(split(id2, "~"), 0)=="", " ", mvindex(split(id2, "~"), 0)), " ") EVAL-newType = if(NOT (match(id2, "^\s*$") OR isnull(id2)), if(mvindex(split(id2, "~"), 1)=="", " ", mvindex(split(id2, "~"), 1)), " ")

Evidence for the desired values are single whitespaces.

I believe this works against the example you provided and you can see in the screenshot below that I have evaluated some boolean value inline in the search to show if the values are actually null or not.

Re: Regex extraction with different field delimiters

michael_vi — Sun, 31 Dec 2023 18:52:59 GMT

Looks good. I'll check it.

I also thought of using EVAL after extraction to replace NULLs and spaces:

EVAL-OldType=if(isnull(OldType) OR OldType = " ", "noData", OldType) EVAL-NewType=if(isnull(NewType) OR NewType = " ", "noData", NewType)