Field Extraction from SQL Coded Events

[SplunkDash]

Hello,

I have some issues to extract fields from this SQL coded events. Is there any way we can perform field extraction on these events?  Two sample events are given below. Thank you so much, any help will be highly appreciated.

Q17CNB_L_0__20210630-235755_5828.html@^@^2021/06/30@^@^23:57:55@^@^ Q17CNB @^@^select "a"."basetin","w2nonus","w2maxdistoff","ssanonus","ssamaxdistoff","f1099rnonus","f1099rmaxdistoff","f1099miscnonus","f1099miscmaxdistoff","f1099gnonus","f1099gmaxdistoff","f1099intnonus","f1099intmaxdistoff","f1099oidnonus","f1099oidmaxdistoff","f1041k1nonus","f1041k1maxdistoff","f1065k1nonus","f1065k1maxdistoff","wages_w2","allocated_tips_w2","medicare_wages_w2","taxable_fica_tips_w2","WITHHLDG_w2","pens_annties_f1099_ssa_rrb","withhldg_f1099_ssa_rrb","gross_distrib_f1099r","taxable_amt_f1099r","WITHHLDG_f1099r","non_emp_compensation_f1099misc","othincome_f1099misc","rents_f1099misc","royalties_f1099misc","crop_insurance_f1099misc","WITHHLDG_f1099misc","taxbl_grant_f1099g","UNEMP_COMP_f1099g","prior_refnd_f1099g","agr_subsds_f1099g","atta_pymnt_f1099g","WITHHLDG_f1099g","interest_f1099int","savings_bonds_f1099int","WITHHLDG_f1099int","interest_f1099oid","withhldg_f1099oid","interest_f1041_k1","bus_inc_f1041_k1","net_rental_f1041_k1","oth_prtflo_f1041_k1","oth_rental_f1041_k1","interest_f1065_k1","guarpaymt_f1065_k1","ord_inc_f1065_k1","othrental_f1065_k1","realestate_f1065_k1","royalties_f1065_k1","section179_f1065_k1" into #TEMP9 from(select "basetin","w2nonus","w2maxdistoff","ssanonus","ssamaxdistoff","f1099rnonus","f1099rmaxdistoff","f1099miscnonus","f1099miscmaxdistoff","f1099gnonus","f1099gmaxdistoff","f1099intnonus","f1099intmaxdistoff","f1099oidnonus","f1099oidmaxdistoff","f1041k1nonus","f1041k1maxdistoff","wages_w2","allocated_tips_w2","medicare_wages_w2","taxable_fica_tips_w2","WITHHLDG_w2","pens_annties_f1099_ssa_rrb","withhldg_f1099_ssa_rrb","gross_distrib_f1099r","taxable_amt_f1099r","WITHHLDG_f1099r","non_emp_compensation_f1099misc","othincome_f1099misc","rents_f1099misc","royalties_f1099misc","crop_insurance_f1099misc","WITHHLDG_f1099misc","taxbl_grant_f1099g","UNEMP_COMP_f1099g","prior_refnd_f1099g","agr_subsds_f1099g","atta_pymnt_f1099g","WITHHLDG_f1099g","interest_f1099int","savings_bonds_f1099int","WITHHLDG_f1099int","interest_f1099oid","withhldg_f1099oid","interest_f1041_k1","bus_inc_f1041_k1","net_rental_f1041_k1","oth_prtflo_f1041_k1","oth_rental_f1041_k1" from #TEMP8) as "A" left outer join(select "tin","min"(case when "f1065k1nonus" = 1 then 1 else 0 end) as "f1065k1nonus","max"(case when "f1065k1maxdistoff" = 1 then 1 when "f1065k1maxdistoff" = 2 then 2 when "f1065k1maxdistoff" = 3 then 3 when "f1065k1maxdistoff" = 4 then 4 when "f1065k1maxdistoff" = 5 then 5 else 0 end) as "f1065k1maxdistoff","sum"("interest_f1065_k1") as "interest_f1065_k1","sum"("guarpaymt_f1065_k1") as "guarpaymt_f1065_k1","sum"("ord_inc_f1065_k1") as "ord_inc_f1065_k1","sum"("othrental_f1065_k1") as "othrental_f1065_k1","sum"("realestate_f1065_k1") as "realestate_f1065_k1","sum"("royalties_f1065_k1") as "royalties_f1065_k1","sum"("section179_f1065_k1") as "section179_f1065_k1" from #TEMP9a group by "tin") as "B" on "a"."basetin" = "b"."tin"@^@^D7CNB.#TEMP9|Temp D7CNB.#TEMP8 AS A|Temp  Q17CNB.#TEMP9A@^@^

