How to extract deleted count values from the below csv data and add them as a separate field?

Renunaren — Wed, 30 Aug 2023 16:14:54 GMT

Dataframe row : {"_c0":{"0":"deleted_count","1":"18","2":"8061","3":"0","4":"366619","5":"2","6":"1285","7":"2484","8":"1705","9":"1517","10":"12998","11":"13","12":"57","13":"0","14":"0","15":"0","16":"0","17":"1315","18":"0","19":"0","20":"0","21":"0","22":"0","23":"410973","24":"18588725","25":"0","26":"0","27":"0","28":"0","29":"25238"},"_c1":{"0":"load_date","1":"2023-08-28","2":"2023-08-28","3":"2023-08-28","4":"2023-08-28","5":"2023-08-28","6":"2023-08-28","7":"2023-08-28","8":"2023-08-28","9":"2023-08-28","10":"2023-08-28","11":"2023-08-28","12":"2023-08-28","13":"2023-08-28","14":"2023-08-28","15":"2023-08-28","16":"2023-08-28","17":"2023-08-28","18":"2023-08-28","19":"2023-08-28","20":"2023-08-28","21":"2023-08-28","22":"2023-08-28","23":"2023-08-28","24":"2023-08-28","25":"2023-08-28","26":"2023-08-28","27":"2023-08-28","28":"2023-08-28","29":"2023-08-28"},"_c2":{"0":"redelivered_count","1":"0","2":"1","3":"0","4":"0","5":"0","6":"0","7":"204","8":"0","9":"0","10":"0","11":"0","12":"0","13":"0","14":"0","15":"0","16":"0","17":"0","18":"0","19":"0","20":"0","21":"0","22":"0","23":"0","24":"9293073","25":"0","26":"0","27":"0","28":"0","29":"0"},"_c3":{"0":"table_name","1":"pc_dwh_rdv.gdh_ls2lo_s99","2":"pc_dwh_rdv.gdh_spar_s99","3":"pc_dwh_rdv.cml_kons_s99","4":"pc_dwh_rdv.gdh_tf3tx_s99","5":"pc_dwh_rdv.gdh_wechsel_s99","6":"pc_dwh_rdv.gdh_revolvingcreditcard_s99","7":"pc_dwh_rdv.gdh_phd_s99","8":"pc_dwh_rdv.gdh_npk_s99","9":"pc_dwh_rdv.gdh_npk_s98","10":"pc_dwh_rdv.gdh_kontokorrent_s99","11":"pc_dwh_rdv.gdh_gds_s99","12":"pc_dwh_rdv.gdh_dszins_s99","13":"pc_dwh_rdv.gdh_cml_vdarl_le_ext_s99","14":"pc_dwh_rdv.gdh_cml_vdarl_s99","15":"pc_dwh_rdv.gdh_avale_s99","16":"pc_dwh_rdv.gdh_spar_festzi_s99","17":"pc_dwh_rdv_gdh_monat.gdh_phd_izr_monthly_s99","18":"pc_dwh_rdv.gdh_orig_sparbr_daily_s99","19":"pc_dwh_rdv.gdh_orig_terming_daily_s99","20":"pc_dwh_rdv.gdh_orig_kredite_daily_s99","21":"pc_dwh_rdv.gdh_orig_kksonst_daily_s99","22":"pc_dwh_rdv.gdh_orig_baufi_daily_s99","23":"pc_dwh_rdv_creditcard.credit_card_s99","24":"pc_dwh_rdv_csw.fkn_security_classification_s99","25":"pc_dwh_rdv_loan_appl.ccdb_loan_daily_s99","26":"pc_dwh_rdv_loan_appl.leon_loan_monthly_s99","27":"pc_dwh_rdv_loan_appl.nospk_loan_daily_s99","28":"pc_dwh_rdv_partnrdata.fkn_special_target_group_s99","29":"pc_dwh_rdv_talanx.insurance_s99"}}

Re: Please help me in extraction of the deleted count values from the below csv data and add them as a separate field

yuanliu — Tue, 29 Aug 2023 08:02:08 GMT

This looks awfully like https://community.splunk.com/t5/Splunk-Search/How-to-extract-a-value-from-the-message-using-rex-command/m-p/652673/highlight/true#M225564, but the data fragment is somewhat more straight-forward. Let me give it a try using a similar method. (BTW, the data frame is not CSV, but a JSON object.)

You never indicated whether the cited data is the complete raw event. I will assume that it is in the following. I also assume that you are using Splunk 8 or higher so you can take advantage of JSON functions. Further more, I assume that you want to list each deleted_count with the corresponding table_name. An additional assumption used in both is that each message contains < 9999 table names.

