Can Some One Help With Query Optimization

rakesh868852914 — Tue, 19 May 2020 10:37:03 GMT

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

Re: Can Some One Help With Query Optimization

richgalloway — Tue, 19 May 2020 13:12:22 GMT

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?

Re: Can Some One Help With Query Optimization

to4kawa — Tue, 19 May 2020 14:14:22 GMT

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

topic Re: Can Some One Help With Query Optimization in Splunk Search

Can Some One Help With Query Optimization

Re: Can Some One Help With Query Optimization

Re: Can Some One Help With Query Optimization