Splunk Enterprise

Search result different for different search mode (fast, smart, verbose)

alvin_kwong
Engager

I am running my Splunk application on version 8.1.1. Several observations from the result when using different search modes to run on the same SPL.

I am having a tstats command to retrieve data from a specific index and further process with stats, lookup, eventstats, and streamstats commands. When the number of event is greater than 1M, the following issues are observed in different search mode,

  1. The number of the sum(count) is different
  2. The total number of rows in statistics tab is different
  3. Some of the column values displayed in another column (e.g. value belongs to field_13 is shown under column field_2)

 

Below is my masked SPL for further reference,

Spoiler
| tstats prestats=t count as count where (`index_macro`)
AND ("field_1"="I" OR "field_1"="T" OR "field_1"="O")
AND field_3="*express*"
AND field_4="*"
AND ("field_5"="*")
by "field_10",field_3, "field_5", "field_6", "field_7 1", "field_7 2", "field_7 3", "field_2"
| stats count by "field_10", field_3, "field_5", "field_6", "field_7 1", "field_7 2", "field_7 3", "field_2"
| eval field_7m = 'field_7 1'." ".'field_7 2'." ".'field_7 3'
| search field_7m="*"
| lookup watchlist_for_latest_field_3 "field_8" as field_3 OUTPUT "field_8", "field_9 (English)","field_9 (Chinese)","field_11"
| search "field_8" = "*"
| eval ts=strptime('field_10',"%Y-%m-%d %H:%M:%S")
| stats sum(count) as count, max(ts) as latest_event_time_by_field_12 by field_3, "field_2", "field_5", "field_6"
| eventstats sum(count) as field_12_cnt by field_3, "field_2", "field_5", "field_6"
| eventstats sum(count) as field_13_cnt by field_3, "field_2", "field_5"
| eventstats sum(count) as field_3_cnt by field_3, "field_2"
| eventstats dc("field_5") as total_no_field_13 by field_3, "field_2"
| eval field_3_for_sort = lower(field_3), field_3_addr_for_sort = lower('field_2'), field_13_for_sort = lower('field_5'), field_12_description_for_sort = lower('field_6')
| sort 0 - field_3_cnt, +field_3_for_sort, +field_3_addr_for_sort, field_13_cnt, +field_13_for_sort, field_12_cnt, latest_event_time_by_field_12, +field_12_description_for_sort
| streamstats dc("field_5") as rank_field_3 by field_3, "field_2"
| streamstats count as rank_by_field_3_cntry by field_3, "field_2", "field_5"
| where rank_field_3 <= 3 and rank_by_field_3_cntry <= 3
| eval "field_6" = "<".rank_by_field_3_cntry.">: ".'field_6'
| stats list("field_6") as "field_12 description (Top 3)", values(field_3_cnt) as "Total Number of field_3_cnt", values(total_no_field_13) as "Total Number of field_13" by field_3, "field_2", "field_5", field_13_cnt
| eval field_3_for_sort = lower(field_3), field_3_addr_for_sort = lower('field_2'), field_13_for_sort = lower('field_5')
| sort 0 - "Total Number of field_3_cnt", +field_3_for_sort, +field_3_addr_for_sort, field_13_cnt, +field_13_for_sort
| streamstats count as rank_by_field_3_after_group by field_3, "field_2"
| eval "field_5" = "<".rank_by_field_3_after_group.">: ".'field_5'
| lookup watchlist_for_latest_field_3 "field_8" as field_3 OUTPUT "field_8", "field_9 (English)","field_9 (Chinese)","field_11"
| rename "field_5" as "field_13 (Top 3)", "field_8" as "field_8 from Watchlist", field_3 as "field_3 (CAPTION)", "field_2" as "field_2 (CAPTION)"
| table "field_3 (CAPTION)", "field_2 (CAPTION)", "field_8 from Watchlist", "field_9 (Chinese)", "field_9 (English)", "field_11", "Total Number of field_3_cnt", "Total Number of field_13", "field_13 (Top 3)", "field_12 description (Top 3)"

P.S. I also referred to some of the post having the similar problem (e.g. https://community.splunk.com/t5/Splunk-Search/Why-does-search-in-fast-mode-return-different-results-...) while the solution seems cannot resolve my problem.

limits.conf:
[search_optimization::projection_elimination]
cmds_black_list = lookup

I target to run this SPL in a scheduled report and no method to force the scheduled report to run in verbose mode. May I know if there are any cures or workaround?

Labels (1)
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!