Can Some One Help With Query Optimization

[rakesh868852914]

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 = 
  relative_time(lastdayformat,"-7d@d"),lastweek=strftime(lastweekformat,"%m/%d/%Y")]
|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

[to4kawa]

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
...

[richgalloway]

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?