Get last login time based upon a list of accounts in a CSV lookup file

[pfhendr]

I have a list of accounts that I wish to monitor in a csv file, say accounts.csv.

The file looks like:
userid,name,department
joeuser,Joe User,Accounting
jimuser,Jim User,Marketing
Janeuser,Jane User,Operations

And the logs are in the format of:

login=successful userid=joeuser

I would like to produce a table showing all the accounts in the csv file and when they last logged in. I can easily do that by using lookup in the search, like:

index=unix_security
| rename _time as last_login
| lookup accounts userid OUTPUT name AS Name, department AS Department
| table userid name department last_login

The trick is to create a table that shows all users, whether or not they logged in. If they never logged in then the last_login field would be blank (or some message like "not logged in"). I can get close by doing something like:

index=unix_security
| dedup userid
| rename _time as last_login
| append [inputlookup accounts.csv]
| table userid name department last_login
| sort -userid

But this will produce an extra entry for those accounts that have activity, like

joeuser Joe User Accounting

joeuser Nov 8, 2017 11:52:32

I believe I need to do a join somehow, but I can't seem to get the syntax right, I'm stilling learning this.

If anyone can provide some pointers that would be greatly appreciated.

Thanks!

[somesoni2 · Accepted solution]

Try like this

index=unix_security
| stats max(_time) as last_login  by userid
| append [ | inputlookup accounts.csv]
| stats values(*) as * by userid
| table userid name department last_login
| sort -userid

[pfhendr]

Perfect, exactly what I was looking for, thanks!