Issues of Defining Fields from Split Row within Extracted Events

SplunkDash — Tue, 07 Sep 2021 20:41:47 GMT

Hello

I have some issues with Defining Fields from Splitted Raw Data within an Event. Sample Events, Code used to split Raw Event, Output of Splitted Data, and My Issues are given below :

Raw Events:

DAS7CNB_L_0__20210630-23574912_5827.html@^@^2021/06/30@^@^23:57:49@^@^DAS7CNB@^@^select "tin","payer_tin","min"(case when "state" in( 'AA','AE','AP','AS','FM','GU','MH','MP','PR','PW','VI' ) then 1 else 0 end) as "f1065k1nonus","max"(case when "state" in( 'WA','OR','CA','AK','HI','MT','ID','WY','NV','UT','CO','AZ','NM' ) then 1 when "state" in( 'ND','MN','SD','IA','NE','KS','MO','WI','IL','IN','MI','OH' ) then 2 when "state" in( 'NY','PA','NJ','NH','VT','ME','MA','RI','CT' ) then 3 when "state" in( 'TX','OK','AR','LA','KY','TN','MS','AL','WV','DE','MD','VA','NC','DC','SC','GA','FL' ) then 4 when "state" in( 'AA','AE','AP','AS','FM','GU','MH','MP','PR','PW','VI' ) then 5 else 0 end) as "f1065k1maxdistoff","max"("interest") as "interest_f1065_k1","max"("guarpaymt") as "guarpaymt_f1065_k1","max"("ord_inc") as "ord_inc_f1065_k1","max"("othrental") as "othrental_f1065_k1","max"("realestate") as "realestate_f1065_k1","max"("royalties") as "royalties_f1065_k1","max"("section179") as "section179_f1065_k1" into #TEMP9A from "irmf_f1065_k1" where "tax_yr" = 2016 and "tin" > 0 and "tin_typ" in( 0,1,2,3 ) group by "tin","payer_tin"@^@^|DAS7CNB.#TEMP9A|cdwsa.IRMF_F1065_K1@^@^

My SQL Command:

eval SQLField=split(_raw,"@^@^")| table SQLField

Output of Splitted Data:

DAS7CNB_L_0__20210630-23574912_5827.html

2021/06/30

23:57:49

DAS7CNB

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" DAS7CNB.#TEMP9

DAS7CNB.#TEMP9A|cdsawsa.IRMF_F1065_K1

My Issues:

It splitted as expected.

But, I have some issues with defining text (please see the text in Bold right above My Issues:) values DAS7CNB.#TEMP9A as ID_DataFile and cdsawsa.IRMF_F1065_K1 as ID_DataTempFile

Thank you.....any help will be highly appreciated.

Re: Issues of Defining Fields from Split Row within Extracted Events

ITWhisperer — Tue, 07 Sep 2021 22:35:24 GMT

| eval datafiles=split(mvindex(SQLField,mvcount(SQLField)-2),"|") | eval ID_DataFile=mvindex(datafiles,1) | eval ID_DataTempFile=mvindex(datafiles,2)

Re: Issues of Defining Fields from Split Row within Extracted Events

SplunkDash — Wed, 08 Sep 2021 00:56:11 GMT

Thank you so much, appreciated your support.

It's working as expected when I have 2 Pipes "|" (as I mentioned)...but, some cases I found data is with one 1 Pipe "|"

For Example ......payer_tin"@^@^DAS7CNB.#TEMP9A|cdwsa.IRMF_F1065_K1@^@^ ....in that case First Value for ID_DataFile Field is missing.... Is there anything we can do so that I can have both values in both conditions....

payer_tin"@^@^|DAS7CNB.#TEMP9A|cdwsa.IRMF_F1065_K1@^@^ (with 2 Pipes)

AND

payer_tin"@^@^DAS7CNB.#TEMP9A|cdwsa.IRMF_F1065_K1@^@^ (with 1 Pipe)

Thank you so much again.

Re: Issues of Defining Fields from Split Row within Extracted Events

ITWhisperer — Wed, 08 Sep 2021 07:14:00 GMT

If there is no trailing pipe you could index from the end

| eval datafiles=split(mvindex(SQLField,mvcount(SQLField)-2),"|") | eval ID_DataFile=mvindex(datafiles,mvcount(datafiles)-2) | eval ID_DataTempFile=mvindex(datafiles,mvcount(datafiles)-1)

Re: Issues of Defining Fields from Split Row within Extracted Events

SplunkDash — Wed, 08 Sep 2021 13:54:34 GMT

Yes, it's working as expected. Thank you so much, appreciated you support.

Are there any ways I can include these codes into Extraction/Transformation (please see screenshot below) option and extract as fields from the Add New Field Under Field Extractions. Thank you so much again.

topic Re: Issues of Defining Fields from Split Row within Extracted Events in Splunk Search

Issues of Defining Fields from Split Row within Extracted Events

Re: Issues of Defining Fields from Split Row within Extracted Events

Re: Issues of Defining Fields from Split Row within Extracted Events

Re: Issues of Defining Fields from Split Row within Extracted Events

Re: Issues of Defining Fields from Split Row within Extracted Events