Splunk Search

Field Extraction from SQL Coded Events

SplunkDash
Motivator

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

 

 

 

Labels (1)
Tags (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Yes, that's what I said.  😊

---
If this reply helps you, Karma would be appreciated.

View solution in original post

SplunkDash
Motivator

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

SplunkDash
Motivator

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

richgalloway_0-1630887234627.png

 

---
If this reply helps you, Karma would be appreciated.

SplunkDash
Motivator

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

 

0 Karma

SplunkDash
Motivator

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

 

malekmo_1-1630945331320.png

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

SplunkDash
Motivator

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

malekmo_1-1630974185712.png

 

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

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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,"\""))
---
If this reply helps you, Karma would be appreciated.

SplunkDash
Motivator

Thank you again.

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

malekmo_1-1631020235198.png

 

0 Karma

SplunkDash
Motivator

Awesome 😊!!! 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Yes, that's what I said.  😊

---
If this reply helps you, Karma would be appreciated.

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

SplunkDash
Motivator

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.

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

SplunkDash
Motivator

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.

 

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

SplunkDash
Motivator

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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,"\""))
---
If this reply helps you, Karma would be appreciated.

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...