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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...