N17CNB_L_0__20210630-235521_5826.html@^@^2021/06/30@^@^23:55:21@^@^ N17CNB @^@^select "a"."basetin","w2nonus","w2maxdistoff","ssanonus","ssamaxdistoff","f1099rnonus","f1099rmaxdistoff","f1099miscnonus","f1099miscmaxdistoff","f1099gnonus","f1099gmaxdistoff","f1099intnonus","f1099intmaxdistoff","f1099oidnonus","f1099oidmaxdistoff","f1041k1nonus","f1041k1maxdistoff","wages_w2","allocated_tips_w2","medicare_wages_w2","taxable_fica_tips_w2","WITHHLDG_w2","pens_annties_f1099_ssa_rrb","withhldg_f1099_ssa_rrb","gross_distrib_f1099r","taxable_amt_f1099r","WITHHLDG_f1099r","non_emp_compensation_f1099misc","othincome_f1099misc","rents_f1099misc","royalties_f1099misc","crop_insurance_f1099misc","WITHHLDG_f1099misc","taxbl_grant_f1099g","UNEMP_COMP_f1099g","prior_refnd_f1099g","agr_subsds_f1099g","atta_pymnt_f1099g","WITHHLDG_f1099g","interest_f1099int","savings_bonds_f1099int","WITHHLDG_f1099int","interest_f1099oid","withhldg_f1099oid","interest_f1041_k1","bus_inc_f1041_k1","net_rental_f1041_k1","oth_prtflo_f1041_k1","oth_rental_f1041_k1" into #TEMP8 from(select "basetin","w2nonus","w2maxdistoff","ssanonus","ssamaxdistoff","f1099rnonus","f1099rmaxdistoff","f1099miscnonus","f1099miscmaxdistoff","f1099gnonus","f1099gmaxdistoff","f1099intnonus","f1099intmaxdistoff","f1099oidnonus","f1099oidmaxdistoff","wages_w2","allocated_tips_w2","medicare_wages_w2","taxable_fica_tips_w2","WITHHLDG_w2","pens_annties_f1099_ssa_rrb","withhldg_f1099_ssa_rrb","gross_distrib_f1099r","taxable_amt_f1099r","WITHHLDG_f1099r","non_emp_compensation_f1099misc","othincome_f1099misc","rents_f1099misc","royalties_f1099misc","crop_insurance_f1099misc","WITHHLDG_f1099misc","taxbl_grant_f1099g","UNEMP_COMP_f1099g","prior_refnd_f1099g","agr_subsds_f1099g","atta_pymnt_f1099g","WITHHLDG_f1099g","interest_f1099int","savings_bonds_f1099int","WITHHLDG_f1099int","interest_f1099oid","withhldg_f1099oid" from #TEMP7) as "A" left outer join(select "tin","min"(case when "f1041k1nonus" = 1 then 1 else 0 end) as "f1041k1nonus","max"(case when "f1041k1maxdistoff" = 1 then 1 when "f1041k1maxdistoff" = 2 then 2 when "f1041k1maxdistoff" = 3 then 3 when "f1041k1maxdistoff" = 4 then 4 when "f1041k1maxdistoff" = 5 then 5 else 0 end) as "f1041k1maxdistoff","sum"("interest_f1041_k1") as "interest_f1041_k1","sum"("bus_inc_f1041_k1") as "bus_inc_f1041_k1","sum"("net_rental_f1041_k1") as "net_rental_f1041_k1","sum"("oth_prtflo_f1041_k1") as "oth_prtflo_f1041_k1","sum"("oth_rental_f1041_k1") as "oth_rental_f1041_k1" from #TEMP8A group by "tin") as "B" on "a"."basetin" = "b"."tin"@^@^D7CNB.#TEMP8|Temp D7CNB.#TEMP7 AS A|Temp  N17CNB.#TEMP8A@^@^

