Help on query for user login info.

[sanju005ind]

I have file which has a set of all users and roles with the Splunk account.The file name is usermap.csv

I am using the following query to get all users who have logged in the last 30 days.

*index=_audit action="login attempt" info="succeeded" earliest=-30d | stats max(timestamp) by user | lookup usermap.csv user OUTPUT role1,role2,role3,role4 | eval role2 = if(isnull(role2),"", ", ".role2 ) | eval role3 = if(isnull(role3),"", ",".role3 ) | eval role4 = if(isnull(role4),"", ", ".role4 ) | strcat role1 role2 role3 role4 Role | fields user,max(timestamp),Role | rename user as "UserName",max(timestamp) as "Last Login", Role as "Roles"*

Since I have the complete set of users in the lookup file. I am getting the list of users who had not logged in he last 30 days using the following query.

*| inputlookup usermap.csv | search NOT [ search index=_audit action="login attempt" info="succeeded" earliest=-30d | dedup user | fields user] | fields user*

How Do I get the last login information and roles for the second query.i.e for the users who did not login for the last 30 days.May be I should use the join.But I am not getting it right.

[sanju005ind · Accepted solution]

Well I used something like this.Atleast I was able to get what I want.

| inputlookup usermap.csv | search NOT [ search index=_audit action="login attempt" info="succeeded" earliest=-30d | dedup user | fields user] | fields user | join user [search index=_audit action="login attempt" info="succeeded" earliest=-90d ] | stats max(timestamp) by user | lookup usermap.csv user OUTPUT role1,role2,role3,role4 | eval role2 = if(isnull(role2),"", ", ".role2 ) | eval role3 = if(isnull(role3),"", ",".role3 ) | eval role4 = if(isnull(role4),"", ", ".role4 ) | strcat role1 role2 role3 role4 Role | fields user,max(timestamp),Role | rename user as "UserName",max(timestamp) as "Last Login", Role as "Roles"