topic Re: How do you find data/values in a lookup that do not exist in the logs? in Splunk Search

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

AnmolKohli — Tue, 29 Sep 2020 22:04:41 GMT

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

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

kamlesh_vaghela — Mon, 19 Nov 2018 08:47:46 GMT

HI @AnmolKohli

Can you please try this?

| inputlookup tss_usage_csv | table TS_SERIES_ID
| join type=left TS_SERIES_ID [ search index=web_timeseries | mvexpand SeriesUT.series{} | fields SeriesUT.series{} |rename SeriesUT.series{} as TS_SERIES_ID | eval temp=1 ] | where isnull(temp) | stats distinct_count(TS_SERIES_ID)

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

FrankVl — Tue, 29 Sep 2020 22:04:44 GMT

Try the search below. It simply appends the 2 lists of series together (so no expensive joins or restricted sub searches) and then counts the total occurrence of each TS_SERIES_ID, filtering for those serie IDs that came from the lookup and only occurred once in the combined list (ie they did not occur in the list from the logs).

| inputlookup tss_usage_csv 
| table TS_SERIES_ID
| eval source=lookup
| append [ 
        search index=web_timeseries | mvexpand SeriesUT.series{} | fields SeriesUT.series{} |rename SeriesUT.series{} as TS_SERIES_ID | eval source=logs ] 
| eventstats count by TS_SERIES_ID
| where source=lookup count=1

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

AnmolKohli — Mon, 19 Nov 2018 09:01:51 GMT

Not working.Getting below error :-

Subsearch produced 50000 results,truncating to maxout 50000

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

AnmolKohli — Mon, 19 Nov 2018 09:02:28 GMT

Not working.Getting below error :-

Subsearch produced 50000 results,truncating to maxout 50000

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

FrankVl — Mon, 19 Nov 2018 09:10:56 GMT

Ok, my bad, apparently that limit also applies when doing append.

Is the lookup also that big? Otherwise, try swapping log search and inputlookup:

  index=web_timeseries | mvexpand SeriesUT.series{} | fields SeriesUT.series{} |rename SeriesUT.series{} as TS_SERIES_ID | eval source=logs
 | append [ 
         | inputlookup tss_usage_csv 
 | table TS_SERIES_ID
 | eval source=lookup ] 
 | eventstats count by TS_SERIES_ID
 | where source=lookup count=1

Does that log search return only unique series, or is that full of duplicates? Then you could also throw in a dedup (as you're only interested in the unique series anyway.

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

AnmolKohli — Mon, 19 Nov 2018 09:15:23 GMT

Lookup has 571007 rows 😕

Any other way to achieve this?

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

AnmolKohli — Mon, 19 Nov 2018 09:41:34 GMT

The lookup has unique values.

Can we increase the limit for subsearch from existing 50K to 500K? Will that impact performance or affect other searches?

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

kamlesh_vaghela — Mon, 19 Nov 2018 09:54:40 GMT

HI @AnmolKohli

Can you please try below searches?

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

AND

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 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

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

AnmolKohli — Mon, 19 Nov 2018 11:15:28 GMT

Same error 😞

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

kamlesh_vaghela — Mon, 19 Nov 2018 11:33:54 GMT

Can you please provide Output from below searches?

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

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

AnmolKohli — Mon, 19 Nov 2018 11:37:12 GMT

Running the query for last 7 days :

Output of #1 - 215830

Output of #2 - 571007

Both values are correct. We just need to subtract them now (not sure how)

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

kamlesh_vaghela — Mon, 19 Nov 2018 12:22:57 GMT

HI @AnmolKohli

Can you please try below searches?

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  
]
| mvexpand TS_SERIES_ID
| stats values(temp1) as temp1 values(temp2) as temp2 by TS_SERIES_ID 
| where isnull(temp1) 
| stats count(TS_SERIES_ID) as count

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

osakachan — Mon, 19 Nov 2018 16:13:32 GMT

Why not use natural usage of lookup? If you add a control value for a match, you can do it easy.

| inputlookup mylook
| eval flag="y"
| outputlookup mylook

Then in the search:

| amazing search...
| lookup mylook ... OUTPUT flag
| search NOT flag="*"

It will run in the indexer because the late search but you will have an output of values that are not in the lookup.

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

AnmolKohli — Tue, 20 Nov 2018 04:47:03 GMT

Output is not correct.

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

AnmolKohli — Tue, 20 Nov 2018 04:51:41 GMT

Any other way to achieve this?

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

kamlesh_vaghela — Tue, 20 Nov 2018 05:21:55 GMT

@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?

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

AnmolKohli — Tue, 29 Sep 2020 22:05:29 GMT

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

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

AnmolKohli — Tue, 20 Nov 2018 05:34:18 GMT

Also I get errors when running the query -

Subsearch produced 50000 results,truncating to maxout

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

kamlesh_vaghela — Tue, 20 Nov 2018 05:52:29 GMT

Can you please do the minor change in search?

OLD

| eval subset=a%50000

NEW

| eval subset=a%49500