Finding Poor Performing Watchlist Queries

Products

• Carbon Black EDR

Issue/Introduction

How to find watchlist that require tuning. 

• Poor performance in the console when searching events or displaying the analysis page.
• Delay in alerts. 
• Unexpected tagged hits or expected results are not tagged. 

Environment

• Carbon Black EDR Server: All Versions

Cause

Syntax issues in the query. 

Resolution

1. Generate a report of long execution times from watchlists. 

   psql -p 5002 cb -c "COPY (select id, duration_ms, query_id, cluster_node_id, timestamp, result_count, original_query from solr_query_execution where query_source = 'watchlist' order by duration_ms desc) TO '/tmp/watchlist_execution_times.csv' with CSV HEADER;"

2. Generate a report of all enabled watchlists. 

   psql -p 5002 cb -c "COPY (select * from watchlist_entries where group_id = '-1' and enabled = 't') TO '/tmp/watchlist_entries.csv' with CSV HEADER;" 

3. Find watchlists with syntax issues (searching all search types). Use the 'watchlist_<id>' to find the watchlist name and query using the output created in step 2. The search query will be in URL encoding.

   cat var/log/cb/solr/debug.log | egrep 'path.*filewrite_md5.*crossproc_type.*blocked_md5' | grep 'watchlist_' |  sed -e 's/last_server_update:{[^}]*}//g' | sort -u

4. Review each long running or syntax query and edit the watchlist. 
5. Best Practices for Watchlist and Search Queries

Additional Information

• When editing a watchlist query, this will delete the original in postgres and create a new row with a new watchlist id.