[SplunkDash]

Hello,

Thank you so much appreciated.

Yes, I got additional 4 fields :  DateTime, ID, select, and SQLField, with your code, that is great.  But, I think we have more fields and associated values should be coming from these SQL  queries. Are there any ways we can get some of them?   Thank you so much, appreciate your support in these efforts.

[richgalloway]

I think the existing answers show how to extract fields from events.  If you need more help, feel free to post a more specific question.  Be sure to share the query you've tried and what's not working about it.

[SplunkDash]

Thank you again. Is there any ways I can extract the field values like :  "f1099rnonus","f1099rmaxdistoff", and "tin"

[richgalloway]

Yes, there is a way and I've already shown it to you.  See this run-anywhere example.

| makeresults | eval _raw="N17CNB_L_0__20210630-235521_5826.html@^@^2021/06/30@^@^23:55:21@^@^ N17CNB @^@^select \"a\".\"basetin\",\"w2nonus\",\"w2maxdistoff\",\"ssanonus\",\"ssamaxdistoff\",\"f1099rnonus\",\"f1099rmaxdistoff\",\"f1099miscnonus\",\"f1099miscmaxdistoff\",\"f1099gnonus\",\"f1099gmaxdistoff\",\"f1099intnonus\",\"f1099intmaxdistoff\",\"f1099oidnonus\",\"f1099oidmaxdistoff\",\"f1041k1nonus\",\"f1041k1maxdistoff\",\"wages_w2\",\"allocated_tips_w2\",\"medicare_wages_w2\",\"taxable_fica_tips_w2\",\"WITHHLDG_w2\",\"pens_annties_f1099_ssa_rrb\",\"withhldg_f1099_ssa_rrb\",\"gross_distrib_f1099r\",\"taxable_amt_f1099r\",\"WITHHLDG_f1099r\",\"non_emp_compensation_f1099misc\",\"othincome_f1099misc\",\"rents_f1099misc\",\"royalties_f1099misc\",\"crop_insurance_f1099misc\",\"WITHHLDG_f1099misc\",\"taxbl_grant_f1099g\",\"UNEMP_COMP_f1099g\",\"prior_refnd_f1099g\",\"agr_subsds_f1099g\",\"atta_pymnt_f1099g\",\"WITHHLDG_f1099g\",\"interest_f1099int\",\"savings_bonds_f1099int\",\"WITHHLDG_f1099int\",\"interest_f1099oid\",\"withhldg_f1099oid\",\"interest_f1041_k1\",\"bus_inc_f1041_k1\",\"net_rental_f1041_k1\",\"oth_prtflo_f1041_k1\",\"oth_rental_f1041_k1\" into #TEMP8 from(select \"basetin\",\"w2nonus\",\"w2maxdistoff\",\"ssanonus\",\"ssamaxdistoff\",\"f1099rnonus\",\"f1099rmaxdistoff\",\"f1099miscnonus\",\"f1099miscmaxdistoff\",\"f1099gnonus\",\"f1099gmaxdistoff\",\"f1099intnonus\",\"f1099intmaxdistoff\",\"f1099oidnonus\",\"f1099oidmaxdistoff\",\"wages_w2\",\"allocated_tips_w2\",\"medicare_wages_w2\",\"taxable_fica_tips_w2\",\"WITHHLDG_w2\",\"pens_annties_f1099_ssa_rrb\",\"withhldg_f1099_ssa_rrb\",\"gross_distrib_f1099r\",\"taxable_amt_f1099r\",\"WITHHLDG_f1099r\",\"non_emp_compensation_f1099misc\",\"othincome_f1099misc\",\"rents_f1099misc\",\"royalties_f1099misc\",\"crop_insurance_f1099misc\",\"WITHHLDG_f1099misc\",\"taxbl_grant_f1099g\",\"UNEMP_COMP_f1099g\",\"prior_refnd_f1099g\",\"agr_subsds_f1099g\",\"atta_pymnt_f1099g\",\"WITHHLDG_f1099g\",\"interest_f1099int\",\"savings_bonds_f1099int\",\"WITHHLDG_f1099int\",\"interest_f1099oid\",\"withhldg_f1099oid\" from #TEMP7) as \"A\" left outer join(select \"tin\",\"min\"(case when \"f1041k1nonus\" = 1 then 1 else 0 end) as \"f1041k1nonus\",\"max\"(case when \"f1041k1maxdistoff\" = 1 then 1 when \"f1041k1maxdistoff\" = 2 then 2 when \"f1041k1maxdistoff\" = 3 then 3 when \"f1041k1maxdistoff\" = 4 then 4 when \"f1041k1maxdistoff\" = 5 then 5 else 0 end) as \"f1041k1maxdistoff\",\"sum\"(\"interest_f1041_k1\") as \"interest_f1041_k1\",\"sum\"(\"bus_inc_f1041_k1\") as \"bus_inc_f1041_k1\",\"sum\"(\"net_rental_f1041_k1\") as \"net_rental_f1041_k1\",\"sum\"(\"oth_prtflo_f1041_k1\") as \"oth_prtflo_f1041_k1\",\"sum\"(\"oth_rental_f1041_k1\") as \"oth_rental_f1041_k1\" from #TEMP8A group by \"tin\") as \"B\" on \"a\".\"basetin\" = \"b\".\"tin\"@^@^D7CNB.#TEMP8|Temp D7CNB.#TEMP7 AS A|Temp  N17CNB.#TEMP8A@^@^"
| rex "select (?<select>.*) into"
| rex field=select max_match=0 "(?<SQLField>[^,]+),"
| eval SQLField=mvmap(SQLField, trim(SQLField,"\""))
| table SQLfield

