Splunk Search

Can Some One Help With Query Optimization

New Member
index="ocdm" source IN ("covid_collection.csv","covid_collection_lcpr.csv","covid_collection_cl.csv", "covid_collection_cr.csv") 
|where AMOUNT!="NA" 
|eval latestdatestart=relative_time(now(),"-10d@d"),latestdateend=now(),parsedate=strptime(TXNDATE,"%m/%d/%Y")
|where parsedate >= latestdatestart AND parsedate < latestdateend  
|stats sum(AMOUNT) as latestdatevalue  by UiCountryCode parsedate|sort parsedate 
|eval latestdate=strftime(parsedate,"%m/%d/%Y"), latestdatevalue=round(latestdatevalue,2) 
| table UiCountryCode latestdate latestdatevalue lastday |eval latestdateformat=strptime(latestdate,"%m/%d/%Y"),lastdayformat = relative_time(latestdateformat,"-1d@d"),lastday=strftime(lastdayformat,"%m/%d/%Y")
|join UiCountryCode lastday [search index="ocdm" source IN ("covid_collection.csv","covid_collection_lcpr.csv","covid_collection_cl.csv", "covid_collection_cr.csv") 
  |where AMOUNT!="NA"
  |eval lastdaystart=relative_time(now(),"-11d@d"),lastdayend=relative_time(now(),"-1d@d"),parsedate=strptime(TXNDATE,"%m/%d/%Y")
  |where parsedate >= lastdaystart AND parsedate < lastdayend |stats sum(AMOUNT) as lastdayvalue  by UiCountryCode parsedate
  |sort parsedate
  |eval lastday=strftime(parsedate,"%m/%d/%Y"),lastdayvalue=round(lastdayvalue,2) 
  | table UiCountryCode lastday lastdayvalue
  |eval lastdayformat=strptime(lastday,"%m/%d/%Y"),lastweekformat = 
|join UiCountryCode lastweek[search index="ocdm" source IN ("covid_collection.csv","covid_collection_lcpr.csv","covid_collection_cl.csv", "covid_collection_cr.csv") 
  |where AMOUNT!="NA"
  |eval lastweekstart=relative_time(now(),"-17d@d"),lastweekend=relative_time(now(),"-6d@d"),parsedate=strptime(TXNDATE,"%m/%d/%Y")
  |where parsedate >= lastweekstart AND parsedate < lastweekend  
  |stats sum(AMOUNT) as lastweekvalue  by UiCountryCode parsedate
  |sort parsedate
  |eval lastweek=strftime(parsedate,"%m/%d/%Y"),lastweekvalue=round(lastweekvalue,2)
  | table UiCountryCode lastweek lastweekvalue]
|eval kpi="COLLECTION AMOUNT",_time=relative_time(now(),"-0d")
|fields - latestdateformat,- lastweekformat,- lastdayformat
0 Karma

Ultra Champion

why don't you stop use join and make flag like:

| eval flag=case(parsedate > relative_time(now(),"-10d@d") AND parsedate < now() ,"lastdate", ....)
| stats sum(AMOUNT) as AMOUNT by UiCountryCode parsedate flag
0 Karma


I've reformatted the query to make it easier to read.

Please explain what the query is attempting to do. What are the desired results? What makes you think it needs to be optimized?

If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Splunk Life | Happy Pride Month!

Happy Pride Month, Splunk Community! &#x1f308; In the United States, as well as many countries around the ...

SplunkTrust | Where Are They Now - Michael Uschmann

The Background Five years ago, Splunk published several videos showcasing members of the SplunkTrust to share ...

Admin Your Splunk Cloud, Your Way

Join us to maximize different techniques to best tune Splunk Cloud. In this Tech Enablement, you will get ...