Splunk Search

How to optimize my current search for better performance?

pjampani
New Member
index=*_alltime (sourcetype=*_data earliest=-1d@d latest=@d) 

|table estl_code_enr_stat estl_code_mrkt_offr_typ estl_date_enr_stat_eff estl_nbr_cardh_acct estl_curr_enr_stat_indic estl_rfrn_mrkt_offr_id

|eval Date2=strptime(estl_date_enr_stat_eff , "%m/%d/%Y")
|where estl_code_mrkt_offr_typ="R"

|where (estl_code_enr_stat="E" OR estl_code_enr_stat="P")

|fillnull value="N" estl_curr_enr_stat_indic

|eventstats MAX(Date2) as estl_date_enr_stat_eff_max_R  by estl_code_mrkt_offr_typ estl_nbr_cardh_acct estl_rfrn_mrkt_offr_id

|eval estl_date_enr_stat_eff_max_R =strftime(estl_date_enr_stat_eff_max_R , "%m/%d/%Y")
|rename estl_code_enr_stat as estl_code_enr_stat_R estl_date_enr_stat_eff as estl_date_enr_stat_eff_R estl_code_mrkt_offr_typ as estl_code_mrkt_offr_typ_R estl_curr_enr_stat_indic as estl_curr_enr_stat_indic_R estl_rfrn_mrkt_offr_id as estl_rfrn_mrkt_offr_id_R

|join type=outer estl_nbr_cardh_acct max=0 [search index=***** (sourcetype=***** (estl_code_mrkt_offr_typ=L)  latest=@d)
 |eval Date=strftime(_time,"%m/%d/%Y")
 |fillnull value="N" estl_curr_enr_stat_indic
 |table estl_code_enr_stat estl_code_mrkt_offr_typ estl_date_enr_stat_eff estl_nbr_cardh_acct estl_curr_enr_stat_indic estl_rfrn_mrkt_offr_id _time
 |eval Date=strptime(strftime(_time,"%Y-%m-%d"),"%Y-%m-%d" ) 
 |eval Date1=strptime(estl_date_enr_stat_eff,"%m/%d/%Y")
 |eval zip=mvzip(Date,Date1) |eval zip=mvzip(zip, estl_code_enr_stat)
 |stats max(zip) as zip by estl_code_mrkt_offr_typ  estl_nbr_cardh_acct estl_curr_enr_stat_indic estl_rfrn_mrkt_offr_id
 |eval zip=split(zip, ",") 
 |eval Date=mvindex(zip,0)
 |eval Date1=mvindex(zip,1)
 |eval estl_code_enr_stat=mvindex(zip,2)
 |eval _time=strftime(Date, "%Y-%m-%d") 
 |eval estl_date_enr_stat_eff=strftime(Date1, "%m/%d/%Y")
 |fields - zip Date 
 |where (estl_code_enr_stat="E" OR estl_code_enr_stat="P")

 |eventstats MAX(Date1) as estl_date_enr_stat_eff_max_l by estl_code_mrkt_offr_typ estl_nbr_cardh_acct estl_rfrn_mrkt_offr_id 
 |eval estl_date_enr_stat_eff_max_l =strftime(estl_date_enr_stat_eff_max_l , "%m/%d/%Y") | fields -  Date1 
|rename estl_code_enr_stat as estl_code_enr_stat_L estl_date_enr_stat_eff as estl_date_enr_stat_eff_L Date as Date_L estl_code_mrkt_offr_typ as estl_code_mrkt_offr_typ_L estl_curr_enr_stat_indic as estl_curr_enr_stat_indic_L estl_rfrn_mrkt_offr_id as estl_rfrn_mrkt_offr_id_L ]