[SplunkDash]

You are awesome, thank you so much.   But, these events are under index=cdsw  and was trying to extract those fields from that events and I wrote this (just added index=cdsw at the beginning).... but not working..... 

index=cdsw | 

| makeresults | eval _raw="N17CNB_L_0__20210630-235521_5826.html@^@^2021/06/30@^@^23:55:21@^@^ N17CNB @^@^select \"a\".\"basetin\",\"w2nonus\",\"w2maxdistoff\",\"ssanonus\",\"ssamaxdistoff\",\"f1099rnonus\",\"f1099rmaxdistoff\",\"f1099miscnonus\",\"f1099miscmaxdistoff\",\"f1099gnonus\",\"f1099gmaxdistoff\",\"f1099intnonus\",\"f1099intmaxdistoff\",\"f1099oidnonus\",\"f1099oidmaxdistoff\",\"f1041k1nonus\",\"f1041k1maxdistoff\",\"wages_w2\",\"allocated_tips_w2\",\"medicare_wages_w2\",\"taxable_fica_tips_w2\",\"WITHHLDG_w2\",\"pens_annties_f1099_ssa_rrb\",\"withhldg_f1099_ssa_rrb\",\"gross_distrib_f1099r\",\"taxable_amt_f1099r\",\"WITHHLDG_f1099r\",\"non_emp_compensation_f1099misc\",\"othincome_f1099misc\",\"rents_f1099misc\",\"royalties_f1099misc\",\"crop_insurance_f1099misc\",\"WITHHLDG_f1099misc\",\"taxbl_grant_f1099g\",\"UNEMP_COMP_f1099g\",\"prior_refnd_f1099g\",\"agr_subsds_f1099g\",\"atta_pymnt_f1099g\",\"WITHHLDG_f1099g\",\"interest_f1099int\",\"savings_bonds_f1099int\",\"WITHHLDG_f1099int\",\"interest_f1099oid\",\"withhldg_f1099oid\",\"interest_f1041_k1\",\"bus_inc_f1041_k1\",\"net_rental_f1041_k1\",\"oth_prtflo_f1041_k1\",\"oth_rental_f1041_k1\" into #TEMP8 from(select \"basetin\",\"w2nonus\",\"w2maxdistoff\",\"ssanonus\",\"ssamaxdistoff\",\"f1099rnonus\",\"f1099rmaxdistoff\",\"f1099miscnonus\",\"f1099miscmaxdistoff\",\"f1099gnonus\",\"f1099gmaxdistoff\",\"f1099intnonus\",\"f1099intmaxdistoff\",\"f1099oidnonus\",\"f1099oidmaxdistoff\",\"wages_w2\",\"allocated_tips_w2\",\"medicare_wages_w2\",\"taxable_fica_tips_w2\",\"WITHHLDG_w2\",\"pens_annties_f1099_ssa_rrb\",\"withhldg_f1099_ssa_rrb\",\"gross_distrib_f1099r\",\"taxable_amt_f1099r\",\"WITHHLDG_f1099r\",\"non_emp_compensation_f1099misc\",\"othincome_f1099misc\",\"rents_f1099misc\",\"royalties_f1099misc\",\"crop_insurance_f1099misc\",\"WITHHLDG_f1099misc\",\"taxbl_grant_f1099g\",\"UNEMP_COMP_f1099g\",\"prior_refnd_f1099g\",\"agr_subsds_f1099g\",\"atta_pymnt_f1099g\",\"WITHHLDG_f1099g\",\"interest_f1099int\",\"savings_bonds_f1099int\",\"WITHHLDG_f1099int\",\"interest_f1099oid\",\"withhldg_f1099oid\" from #TEMP7) as \"A\" left outer join(select \"tin\",\"min\"(case when \"f1041k1nonus\" = 1 then 1 else 0 end) as \"f1041k1nonus\",\"max\"(case when \"f1041k1maxdistoff\" = 1 then 1 when \"f1041k1maxdistoff\" = 2 then 2 when \"f1041k1maxdistoff\" = 3 then 3 when \"f1041k1maxdistoff\" = 4 then 4 when \"f1041k1maxdistoff\" = 5 then 5 else 0 end) as \"f1041k1maxdistoff\",\"sum\"(\"interest_f1041_k1\") as \"interest_f1041_k1\",\"sum\"(\"bus_inc_f1041_k1\") as \"bus_inc_f1041_k1\",\"sum\"(\"net_rental_f1041_k1\") as \"net_rental_f1041_k1\",\"sum\"(\"oth_prtflo_f1041_k1\") as \"oth_prtflo_f1041_k1\",\"sum\"(\"oth_rental_f1041_k1\") as \"oth_rental_f1041_k1\" from #TEMP8A group by \"tin\") as \"B\" on \"a\".\"basetin\" = \"b\".\"tin\"@^@^D7CNB.#TEMP8|Temp D7CNB.#TEMP7 AS A|Temp  N17CNB.#TEMP8A@^@^"
| rex "select (?<select>.*) into"
| rex field=select max_match=0 "(?<SQLField>[^,]+),"
| eval SQLField=mvmap(SQLField, trim(SQLField,"\""))
| table SQLfield

