Splunk Search
×

Join the Conversation

Without signing in, you're just watching from the sidelines. Sign in or Register to connect, share, and be part of the Splunk Community.
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
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

[Puzzles] Solve, Learn, Repeat: Character substitutions with Regular Expressions

This challenge was first posted on Slack #puzzles channelFor BORE at .conf23, we had a puzzle question which ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

[Puzzles] Solve, Learn, Repeat: Matching cron expressions

This puzzle (first published here) is based on matching timestamps to cron expressions.All the timestamps ...