How do you find data/values in a lookup that do not exist in the logs?

[AnmolKohli]

We have a lookup file that has a list of series stored in a field — TS_SERIES_ID. We want to find the count of series that don't exist in logs and we used the below query to achieve the same.

| inputlookup tss_usage_csv | table TS_SERIES_ID
| search NOT [search index=web_timeseries  | mvexpand SeriesUT.series{}  | fields SeriesUT.series{} |rename SeriesUT.series{} as TS_SERIES_ID] | stats distinct_count(TS_SERIES_ID)

Issue : The results are getting truncated because we cannot have more than 10K results from the subsearch (We need this value to be around 300K and maximum can be set to 10500 in limits.conf).

Can you please let us know if there is any other way to achieve this?

Thanks

[kamlesh_vaghela]

@AnmolKohli

It should work. Can you please try below search?

index=web_timeseries 
| mvexpand SeriesUT.series{} 
| fields SeriesUT.series{} 
| rename SeriesUT.series{} as TS_SERIES_ID 
| eval temp2=1
| append 
[ 
 | inputlookup tss_usage_csv 
 | table TS_SERIES_ID 
 | eval a=1 | accum a | eval subset=a%50000 | stats values(TS_SERIES_ID) as TS_SERIES_ID by subset
 | eval temp1=1  
]
| stats values(temp1) as temp1 values(temp2) as temp2 by TS_SERIES_ID 
| where isnull(temp1) 
| stats count(TS_SERIES_ID) as count

Can you please let me know how you are comparing your data for verification?

[AnmolKohli]

The second query worked fine. Testing on different time ranges now to make sure the same is working as expected 🙂

[kamlesh_vaghela]

Great.. Finally...

Just let me know when you finished.

[AnmolKohli]

Running the query for last 7 days - output should be 352828 but using above query we are getting 352912 results. Manually picked 2 -3 series and they are getting reported in our query even though they have been accessed in last 7 days. Can you please help check?

[kamlesh_vaghela]

can you please share your search?

[AnmolKohli]

The query runs for 2 minutes and displays the correct results - 200K results but at the very last second the results drop to 24.

Results should be - (Subtract the values from below 2 queries)

index=web_timeseries
| mvexpand SeriesUT.series{}
| fields SeriesUT.series{}
| rename SeriesUT.series{} as TS_SERIES_ID
| stats distinct_count(TS_SERIES_ID) as count

| inputlookup tss_usage_csv
| table TS_SERIES_ID
| stats distinct_count(TS_SERIES_ID) as count

[AnmolKohli]

Also I get errors when running the query -

Subsearch produced 50000 results,truncating to maxout

[kamlesh_vaghela]

Can you please do the minor change in search?

OLD

| eval subset=a%50000 

NEW

| eval subset=a%49500 

[AnmolKohli]

Still getting 24 as output.The result changes at the very last second.

[kamlesh_vaghela]

Can you please try with below scenarios?

1) remove below condition.

 | where isnull(temp1) 

2) update condition.

old : | where isnull(temp1)

new: | where isnull(temp2)