We have a job which is getting terminated intermittently , even though when this search gets executed successfully it's run time is just 7-8 mins. Still it fails sometimes. Can someone suggest how we can avoid its failure:
(index=200004664_triumph_sidx sourcetype=tr_billed_summary_sidx (indc_wve_fnchg_sdx="N") (player_acct_nbr_sdx=100269999 OR player_acct_nbr_sdx=100269984 OR player_acct_nbr_sdx=100269963 OR player_acct_nbr_sdx=100269976 OR player_acct_nbr_sdx=100269950) (code_ia_prod_id_sdx!="KOB" AND code_ia_prod_id_sdx!="KOC" AND code_ia_prod_id_sdx!="KOD" AND code_ia_prod_id_sdx!="KOE" AND code_ia_prod_id_sdx!="KOF") earliest=-45d@d latest=-1d@d)
| fields - _raw
| fields nbr_plst_sdx code_trans_acct_sdx indc_cyc_rvlv_sdx amt_curr_outst_bal_sdx indc_wve_fnchg_sdx player_acct_nbr_sdx code_ia_prod_id_sdx
| eval date_prev=strftime(_time,"%m/%d/%Y")
| fillnull value="NULL" code_trans_acct_sdx indc_cyc_rvlv_sdx
| where code_trans_acct_sdx="NULL"
| rename indc_cyc_rvlv_sdx as indc_cyc_rvlv_prev amt_curr_outst_bal_sdx as amt_curr_outst_bal_prev
| JOIN nbr_plst_sdx
[ search
(index=200004664_triumph_sidx sourcetype=tr_billed_summary_sidx (indc_wve_fnchg_sdx="N") (player_acct_nbr_sdx=100269999 OR player_acct_nbr_sdx=100269984 OR player_acct_nbr_sdx=100269963 OR player_acct_nbr_sdx=100269976 OR player_acct_nbr_sdx=100269950) earliest=-1d@d latest=@d)
| fields - _raw
| fields nbr_plst_sdx code_trans_acct_sdx indc_cyc_rvlv_sdx amt_curr_outst_bal_sdx indc_wve_fnchg_sdx player_acct_nbr_sdx code_ia_prod_id_sdx
| eval date_current=strftime(_time,"%m/%d/%Y")
| fillnull value="NULL" code_trans_acct_sdx indc_cyc_rvlv_sdx
| where code_trans_acct_sdx="NULL" and amt_curr_outst_bal_sdx > 0
| rename indc_cyc_rvlv_sdx as indc_cyc_rvlv_current amt_curr_outst_bal_sdx as amt_curr_outst_bal_current]
| stats values(date_current) as date_current values(indc_cyc_rvlv_current) as indc_cyc_rvlv_current values(amt_curr_outst_bal_current) as amt_curr_outst_bal_current latest(date_prev) as date_prev latest(indc_cyc_rvlv_prev) as indc_cyc_rvlv_prev latest(amt_curr_outst_bal_prev) as amt_curr_outst_bal_prev values(player_acct_nbr_sdx) as player_acct_nbr values(indc_wve_fnchg_sdx) as indc_wve_fnchg values(code_ia_prod_id_sdx) as code_ia_prod_id by nbr_plst_sdx
| eval check_type=case((indc_cyc_rvlv_prev="N" AND indc_cyc_rvlv_current="N"),"T2T", (indc_cyc_rvlv_prev="Y" AND indc_cyc_rvlv_current="Y"),"R2R", (indc_cyc_rvlv_prev="N" AND indc_cyc_rvlv_current="Y"),"T2R",1=1,"NA")
| rename nbr_plst_sdx as nbr_plst
| where check_type != "NA"
| table nbr_plst player_acct_nbr code_ia_prod_id indc_wve_fnchg date_prev indc_cyc_rvlv_prev amt_curr_outst_bal_prev date_current indc_cyc_rvlv_current amt_curr_outst_bal_current check_type
| eval date_curr_epoch=strptime(date_current,"%m/%d/%Y")
| eval date_prev_epoch=strptime(date_prev,"%m/%d/%Y")
| eval date_diff=(date_curr_epoch-date_prev_epoch)/86400
| eval date_diff=round(date_diff)
| JOIN nbr_plst
[ search
index=triumph sourcetype=tr_billed_subbalance (nbr_plyr_acct=100269999 OR nbr_plyr_acct=100269984 OR nbr_plyr_acct=10026963 OR nbr_plyr_acct=100269976 OR nbr_plyr_acct=100269950) (code_prch_cadv="P") earliest=-1d@d latest=@d
| stats values(amt_ctd_fc) as amt_ctd_fc values(amt_adb) as amt_adb values(beg_dpr) as beg_dpr values(pct_stmt_dpr) as pct_stmt_dpr values(code_be_grace) as code_be_grace values(code_fe_grace) as code_fe_grace by nbr_plst prty_l2h]
| eval amt_adb = tonumber(amt_adb)
| eval amt_ctd_fc = tonumber(amt_ctd_fc)
| eval beg_dpr = tonumber(beg_dpr)
| eval pct_stmt_dpr = tonumber(pct_stmt_dpr)
| eval calc_fc=exact((amt_adb*pct_stmt_dpr*date_diff)/100)
| eval calc_fc_round=round(calc_fc,2)
| rex field=calc_fc "(?P<calc_fc>[0-9]*[.][0-9]{2})\d*"
| table * amt_ctd_fc amt_adb pct_stmt_dpr date_diff calc_fc calc_fc_round
| eval valid_type=case((check_type="T2T" AND amt_ctd_fc>0),"alert1",
(check_type="R2R" AND (amt_adb>0 AND amt_ctd_fc=0)),"alert2",
(check_type="R2R" AND (pct_stmt_dpr>0 AND amt_ctd_fc=0)),"alert3",
(check_type="R2R" AND (amt_adb>0 AND amt_ctd_fc!=calc_fc_round)),"alert4",
(check_type="R2R" AND (pct_stmt_dpr>0 AND amt_ctd_fc!=calc_fc_round)),"alert5",
(check_type="T2R" AND (amt_adb>0 AND amt_ctd_fc=0)),"alert6",
(check_type="T2R" AND (pct_stmt_dpr>0 AND amt_ctd_fc=0)),"alert7",
(check_type="T2R" AND (amt_adb>0 AND amt_ctd_fc!=calc_fc_round)),"alert8",
(check_type="T2R" AND (pct_stmt_dpr>0 AND amt_ctd_fc!=calc_fc_round)),"alert9",1=1,"NA")
| table * valid_type
| where valid_type="alert1" OR valid_type="alert2" OR valid_type="alert3" OR valid_type="alert4" OR valid_type="alert5" OR valid_type="alert6" OR valid_type="alert7" OR valid_type="alert8" OR valid_type="alert9"
| where calc_fc_round > 0
| fields - amt_curr_outst_bal_prev beg_dpr date_curr_epoch date_prev_epoch
| where (check_type!="T2T" AND code_fe_grace!=02)
| eval calc_fc_low = round(calc_fc-0.01,2)
| eval calc_fc_high = round(calc_fc+0.01,2)
| where (amt_ctd_fc > calc_fc_high) OR (amt_ctd_fc < calc_fc_low)
| rename amt_adb as "Average Daily Balance Amt" pct_stmt_dpr as "Statement DPR Percentage" amt_ctd_fc as "FC Assessed by Triumph" calc_fc_round as "Calculated FC" nbr_plst as "Plastic Number" amt_curr_outst_bal_current as "Current Outstanding Balance"
| eval epochyesterday=relative_time(now(),"-1d@d")
| eval yesterday=strftime(epochyesterday,"%m%d%y")
| eval Splunk_Alert_Id="iFIND Pricing and Fees"."||"."Grace_Monitoring"."_".yesterday
| fields - date_current date_diff date_prev epochyesterday yesterday valid_type
Manual execution of this search is working fine. I Don't have to execute it in background. It takes just 7-8 mins to execute. But when I execute it through scheduling it sometimes fail after 5 mins. Plus please brief on how can I optimize this?
Hi @VK_27,
this is a very long search and it probably goes in timeout.
So you can execute it in background and see results when finished.
You can activate background execution from job menu button
Anyway your search is a very long search and probably you could try to optimize it e.g. elimining the join command, or at least reducing the time period (45 days).
Ciao.
Giuseppe