Other Usage

field extraction for the complex data

Supriya
Path Finder

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"

Labels (3)
0 Karma
1 Solution

Supriya
Path Finder

Its working thank you! 
could you please explain @ITWhisperer 

View solution in original post

0 Karma

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

Supriya
Path Finder

Its working thank you! 
could you please explain @ITWhisperer 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

Supriya
Path Finder

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 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...