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!
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"
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
Doesnt seem to work, I'm getting a "no results found".
Does the field name in the lookup table and the actual data match (case-sensitive)?
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.
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
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?