topic Re: Field Extraction from SQL Coded Events in Splunk Search

Field Extraction from SQL Coded Events

SplunkDash — Fri, 03 Sep 2021 21:48:26 GMT

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@^@^

Re: Field Extraction from SQL Coded Events

richgalloway — Fri, 03 Sep 2021 23:55:36 GMT

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

Re: Field Extraction from SQL Coded Events

SplunkDash — Sat, 04 Sep 2021 00:09:27 GMT

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.

Re: Field Extraction from SQL Coded Events

richgalloway — Sun, 05 Sep 2021 00:23:10 GMT

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.

Re: Field Extraction from SQL Coded Events

SplunkDash — Sun, 05 Sep 2021 02:47:14 GMT

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.

Re: Field Extraction from SQL Coded Events

richgalloway — Sun, 05 Sep 2021 12:57:20 GMT

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,"\""))

Re: Field Extraction from SQL Coded Events

SplunkDash — Sun, 05 Sep 2021 16:11:54 GMT

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.

Re: Field Extraction from SQL Coded Events

richgalloway — Sun, 05 Sep 2021 20:11:27 GMT

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.

Re: Field Extraction from SQL Coded Events

SplunkDash — Sun, 05 Sep 2021 20:17:18 GMT

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

Re: Field Extraction from SQL Coded Events

richgalloway — Mon, 06 Sep 2021 00:14:14 GMT

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

Re: Field Extraction from SQL Coded Events

SplunkDash — Mon, 06 Sep 2021 00:42:16 GMT

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

Re: Field Extraction from SQL Coded Events

richgalloway — Mon, 06 Sep 2021 12:56:59 GMT

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

Re: Field Extraction from SQL Coded Events

SplunkDash — Mon, 06 Sep 2021 16:25:11 GMT

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........... ?.

Re: Field Extraction from SQL Coded Events

richgalloway — Tue, 07 Sep 2021 00:11:54 GMT

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.

Re: Field Extraction from SQL Coded Events

SplunkDash — Tue, 07 Sep 2021 00:28:34 GMT

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,"\""))

Re: Field Extraction from SQL Coded Events

richgalloway — Tue, 07 Sep 2021 12:09:10 GMT

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,"\""))

Re: Field Extraction from SQL Coded Events

SplunkDash — Tue, 07 Sep 2021 13:11:19 GMT

Thank you again.

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

Re: Field Extraction from SQL Coded Events

richgalloway — Tue, 07 Sep 2021 14:25:14 GMT

Yes, that's what I said. 😊

Re: Field Extraction from SQL Coded Events

SplunkDash — Tue, 07 Sep 2021 14:31:03 GMT

Awesome 😊!!!

Re: Field Extraction from SQL Coded Events

SplunkDash — Tue, 07 Sep 2021 15:30:51 GMT

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.