Search by lookup file did not return all results

[timyong80]

I have a base search that produces a lookup that contains a million rows. When doing inputlookup, it displays the number of events correctly. Sample data below.

| inputlookup windows.csv

_time	time	host	event	user
2020-05-20T00:00:01.000+0000	5/20/2020	qatads23	4624	kristin
2020-05-22T00:00:12.000+0000	5/22/2020	sgpads30	4624	john
2020-05-24T00:00:12.000+0000	5/24/2020	sgpads30	4624	alice
2020-05-24T00:00:28.000+0000	5/24/2020	sgpads30	4624	mike
2020-05-24T00:00:33.000+0000	5/24/2020	sgpads30	4624	susan
2020-05-24T00:00:33.000+0000	5/24/2020	sgpads30	4624	lisa

However, when the search is being put into a dashboard, the number of results returned is lesser. I suspect that this is related to a truncate issue.  I noticed the bigger the lookup, the number of results returned reduces more even though the date range selected is the same from the time picker. For example, when I select the date range from May 20th-25th, the number of events returned is 180,000. However, as the lookup file grows, the same date range returns lesser events, maybe around 150,000.

Below is the example of the script to get the time format in order for the time picker to work with the time field when searching based on a lookup file. Reference - https://www.splunk.com/en_us/blog/tips-and-tricks/i-cant-make-my-time-range-picker-pick.html

| inputlookup windows.csv

| eval time=strftime(_time,"%Y-%m-%d")
| addinfo
| where _time>=info_min_time AND (_time<=info_max_time OR info_max_time="+Infinity")

| eval Start_Time=strftime(info_min_time,"%m/%d/%y")
| eval Stop_Time=strftime(info_max_time,"%m/%d/%y")
| fields _time host event user

| search $field1$ $field2$
| sort 0 _time

_time	time	host	event	user
2020-05-20T00:00:01.000+0000	5/20/2020	qatads23	4624	kristin
2020-05-22T00:00:12.000+0000	5/22/2020	sgpads30	4624	john
2020-05-24T00:00:12.000+0000	5/24/2020	sgpads30	4624	alice
2020-05-24T00:00:33.000+0000	5/24/2020	sgpads30	4624	susan

Noticed that 2 entries (mike and lisa) are missing from the original lookup. One thing to note is that when searching via index, there's no issue on the number of results returned except that it's very slow. Hence, I appended the results into a lookup file and search from that file instead. Theoretically, this should be faster. Below is the same search but querying from the index instead of lookup file.

index=windows

| fields _time host event user

| search $field1$ $field2$
| sort 0 _time

Any help would be much appreciated. Thank you.

[to4kawa]

| makeresults 
| eval _raw="_Time	time	host	event	user
2020-05-20T00:00:01.000+0000	5/20/2020	qatads23	4624	kristin
2020-05-22T00:00:12.000+0000	5/22/2020	sgpads30	4624	john
2020-05-24T00:00:12.000+0000	5/24/2020	sgpads30	4624	alice
2020-05-24T00:00:28.000+0000	5/24/2020	sgpads30	4624	mike
2020-05-24T00:00:33.000+0000	5/24/2020	sgpads30	4624	susan
2020-05-24T00:00:33.000+0000	5/24/2020	sgpads30	4624	lisa" 
| multikv forceheader=1 
| rename Time as _time
| table _time	time	host	event	user
| rename COMMENT as "this is your sample instead of inputlookup. from here, the logic"
| addinfo
| where strptime(_time,"%Y-%m-%dT%H:%M:%S.%3Q%z") >=info_min_time AND (strptime(_time,"%Y-%m-%dT%H:%M:%S.%3Q%z")<=info_max_time OR info_max_time="+Infinity")

Hi @timyong80 

your _time field is string. so you should convert it to epoch time.
How about this?

Please Accept and Upvote, if you'd like.