https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276327#M83343 <P>Does this work better?</P> <PRE><CODE> index=web sourcetype=login earliest=-31d@d latest=@d | bucket span=1m _time | join max=0 type=left usetime=false username [ | inputlookup 2015LoginReport.csv ] | stats count by _time username </CODE></PRE> Tue, 31 May 2016 17:52:12 GMT jkat54 2016-05-31T17:52:12Z https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276321#M83337 <P>I have a lookup table with a list of usernames that have logged in to a website last year in 2015, and I'm trying to match logins from another search with any usernames that exist in that lookup table for the past 30 days. </P> <P>I tried the below search, but it doesn't quite work as expected... well, it produces an output but only for the current "today"... and I know the matched list should be much greater.</P> <P>The 2015LoginReport.csv lookup contains only a single column of username which matches the format of the index=web sourcetype=login search, and i'm able to run a | inputlookup 2015LoginReport.csv which shows the list... so there are no issues with the CSV.</P> <PRE><CODE>index=web sourcetype=login earliest=-31d@d latest=@d | bucket span=1m _time | join username [ | inputlookup 2015LoginReport.csv ] | stats count by _time username </CODE></PRE> <P>Thank you in advance!</P> Tue, 31 May 2016 14:56:15 GMT https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276321#M83337 WyldeRhoads 2016-05-31T14:56:15Z https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276322#M83338 <P>The join uses subsearch and the subsearch has a limit of 10K rows which may be skewing the result. How many matches you're getting right now? also, give this a try.</P> <PRE><CODE>index=web sourcetype=login earliest=-31d@d latest=@d [ | inputlookup 2015LoginReport.csv | table username ] | bucket span=1m _time | stats count by _time username </CODE></PRE> Tue, 31 May 2016 15:38:28 GMT https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276322#M83338 somesoni2 2016-05-31T15:38:28Z https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276323#M83339 <P>Doesnt seem to work, I'm getting a "no results found". </P> Tue, 31 May 2016 16:13:54 GMT https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276323#M83339 WyldeRhoads 2016-05-31T16:13:54Z https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276324#M83340 <P>not sure why somesoni2's search doesnt work. it looks good based on the example search you provided.</P> <P>Is your time picker set to all time?</P> Tue, 31 May 2016 16:31:29 GMT https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276324#M83340 jkat54 2016-05-31T16:31:29Z https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276325#M83341 <P>Does the field name in the lookup table and the actual data match (case-sensitive)? </P> Tue, 31 May 2016 16:33:52 GMT https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276325#M83341 somesoni2 2016-05-31T16:33:52Z https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276326#M83342 <P>yes they match, they are just ID numbers. I'm getting some matches with my OP search, but its only producing 100 or so matches for today, even though i can eyeball far more matches.</P> Tue, 31 May 2016 17:40:38 GMT https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276326#M83342 WyldeRhoads 2016-05-31T17:40:38Z https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276327#M83343 <P>Does this work better?</P> <PRE><CODE> index=web sourcetype=login earliest=-31d@d latest=@d | bucket span=1m _time | join max=0 type=left usetime=false username [ | inputlookup 2015LoginReport.csv ] | stats count by _time username </CODE></PRE> Tue, 31 May 2016 17:52:12 GMT https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276327#M83343 jkat54 2016-05-31T17:52:12Z https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276328#M83344 <P>Like this:</P> <PRE><CODE> index=web sourcetype=login earliest=-31d@d latest=@d | eval type="30Days" | appendpipe [| inputlookup 2015LoginReport.csv | eval type="2015"] | stats dc(type) AS numTypes values(*) AS * BY type </CODE></PRE> <P>Now you have a full join with no discrimination.</P> <P>For left join, continue with:</P> <PRE><CODE>| search type = "30Days" </CODE></PRE> <P>For right join, continue with:</P> <PRE><CODE>| search type = "2015" </CODE></PRE> <P>For outer join, continue with:</P> <PRE><CODE>| numTypes = "1" </CODE></PRE> <P>For inner join, continue with:</P> <PRE><CODE>| numTypes = "2" </CODE></PRE> Wed, 01 Jun 2016 01:06:21 GMT https://community.splunk.com/t5/Splunk-Search/How-to-match-two-values-from-a-lookup-table-and-a-search/m-p/276328#M83344 woodcock 2016-06-01T01:06:21Z