[SplunkDash]

Thank you so much, appreciated...I got this result (please see screenshot  below ). But, what the list represents...is it List of the Fields Names or Values of the Fields........... ?.

[richgalloway]

From the information you've shared, it seems like the event contains a SQL query rather than the results of a query.  Therefore, the SQLField field contains field names rather than field values.

[SplunkDash]

Make sense....thank you again. How  I would incorporate this (REX)  in this extraction windows

and would if be possible to explain what do following three lines of your codes are doing here

| rex "select (?<select>.*) into"

| rex field=select max_match=0 "(?<SQLField>[^,]+),"

| eval SQLField=mvmap(SQLField, trim(SQLField,"\""))

[richgalloway]

The Extraction field is expecting a regular expression.  That would be the part inside quotation marks in the rex commands.

```Extract everything after "select " in _raw into the 'select' field.```
| rex "select (?<select>.*) into"
```Scan the 'select' field and extract all instances of text that is not a comma followed by a comma into the field called 'SQLField'.```
| rex field=select max_match=0 "(?<SQLField>[^,]+),"
```For each entry in SQLField, remove the quotation marks from each end```
| eval SQLField=mvmap(SQLField, trim(SQLField,"\""))

[SplunkDash]

Thank you again.

You meant part within  " " in the REX  command of your codes should be in  the Extraction/Transforms  of the following screenshot