| fillnull value=NULL estl_code_enr_stat_L, estl_curr_enr_stat_indic_L,estl_code_mrkt_offr_typ_L,estl_date_enr_stat_eff_L,estl_rfrn_mrkt_offr_id_L
|eval epochyesterday=relative_time(now(),"-1d@d")
|eval yesterday=strftime(epochyesterday,"%m%d%y")
|eval Splunk_Alert_Id="iFIND Pricing and Fees"."||"."LOC POTL R Type"."_".yesterday
|where ( (estl_curr_enr_stat_indic_R="Y" AND (estl_curr_enr_stat_indic_L="Y" OR estl_curr_enr_stat_indic_L="NULL") ))
|where ((estl_code_enr_stat_R!=estl_code_enr_stat_L)  OR (estl_code_enr_stat_R="P" AND estl_code_enr_stat_L="P" AND estl_date_enr_stat_eff_max_R!=estl_date_enr_stat_eff_max_l))

| rename estl_nbr_cardh_acct as nbr_card_acct
|append [|inputlookup LOC_R_TYPE.csv]
| map maxsearches=10000 search="search index=\"*****\" sourcetype=\"******\"  rec_type=\"ACH\" nbr_card_acct=\"$nbr_card_acct$\" | head 1 | table nbr_plastic code_ia_prod_id_demo nbr_card_acct nbr_player_acct1
| eval nbr_card_acct =\"$nbr_card_acct$\" 
| eval Splunk_Alert_Id =\"$Splunk_Alert_Id$\" 
| eval Date_L=\"$Date_L$\"
| eval estl_code_enr_stat_R=\"$estl_code_enr_stat_R$\"
| eval estl_code_mrkt_offr_typ_R=\"$estl_code_mrkt_offr_typ_R$\"
| eval estl_code_mrkt_offr_typ_L=\"$estl_code_mrkt_offr_typ_L$\"
| eval estl_date_enr_stat_eff_L=\"$estl_date_enr_stat_eff_L$\"
| eval estl_date_enr_stat_eff_R=\"$estl_date_enr_stat_eff_R$\"
|table Splunk_Alert_Id nbr_plastic Date_L code_ia_prod_id_demo nbr_card_acct nbr_player_acct1 estl_code_enr_stat_R estl_code_mrkt_offr_typ_R estl_code_mrkt_offr_typ_L estl_date_enr_stat_eff_L estl_date_enr_stat_eff_R"
|where (nbr_player_acct1="100261023")
|where (code_ia_prod_id_demo!="LU7" AND code_ia_prod_id_demo!="LU8" AND code_ia_prod_id_demo!="LU9" AND code_ia_prod_id_demo!="LVA" AND code_ia_prod_id_demo!="LVB" AND code_ia_prod_id_demo!="M8P" AND code_ia_prod_id_demo!="M8Q" AND code_ia_prod_id_demo!="M8R" AND code_ia_prod_id_demo!="M8S"
AND code_ia_prod_id_demo!="M8T" AND code_ia_prod_id_demo!="M8U" AND code_ia_prod_id_demo!="M8V" AND code_ia_prod_id_demo!="M8W" AND code_ia_prod_id_demo!="M8X" AND code_ia_prod_id_demo!="M8Y" AND code_ia_prod_id_demo!="QL5" AND code_ia_prod_id_demo!="QL6"
AND code_ia_prod_id_demo!="QL7" AND code_ia_prod_id_demo!="QL8" AND code_ia_prod_id_demo!="QL9" AND code_ia_prod_id_demo!="QMA" AND code_ia_prod_id_demo!="QMB" AND code_ia_prod_id_demo!="QMC" AND code_ia_prod_id_demo!="QMD" AND code_ia_prod_id_demo!="LV5"
AND code_ia_prod_id_demo!="LVD" AND code_ia_prod_id_demo!="LVK" AND code_ia_prod_id_demo!="LVR" AND code_ia_prod_id_demo!="LVX")
0 Karma

felipecerda
Path Finder

Try to move "what you know" to the left side of the query. For example the part where you have where (nbr_player_acct1="100261023"). Move that to line 46 before the table. Filter as early as possible.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...