Hi,
I have the following search:
| inputlookup work_locations
| fields work_location
| join type=left work_location
[ search index=_internal latest=$token_carryover_latest$
| search type="Incident"
| search status="Pending*" OR status="In Progress"
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count as "carryover1" by work_location ]
| join type=left work_location
[ search index=_internal
| where _time <= $token_carryover_latest$ AND date_restored_e >= $token_carryover_latest$
| search type="Incident"
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count as "carryover2" by work_location ]
| join type=left work_location
[ search index=_internal earliest=$token_currentMonth_earliest$ latest=@d
| search type="Incident"
| search status="*"
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count as new_mtd by work_location]
| join type=left work_location
[ search index=_internal
| search type="Incident"
| search status="Resolved" OR status="Closed"
| where date_restored_e >= $token_resolvedMonth_earliest$ AND date_restored_e <= $token_resolvedMonth_latest$
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count as resolved_mtd1 by work_location]
| join type=left work_location
[ search index=_internal earliest=$token_currentMonth_earliest$ latest=$token_currentMonth_latest$
| search type="Incident"
| search status="Cancelled"
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count as resolved_mtd2 by work_location]
| join type=left work_location
[| search index=_internal
| search type="Incident"
| search status="*"
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count(eval(_time >= relative_time(now(), "-3d@d") AND _time <= relative_time(now(), "-2d@d"))) as new_a, count(eval(_time >= relative_time(now(), "-2d@d") AND _time <= relative_time(now(), "-1d@d"))) as new_b, count(eval(_time >= relative_time(now(), "-1d@d") AND _time <= relative_time(now(), "@d"))) as new_c by work_location]
| join type=left work_location
[ search index=_internal
| search type="Incident"
| search status="Resolved" OR status="Closed"
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count(eval(date_restored_e >= relative_time(now(), "-3d@d") AND date_restored_e <= relative_time(now(), "-2d@d"))) as resolved_a1, count(eval(date_restored_e >= relative_time(now(), "-2d@d") AND date_restored_e <= relative_time(now(), "-1@d"))) as resolved_b1, count(eval(date_restored_e >= relative_time(now(), "-1d@d") AND date_restored_e <= relative_time(now(), "@d"))) as resolved_c1 by work_location ]
| join type=left work_location
[ search index=_internal
| search type="Incident"
| search status="Cancelled"
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count(eval(_time >= relative_time(now(), "-3d@d") AND _time <= relative_time(now(), "-2d@d"))) as resolved_a2, count(eval(_time >= relative_time(now(), "-2@d") AND _time <= relative_time(now(), "-1d@d"))) as resolved_b2, count(eval(_time >= relative_time(now(), "-1@d") AND _time <= relative_time(now(), "@d"))) as resolved_c2 by work_location ]
| join type=left work_location
[ search index=_internal earliest=1522540800 latest=-3d@d
| search type="Incident"
| search status="Pending*" OR status="In Progress"
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count(eval(_time <= relative_time(now(), "-3d@d"))) as open_a1, count(eval(_time <= relative_time(now(), "-1d@d"))) as open_b1 by work_location]
| join type=left work_location
[ search index=_internal earliest=1522540800 latest=-2d@d
| where type="Incident"
| where date_restored_e > relative_time(now(), "-2d@d")
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count as "open_a2" by work_location]
| join type=left work_location
[ search index=_internal earliest=1522540800 latest=@d
| where type="Incident"
| where date_restored_e > relative_time(now(), "@d")
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count as "open_b2" by work_location]
| join type=left work_location
[ search index=_internal earliest=1522540800 latest=@d
| search type="Incident"
| search status="Pending*" OR status="In Progress"
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| dedup ticket_no
| stats count as "open_c" by work_location ]
| eval carryover = if(isnotnull(carryover2), carryover1 + carryover2, carryover1)
| eval resolved_mtd = if(isnotnull(resolved_mtd2), resolved_mtd1 + resolved_mtd2, resolved_mtd1)
| eval resolved_a = if(isnotnull(resolved_a2), resolved_a1 + resolved_a2, resolved_a1)
| eval resolved_b = if(isnotnull(resolved_b2), resolved_b1 + resolved_b2, resolved_b1)
| eval resolved_c = if(isnotnull(resolved_c2), resolved_c1 + resolved_c2, resolved_c1)
| eval open_a = if(isnotnull(open_a2), open_a1 + open_a2, open_a1)
| eval open_b = if(isnotnull(open_b2), open_b1 + open_b2, open_b1)
| eval delta = open_c - open_b
| addcoltotals labelfield=work_location label="SUB TOTAL"
| append
[| makeresults
| eval work_location="Eggsblain"]
| table work_location, carryover, new_mtd, resolved_mtd, new_a, resolved_a, open_a, new_b, resolved_b, open_b, new_c, resolved_c, open_c, delta
| fillnull carryover, new_mtd, resolved_mtd, new_a, resolved_a, open_a, new_b, resolved_b, open_b, new_c, resolved_c, open_c, delta
| rename work_location as Tower, carryover as "Carryover Month Open Incidents", new_mtd as "Month New MTD", resolved_mtd as "Month Resolved MTD", new_a as "New from Before Yesterday", resolved_a as "Resolved from Before Yesterday", open_a as "Open from Before Yesterday", new_b as "New from Yesterday", resolved_b as "Resolved from Yesterday", open_b as "Open from Yesterday", new_c as "New from Today", resolved_c as "Resolved from Today", open_c as "Open from Today",delta as "Delta"
Loading times take up as much as 5 minutes.
I am using an inputlookup and join because I need to present the work_locations in a specific order.
What can I do to make the search more efficient?
Thank you.
... View more