How to match two values from a lookup table and a search

[WyldeRhoads]

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.

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.

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.

index=web sourcetype=login
earliest=-31d@d latest=@d
| bucket span=1m _time
| join username
[
| inputlookup 2015LoginReport.csv
]
| stats count by _time username

Thank you in advance!

[woodcock]

Like this:

 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

Now you have a full join with no discrimination.

For left join, continue with:

| search type = "30Days"

For right join, continue with:

| search type = "2015"

For outer join, continue with:

| numTypes = "1"

For inner join, continue with:

| numTypes = "2"

[somesoni2]

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.

index=web sourcetype=login  earliest=-31d@d latest=@d  [ | inputlookup 2015LoginReport.csv | table username ]
| bucket span=1m _time  | stats count by _time username

[WyldeRhoads]

Doesnt seem to work, I'm getting a "no results found".

[somesoni2]

Does the field name in the lookup table and the actual data match (case-sensitive)?

[WyldeRhoads]

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.

[jkat54]

Does this work better?

 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

[jkat54]

not sure why somesoni2's search doesnt work. it looks good based on the example search you provided.

Is your time picker set to all time?