| eval message = replace(_raw, "Dataframe row : ", "") | spath input=message | eval json0 = json_object() | foreach c0.* [eval json0 = json_set(json0, printf("%04d", <<MATCHSTR>>), '<<FIELD>>')] | eval json1 = json_object() | foreach c1.* [eval json1 = json_set(json1, printf("%04d", <<MATCHSTR>>), '<<FIELD>>')] | eval json2 = json_object() | foreach c2.* [eval json2 = json_set(json2, printf("%04d", <<MATCHSTR>>), '<<FIELD>>')] | eval json3 = json_object() | foreach c3.* [eval json3 = json_set(json3, printf("%04d", <<MATCHSTR>>), '<<FIELD>>')] | fields - _* c*.* message | eval frag = mvrange(1, mvcount(json_array_to_mv(json_keys(json0)))) | eval frag = mvmap(frag, printf("%04d", frag)) | eval jsontable = mvmap(frag, json_object(json_extract(json0, "0000"), json_extract(json0, frag), json_extract(json1, "0000"), json_extract(json1, frag), json_extract(json2, "0000"), json_extract(json2, frag), json_extract(json3, "0000"), json_extract(json3, frag))) | mvexpand jsontable | foreach table_name deleted_count load_date redelivered_count [ eval "<<FIELD>>" = json_extract(jsontable, "<<FIELD>>")] | table table_name deleted_count redelivered_count load_date

Your sample data should give

table_name	deleted_count	redelivered_count	load_date
pc_dwh_rdv.gdh_ls2lo_s99	18	0	2023-08-28
pc_dwh_rdv.gdh_spar_s99	8061	1	2023-08-28
pc_dwh_rdv.cml_kons_s99	0	0	2023-08-28
pc_dwh_rdv.gdh_tf3tx_s99	366619	0	2023-08-28
pc_dwh_rdv.gdh_wechsel_s99	2	0	2023-08-28
pc_dwh_rdv.gdh_revolvingcreditcard_s99	1285	0	2023-08-28
pc_dwh_rdv.gdh_phd_s99	2484	204	2023-08-28
pc_dwh_rdv.gdh_npk_s99	1705	0	2023-08-28
pc_dwh_rdv.gdh_npk_s98	1517	0	2023-08-28
pc_dwh_rdv.gdh_kontokorrent_s99	12998	0	2023-08-28
pc_dwh_rdv.gdh_gds_s99	13	0	2023-08-28
pc_dwh_rdv.gdh_dszins_s99	57	0	2023-08-28
pc_dwh_rdv.gdh_cml_vdarl_le_ext_s99	0	0	2023-08-28
pc_dwh_rdv.gdh_cml_vdarl_s99	0	0	2023-08-28
pc_dwh_rdv.gdh_avale_s99	0	0	2023-08-28
pc_dwh_rdv.gdh_spar_festzi_s99	0	0	2023-08-28
pc_dwh_rdv_gdh_monat.gdh_phd_izr_monthly_s99	1315	0	2023-08-28
pc_dwh_rdv.gdh_orig_sparbr_daily_s99	0	0	2023-08-28
pc_dwh_rdv.gdh_orig_terming_daily_s99	0	0	2023-08-28
pc_dwh_rdv.gdh_orig_kredite_daily_s99	0	0	2023-08-28
pc_dwh_rdv.gdh_orig_kksonst_daily_s99	0	0	2023-08-28
pc_dwh_rdv.gdh_orig_baufi_daily_s99	0	0	2023-08-28
pc_dwh_rdv_creditcard.credit_card_s99	410973	0	2023-08-28
pc_dwh_rdv_csw.fkn_security_classification_s99	18588725	9293073	2023-08-28
pc_dwh_rdv_loan_appl.ccdb_loan_daily_s99	0	0	2023-08-28
pc_dwh_rdv_loan_appl.leon_loan_monthly_s99	0	0	2023-08-28
pc_dwh_rdv_loan_appl.nospk_loan_daily_s99	0	0	2023-08-28
pc_dwh_rdv_partnrdata.fkn_special_target_group_s99	0	0	2023-08-28
pc_dwh_rdv_talanx.insurance_s99	25238	0	2023-08-28

The following is an emulation you can play with and compare with real data

