Got any tips on how I can shorten my search?

[dojiepreji]

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.

[VatsalJagani]

@dojiepreji

Here is some optimized search, but you can optimize it further as I'm writing this without having data so please mind the minor mistakes. One more point is wherever earliest and latest is not specified, if possible specify.

index=_internal type="Incident" (status="Pending*" OR status="In Progress") latest=$token_carryover_latest$ 
     | dedup ticket_no 
     | stats count as "carryover1" by work_location ]
| append
     [ search index=_internal type="Incident"  latest=$token_carryover_latest$
     | where date_restored_e >= $token_carryover_latest$  
     | dedup ticket_no 
     | stats count as "carryover2" by work_location ] 
| append
     [ search index=_internal type="Incident" status="*" earliest=$token_currentMonth_earliest$ latest=@d
     | dedup ticket_no 
     | stats count as new_mtd by work_location] 
| append
     [ search index=_internal type="Incident" (status="Resolved" OR status="Closed")
     | where date_restored_e >= $token_resolvedMonth_earliest$ AND date_restored_e <= $token_resolvedMonth_latest$ 
     | dedup ticket_no 
     | stats count as resolved_mtd1 by work_location] 
| append
     [ search index=_internal type="Incident" status="Cancelled" earliest=$token_currentMonth_earliest$ latest=$token_currentMonth_latest$
     | dedup ticket_no 
     | stats count as resolved_mtd2 by work_location] 
| append
     [| search index=_internal type="Incident" status="*" 
     | 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] 
| append
     [ search index=_internal  type="Incident" (status="Resolved" OR status="Closed") 
     | 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 ]
| append
     [ search index=_internal type="Incident" status="Cancelled" 
     | 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 ]
| append
     [ search index=_internal type="Incident" (status="Pending*" OR status="In Progress") earliest=1522540800 latest=-3d@d 
     | 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] 
| append
     [ search index=_internal earliest=1522540800 latest=-2d@d type="Incident" 
     | where date_restored_e > relative_time(now(), "-2d@d") 
     | dedup ticket_no 
     | stats count as "open_a2" by work_location]  
| append
     [ search index=_internal type="Incident" earliest=1522540800 latest=@d 
     | where date_restored_e > relative_time(now(), "@d") 
     | dedup ticket_no 
     | stats count as "open_b2" by work_location] 
| append
     [ search index=_internal type="Incident" (status="Pending*" OR status="In Progress") earliest=1522540800 latest=@d 
     | dedup ticket_no 
     | stats count as "open_c" by work_location ]
| eval work_location = if(work_location="Arkansaw" OR work_location="Kansaw", "Arkansaw/Kansaw", work_location)
| stats sum(*) as * by work_location
| lookup work_locations OUTPUT order
 | 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"]
| sort order
 | 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"

This search will also give you some idea about search optimization tips. Hope this helps!!!

[VatsalJagani]

Run the search in Fast Mode.

[mhoogcarspel_sp]

doesn't look like the join is just for the ordering of the work_locations, but for that you can just make a lookup with the order and sort in the end by the order field:

order, work_location
1, New York
2, LA
3, SF

search:

| lookup work_locations.csv work_location OUTPUT order | sort - order | fields - order

Not sure how the data looks, so this is mostly generic

search index=_internal 
     | search type="Incident" 
     | search status="Cancelled" 

can be just

search index=_internal type="Incident" status="Cancelled" 

probably (provided that that ticket_no is there on every field)

| dedup ticket_no | stats count as "open_b2" by work_location

can be

| stats dc(ticket_no) AS "open_b2" BY work_location

or at least limit fields that you're pulling through dedup, to just the ones you need

 | fields ticket_no work_location | dedup ticket_no

search after where can be optimized too

 | join type=left work_location 
     [ search index=_internal 
     | where _time <= $token_carryover_latest$ AND date_restored_e >= $token_carryover_latest$  
     | search type="Incident" 

becomes

 | join type=left work_location
     [ search index=_internal  type="Incident" 
     | where _time <= $token_carryover_latest$ AND date_restored_e >= $token_carryover_latest$ 

"where _time" can be latest or earliest

| join type=left work_location 
    [ search index=_internal 
    | where _time <= $token_carryover_latest$

->

| join type=left work_location 
    [ search index=_internal latest=$token_carryover_latest$

[mhoogcarspel_sp]

Also, consider making this an accelerated/scheduled report if running takes relatively long, it gets looked at often, and can be (slightly) behind