[SplunkDash]

Awesome 😊!!! 

[richgalloway · Accepted solution]

Yes, that's what I said.  😊

[richgalloway]

The makeresults and first eval commands are for generating test data.  Remove them to use live data.

[SplunkDash]

Thank you so much, appreciated your support.

In regards to what I am trying to get/extract from this Events,

1. First 6 Alphanumeric Values as a ID field

2. Date/time Stamp field

3. Each of the text within " " as Field and each Field is separated by Comma

Thank you again.

[richgalloway]

This query will extract the ID and DateTime fields.

... | rex "(?<ID>.{6}).*?@\^@\^(?<DateTime>\d\d\d\d\/\d\d\/\d\d@\^@\^\d\d:\d\d:\d\d)"

Getting the SQL fields is a bit trickier because the SQL command might vary significantly.

[SplunkDash]

Thank you so much again. One more quick question if it can be resolved.. when I used your codes

rex "(?<ID>.{6}).*?@\^@\^(?<DateTime>\d\d\d\d\/\d\d\/\d\d@\^@\^\d\d:\d\d:\d\d) to extract ID and DateTime Fields: ID looked as expected, but I got DateTime Field  "2021/06/30@^@^20:20:59" . Is there any ways we can have DateTime Field like "2021/06/30 20:20:59"....without (@^@^) from this Event. Thank you so much, appreciate your support.

[richgalloway]

There are a few ways to do that, but all of them require additional steps.  Once the DateTime string is extracted from the event, we can edit it to remove unwanted characters.

| rex mode=sed field=DateTime "s/@\^//g"

or

| eval DateTime=replace(DateTime, "@\^", "")

Or we can extract the date and time separately then combine them.

| rex "(?<ID>.{6}).*?@\^@\^(?<Date>\d\d\d\d\/\d\d\/\d\d)@\^@\^(?<Time>\d\d:\d\d:\d\d)"
| eval DateTime=Date . Time

[SplunkDash]

Thank you so much greatly appreciate your help.

I totally agree that getting the SQL fields is a bit trickier and we will get lots of false alarms  in these extraction process. But, would it be possible go help me  to extract those fields even though SQL command might vary significantly. Thank you again, any help in these efforts will be highly appreciated.

[richgalloway]

Perhaps this will help with the SQL fields.

| rex "select (?<select>.*) into"
| rex field=select max_match=0 "(?<SQLField>[^,]+),"
| eval SQLField=mvmap(SQLField, trim(SQLField,"\""))

[richgalloway]

Is there a way?  Probably.  What exactly do you want to get out of the events?