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!
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
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
Perfect, exactly what I was looking for, thanks!