Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question

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

Renunaren
Loves-to-Learn Everything
‎08-28-2023 11:19 PM

 

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"}}

 

Labels (1)
Labels
Tags (3)
0 Karma
Reply

ITWhisperer
SplunkTrust
SplunkTrust
‎08-29-2023 01:09 AM 
| rex max_match=0 "(?<deleted_count>\{[^\}\{]+deleted_count[^\}]+\})"
| rex max_match=0 field=deleted_count "\"\d+\":\"(?<count>\d+)"
| eval count=sum(count)
0 Karma
Reply

yuanliu
SplunkTrust
SplunkTrust
‎08-29-2023 01:02 AM

This looks awfully like https://community.splunk.com/t5/Splunk-Search/How-to-extract-a-value-from-the-message-using-rex-comm..., 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_namedeleted_countredelivered_countload_date
pc_dwh_rdv.gdh_ls2lo_s991802023-08-28
pc_dwh_rdv.gdh_spar_s99806112023-08-28
pc_dwh_rdv.cml_kons_s99002023-08-28
pc_dwh_rdv.gdh_tf3tx_s9936661902023-08-28
pc_dwh_rdv.gdh_wechsel_s99202023-08-28
pc_dwh_rdv.gdh_revolvingcreditcard_s99128502023-08-28
pc_dwh_rdv.gdh_phd_s9924842042023-08-28
pc_dwh_rdv.gdh_npk_s99170502023-08-28
pc_dwh_rdv.gdh_npk_s98151702023-08-28
pc_dwh_rdv.gdh_kontokorrent_s991299802023-08-28
pc_dwh_rdv.gdh_gds_s991302023-08-28
pc_dwh_rdv.gdh_dszins_s995702023-08-28
pc_dwh_rdv.gdh_cml_vdarl_le_ext_s99002023-08-28
pc_dwh_rdv.gdh_cml_vdarl_s99002023-08-28
pc_dwh_rdv.gdh_avale_s99002023-08-28
pc_dwh_rdv.gdh_spar_festzi_s99002023-08-28
pc_dwh_rdv_gdh_monat.gdh_phd_izr_monthly_s99131502023-08-28
pc_dwh_rdv.gdh_orig_sparbr_daily_s99002023-08-28
pc_dwh_rdv.gdh_orig_terming_daily_s99002023-08-28
pc_dwh_rdv.gdh_orig_kredite_daily_s99002023-08-28
pc_dwh_rdv.gdh_orig_kksonst_daily_s99002023-08-28
pc_dwh_rdv.gdh_orig_baufi_daily_s99002023-08-28
pc_dwh_rdv_creditcard.credit_card_s9941097302023-08-28
pc_dwh_rdv_csw.fkn_security_classification_s991858872592930732023-08-28
pc_dwh_rdv_loan_appl.ccdb_loan_daily_s99002023-08-28
pc_dwh_rdv_loan_appl.leon_loan_monthly_s99002023-08-28
pc_dwh_rdv_loan_appl.nospk_loan_daily_s99002023-08-28
pc_dwh_rdv_partnrdata.fkn_special_target_group_s99002023-08-28
pc_dwh_rdv_talanx.insurance_s992523802023-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 ```

 

Reply
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...