Why don't charts display from a base search on a dashboard when 'Open in search' does work?

[wcooper003]

I've seen other's ask this repeatedly - why do post-processing base searches not work in a dashboard, but they work fine when you click on 'Open in search'? I've tried a few approaches listed in other Splunk Answers (e.g., quoting fields) but I still can't get my dashboard to work with the base (post-processing) searches. Sometimes they work and sometimes they don't, it's extremely frustrating and buggy. I have another base search on the dashboard that calls the same 'ticket_base' using a lookup in the same way, and those charts work fine. Here's my code for the base searches that aren't working:

  <search id="ticket_base">
    <query>index=sm9_us source=interaction AFFECTED_ITEM="$service$"              
            | fields INTERACTION_ID OPEN_TIME CLOSE_TIME KPF_ID HANDLE_TIME TERRITORY SUBCATEGORY OPERATOR_ID
            | dedup INTERACTION_ID</query>
    <earliest>$earliest$</earliest>
    <latest>$latest$</latest>
  </search>

  <search base="ticket_base" id="contacts_base">
    <query>
      | search $territory$
      | lookup sm9_contacts_lookup OPERATOR_ID OUTPUT LOCATION TITLE
    </query>
  </search>

And here's one of the searches that is using the above base search:

    <panel>
      <chart>
        <title>Ticket Volume by User Location</title>
        <search base="contacts_base">
          <query>
            | lookup sm9_locations_lookup  LOCATION OUTPUT CITY
            | stats count as "Total Volume" by CITY
            | sort - "Total Volume"
            | head 10
            </query>
        </search>
      </chart>
    </panel>

On the dashboard, it says:
Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table.

But 'Open in search' will run it fine.

Anyone have thoughts on how to get this specific set of base searches working?

Thanks

[wcooper003]

I finally got the post-processing chained searches working - what I did was create a smaller kvstore lookup specifically for this search. It took the first lookup from ~500K rows to ~100K rows and 10 to 3 fields, and now the searches work OK. So my suspicion is that there was some issue with hitting default limits in the backend where the very large lookup wasn't working in the post-processing (chained) search.

[DalJeanis]

Verify that these fields -- LOCATION and CITY -- spelled and capitalized that way, are in the lookup table called sm9_locations_lookup.

[wcooper003]

Yes they're spelled correctly. The search runs fine when I click 'Open in search' on the same chart panel that says:

Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table.

[niketn]

Ideally, Post Processing is meant for posting only stats data (in other words when base search uses a transforming command). You would see no results with Post Processing or data getting dropped if you push fields from raw events without using any transforming commands on them. Refer to the the limitation and performance considerations for post processing.

https://docs.splunk.com/Documentation/Splunk/6.6.0/Viz/Savedsearches#Post-process_searches_2

Having said that after the dedup command pipe the following to see if it works:

| table _time INTERACTION_ID OPEN_TIME CLOSE_TIME KPF_ID HANDLE_TIME TERRITORY SUBCATEGORY OPERATOR_ID
| fields *

If this does not work see total no of events in Base Search and also check whether you can run transforming commands like stats in the base search or not.

[wcooper003]

Do you think there could be issues because the lookup in the post-process search is large (300K rows)? Even if I include a stats command prior to the lookup in the post-process command, very similar to the "Chained post-process searches" example in the documentation, it still doesn't run.

However, if I replace the lookup command with a join+inputlookup it runs, just painfully slow. It really doesn't like that specific lookup file.

Well i've spent 6hrs on this now, maybe it's time to switch back to a raw search for each chart individually.

[niketn]

For me | field * as the final command in base search had worked, but it was still a performance overload. As the documentation points out unless you can convert to transforming command first, you are better off re-running the searches for separate visualizations.

Looking at queries, you have used recursive post-processing without transforming commands. Have you used "ticket_base" search at any place other than <search base="ticket_base" id="contacts_base">?

If not merge contact_base and ticket_base as single search.
If you have then use | field * in both of them.

There are other performance tips that you should see if you can accommodate:

(1) Instead of performing | search $territory$ you should filter results upfront if you can i.e.

 index=sm9_us source=interaction AFFECTED_ITEM="$service$"  $territory$

If you can't do this, you would be better off running independent searches instead of post processing, since filtering only required data in the base search will give you optimal performance.

(2) Instead of performing lookup on events run stats first and then lookup (Refer to following lookup optimization documentation: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Lookup#Optimizing_your_lookup_sea...)

 | stats count as "Total Volume" by LOCATION 
 | lookup sm9_locations_lookup  LOCATION OUTPUT CITY
 | stats sum("Total Volume") as "Total Volume" by CITY

(3) Finally as Post Processing documentation mentions, in cases you are better off running separate searches rather than pushing all raw events from one search to another.

[wcooper003]

Forgot to answer you're other question - yes there were other post-processing searches using ticket_base, that included stats and lookup commands. The raw data forked out to 6 charts total.

[wcooper003]

Thanks for the feedback. I intentionally put the $territory$ filter further down in a post-processing search so that if users change that filter (a multiselect), the data loads faster, since it apparently doesn't run the base search to return the raw data. Is this interpretation correct? It appears it is.

I finally got the post-processing working, what I did was create a smaller lookup specifically for this search - it took the first lookup from ~500K rows to ~100K rows and with ~30% of the fields, and now the searches work OK. So my suspicion is that there was some issue with default limits in the backend where the very large lookup wasn't working in the post-processing (chained) search.

[wcooper003]

Ok thanks for the link to the documentation. I'm clearly using base searches incorrectly, but I like the option of pulling the raw data only once for a dashboard, not having to pull it separately for each chart.

I tried the "| table xyz | fields *" approach and that didn't work. Trying a bunch of permutations now, still nothing working.

I think the moral of the story is i'm using base searches inappropriately, and they do weird things when used inappropriately.