Here is the query below- for @DalJeanis. It looks pretty messy and the reason I have weird things going on with my location information is because for any location that does not match my lookup- location, Country, and Region to be filled as Unknown. And unfortunately, I was getting a weird error with using a lookup table and setting a default value so I had to do it manually.
Are there any ways to improve the efficiency of this search and make it run quicker?
index=it_snow_call_kiosk_logs_weekly date_month=August date_year=2017 (assignment_group="") | dedup number | fillnull value="UNKNOWN" location | eval regionblank= "UNKNOWN" | eval countryblank= "UNKNOWN" | eval locationblank="UNKNOWN" | lookup CurrentSiteInfo.csv location| eval site=coalesce(location2,locationblank) | eval Region=coalesce(Region,regionblank)| eval Country=coalesce(Country,countryblank) | search ((Region="") (Country="") (site="")) |stats count as Tickets by contact_type | join overwrite=false contact_type [search index=it_snow_call_kiosk_logs_weekly earliest="6/01/2017:00:00:00" latest="12/31/2017:24:00:00" (assignment_group="") | dedup number | fillnull value="UNKNOWN" location | eval regionblank= "UNKNOWN" | eval countryblank= "UNKNOWN" | eval locationblank="UNKNOWN" | lookup CurrentSiteInfo.csv location| eval site=coalesce(location2,locationblank) | eval Region=coalesce(Region,regionblank)| eval Country=coalesce(Country,countryblank) | search ((Region="") (Country="") (site=""))| bucket _time span=1mon | stats count as Tickets by contact_type _time | stats avg(Tickets) as Baseline by contact_type | eval Baseline = round(Baseline,0)] | eval "Baseline Variance" = Tickets - Baseline | join overwrite=false contact_type [search index=it_snow_call_kiosk_logs_weekly earliest=-3mon@mon (assignment_group="") | dedup number | fillnull value="UNKNOWN" location | eval regionblank= "UNKNOWN" | eval countryblank= "UNKNOWN" | eval locationblank="UNKNOWN" | lookup CurrentSiteInfo.csv location| eval site=coalesce(location2,locationblank) | eval Region=coalesce(Region,regionblank)| eval Country=coalesce(Country,countryblank) | search ((Region="") (Country="") (site="")) | bucket _time span=1mon | stats count as Tickets by contact_type _time | stats avg(Tickets) as Average by contact_type | eval Average = round(Average,0)] | eval "Average Variance" = Tickets - Average | table contact_type Tickets Baseline "Baseline Variance" Average "Average Variance" | addcoltotals | sort 0 Tickets
... View more