| makeresults | eval _raw = "Dataframe row : {\"_c0\":{\"0\":\"deleted_count\",\"1\":\"18\",\"2\":\"8061\",\"3\":\"0\",\"4\":\"366619\",\"5\":\"2\",\"6\":\"1285\",\"7\":\"2484\",\"8\":\"1705\",\"9\":\"1517\",\"10\":\"12998\",\"11\":\"13\",\"12\":\"57\",\"13\":\"0\",\"14\":\"0\",\"15\":\"0\",\"16\":\"0\",\"17\":\"1315\",\"18\":\"0\",\"19\":\"0\",\"20\":\"0\",\"21\":\"0\",\"22\":\"0\",\"23\":\"410973\",\"24\":\"18588725\",\"25\":\"0\",\"26\":\"0\",\"27\":\"0\",\"28\":\"0\",\"29\":\"25238\"},\"_c1\":{\"0\":\"load_date\",\"1\":\"2023-08-28\",\"2\":\"2023-08-28\",\"3\":\"2023-08-28\",\"4\":\"2023-08-28\",\"5\":\"2023-08-28\",\"6\":\"2023-08-28\",\"7\":\"2023-08-28\",\"8\":\"2023-08-28\",\"9\":\"2023-08-28\",\"10\":\"2023-08-28\",\"11\":\"2023-08-28\",\"12\":\"2023-08-28\",\"13\":\"2023-08-28\",\"14\":\"2023-08-28\",\"15\":\"2023-08-28\",\"16\":\"2023-08-28\",\"17\":\"2023-08-28\",\"18\":\"2023-08-28\",\"19\":\"2023-08-28\",\"20\":\"2023-08-28\",\"21\":\"2023-08-28\",\"22\":\"2023-08-28\",\"23\":\"2023-08-28\",\"24\":\"2023-08-28\",\"25\":\"2023-08-28\",\"26\":\"2023-08-28\",\"27\":\"2023-08-28\",\"28\":\"2023-08-28\",\"29\":\"2023-08-28\"},\"_c2\":{\"0\":\"redelivered_count\",\"1\":\"0\",\"2\":\"1\",\"3\":\"0\",\"4\":\"0\",\"5\":\"0\",\"6\":\"0\",\"7\":\"204\",\"8\":\"0\",\"9\":\"0\",\"10\":\"0\",\"11\":\"0\",\"12\":\"0\",\"13\":\"0\",\"14\":\"0\",\"15\":\"0\",\"16\":\"0\",\"17\":\"0\",\"18\":\"0\",\"19\":\"0\",\"20\":\"0\",\"21\":\"0\",\"22\":\"0\",\"23\":\"0\",\"24\":\"9293073\",\"25\":\"0\",\"26\":\"0\",\"27\":\"0\",\"28\":\"0\",\"29\":\"0\"},\"_c3\":{\"0\":\"table_name\",\"1\":\"pc_dwh_rdv.gdh_ls2lo_s99\",\"2\":\"pc_dwh_rdv.gdh_spar_s99\",\"3\":\"pc_dwh_rdv.cml_kons_s99\",\"4\":\"pc_dwh_rdv.gdh_tf3tx_s99\",\"5\":\"pc_dwh_rdv.gdh_wechsel_s99\",\"6\":\"pc_dwh_rdv.gdh_revolvingcreditcard_s99\",\"7\":\"pc_dwh_rdv.gdh_phd_s99\",\"8\":\"pc_dwh_rdv.gdh_npk_s99\",\"9\":\"pc_dwh_rdv.gdh_npk_s98\",\"10\":\"pc_dwh_rdv.gdh_kontokorrent_s99\",\"11\":\"pc_dwh_rdv.gdh_gds_s99\",\"12\":\"pc_dwh_rdv.gdh_dszins_s99\",\"13\":\"pc_dwh_rdv.gdh_cml_vdarl_le_ext_s99\",\"14\":\"pc_dwh_rdv.gdh_cml_vdarl_s99\",\"15\":\"pc_dwh_rdv.gdh_avale_s99\",\"16\":\"pc_dwh_rdv.gdh_spar_festzi_s99\",\"17\":\"pc_dwh_rdv_gdh_monat.gdh_phd_izr_monthly_s99\",\"18\":\"pc_dwh_rdv.gdh_orig_sparbr_daily_s99\",\"19\":\"pc_dwh_rdv.gdh_orig_terming_daily_s99\",\"20\":\"pc_dwh_rdv.gdh_orig_kredite_daily_s99\",\"21\":\"pc_dwh_rdv.gdh_orig_kksonst_daily_s99\",\"22\":\"pc_dwh_rdv.gdh_orig_baufi_daily_s99\",\"23\":\"pc_dwh_rdv_creditcard.credit_card_s99\",\"24\":\"pc_dwh_rdv_csw.fkn_security_classification_s99\",\"25\":\"pc_dwh_rdv_loan_appl.ccdb_loan_daily_s99\",\"26\":\"pc_dwh_rdv_loan_appl.leon_loan_monthly_s99\",\"27\":\"pc_dwh_rdv_loan_appl.nospk_loan_daily_s99\",\"28\":\"pc_dwh_rdv_partnrdata.fkn_special_target_group_s99\",\"29\":\"pc_dwh_rdv_talanx.insurance_s99\"}}" ``` data emulation above ```

Re: Please help me in extraction of the deleted count values from the below csv data and add them as a separate field

ITWhisperer — Tue, 29 Aug 2023 08:09:39 GMT

| rex max_match=0 "(?<deleted_count>\{[^\}\{]+deleted_count[^\}]+\})" | rex max_match=0 field=deleted_count "\"\d+\":\"(?<count>\d+)" | eval count=sum(count)

topic How to extract deleted count values from the below csv data and add them as a separate field? in Splunk Search

How to extract deleted count values from the below csv data and add them as a separate field?

Re: Please help me in extraction of the deleted count values from the below csv data and add them as a separate field

Re: Please help me in extraction of the deleted count values from the below csv data and add them as a separate field