Splunk Search

Issues of Defining Fields from Split Row within Extracted Events

SplunkDash
Motivator

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.

 

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

ITWhisperer
SplunkTrust
SplunkTrust

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)

 

View solution in original post

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

SplunkDash
Motivator

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

like 

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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)

 

SplunkDash
Motivator

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.

 

malekmo_1-1631109206289.png

 

 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...