field extraction for the complex data

Supriya — Mon, 04 Oct 2021 17:32:00 GMT

Hi Team,

Could someone help me with the field extraction for the below complex data(1000 lines of data I concised to 10 lines of data ) :

columns to be extracted are statement_text , cnt, total_reads, total_writes, db_name

statement_text="SELECT ISNULL("Interface Entry Header"."timestamp",@3) AS "timestamp",ISNULL("Interface Entry Header"."ImageURL",@8) AS "ImageURL",ISNULL("Interface Entry Header"."Pick Date Time",@7) AS "Pick Date Time",ISNULL("Interface Entry Header"."Start Execution",@7) AS "Start Execution",ISNULL("Interface Entry Header"."End Execution",@7) AS "End Execution",ISNULL("Interface Entry Header"."Send Request",@7) AS "Send Request",ISNULL("Maximo Issue Type$Interface Entry Line"."Header Entry No_",@4) AS "Maximo Issue Type$Interface Entry Line$Header Entry No_",ISNULL("Maximo Issue Type$Interface Entry Line"."Entry No_",@4) AS "Maximo Issue Type$Interface Entry Line$Entry No_" FROM "Algeria".dbo."Tango$Interface Entry Line" AS "Maximo Issue Type$Interface Entry Line" WITH(READUNCOMMITTED) WHERE ("Maximo Issue Type$Interface Entry Line"."Header Entry No_"="Interface Entry Header"."Entry No_") ORDER BY "Maximo Issue Type$Interface Entry Line$Header Entry No_" ASC,"Maximo Issue Type$Interface Entry Line$Entry No_" ASC) AS "SUB$Maximo Issue Type" WHERE ("Interface Entry Header"."Interface Code"=@0 AND "Interface Entry Header"."Direction"=@1 AND "Interface Entry Header"."Status"=@2) ORDER BY "Source No_" ASC,"Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)", cnt="12", total_reads="31",
total_writes="0", db_name="test"

I couldn't able to extract the statement_text column completely and the remaining columns are working fine

index="index" source="source1"| rex field=_raw "statement_text\=\"(?<statement_text>[@ ( ) $ . , \"A-Z ! ^ | \" - _ : { } A-Z a-z _ 0-9]+]+)\"" | rex field=_raw "cnt\=\"(?<cnt>[0-9]+)\"" | rex field=_raw "diff_reads\=\"(?<diff_reads>[0-9]+)\""| rex field=_raw "total_writes\=\"(?<total_writes>[0-9]+)\"" | rex field=_raw "db_name\=\"(?<db_name>[A-Z a-z _ 0-9]+)\""

Please provide me rex for statement_text column where the data can be extracted till the 2nd column "cnt"

Re: field extraction for the complex data

ITWhisperer — Mon, 04 Oct 2021 18:41:05 GMT

| rex "statement_text=\"(?<statement_text>.*)\",\s+cnt"

Re: field extraction for the complex data

Supriya — Tue, 05 Oct 2021 11:45:13 GMT

Its working thank you!
could you please explain @ITWhisperer

Re: field extraction for the complex data

ITWhisperer — Tue, 05 Oct 2021 12:02:29 GMT

statement_text=\"(?<statement_text>.*)\",\s+cnt

pre-anchor fieldname pattern post-anchor

That is, everything (.*) between statement_text=" and ", cnt is put into field named statement_text

Re: field extraction for the complex data

Supriya — Tue, 05 Oct 2021 16:33:12 GMT

thank you for explaining and accepted your solution

Here one more issue I have is while downloading these results in CSV file, the column statement_text is splitting in different rows. could you help me with this issue @ITWhisperer

topic Re: field extraction for the complex data in Other Usage

field extraction for the complex data

Re: field extraction for the complex data

Re: field extraction for the complex data

Re: field extraction for the complex data

Re: field extraction for the complex data