Thanks a lot!
Based on your suggestions, I currently have this:
source=A earliest=-12mon@mon | rename COMMENT as "limit to fields we need" | fields _time status number id type code code2 rep name ReportKey Count | rename code AS code2
| search NOT (status=*request* OR status=*pending* OR status=Error OR status="Expired") | dedup number sortby -id | search NOT (status="Voided" OR type="nullified")
| rename COMMENT as "Throw away records in the middle months" | where _time <= relative_time(now(),"-11mon@mon") OR _time >= relative_time(now(),"@mon")
| rename COMMENT as "Your lookup code goes here, as long as it doesn't depend on the ReportKey" | join code2 [search source=B earliest=-12y latest=now | dedup code2 sortby -_time] | join id [search source=C earliest=-12y latest=now | dedup rep sortby -_time]
| fillnull value="" name ReportKey | stats count(no) as Count by _time code2 name
| rename COMMENT as "Create two records for the year ago month to date, one record otherwise" | eval ReportKey=case(_time <= relative_time(now(),"-52w"), mvappend("Last year","Last Year by now"), _time <= now() - 3000000, "Last year", true(),"This month") | mvexpand ReportKey
| chart sum(Count) by name,ReportKey | fillnull | sort -"Last year", -"This month"| table name, "Last year", "This month", "Last Year by now"
That version perform better than what I originally had, which is great.
I can't quite figure out how to incorporate what you suggested for the last section. Any other recommendations on getting this to perform better would be greatly appreciated.
Thanks!
... View more