https://community.splunk.com/t5/Splunk-Search/Help-on-query-for-user-login-info/m-p/99672#M25696 <P>I have file which has a set of all users and roles with the Splunk account.The file name is usermap.csv</P> <P>I am using the following query to get all users who have logged in the last 30 days.</P> <P>*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"*</P> <P>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.</P> <P>*| inputlookup usermap.csv | search NOT [ search index=_audit action="login attempt" info="succeeded" earliest=-30d | dedup user | fields user] | fields user*</P> <P>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.</P> Mon, 06 Dec 2010 22:22:54 GMT sanju005ind 2010-12-06T22:22:54Z https://community.splunk.com/t5/Splunk-Search/Help-on-query-for-user-login-info/m-p/99672#M25696 <P>I have file which has a set of all users and roles with the Splunk account.The file name is usermap.csv</P> <P>I am using the following query to get all users who have logged in the last 30 days.</P> <P>*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"*</P> <P>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.</P> <P>*| inputlookup usermap.csv | search NOT [ search index=_audit action="login attempt" info="succeeded" earliest=-30d | dedup user | fields user] | fields user*</P> <P>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.</P> Mon, 06 Dec 2010 22:22:54 GMT https://community.splunk.com/t5/Splunk-Search/Help-on-query-for-user-login-info/m-p/99672#M25696 sanju005ind 2010-12-06T22:22:54Z https://community.splunk.com/t5/Splunk-Search/Help-on-query-for-user-login-info/m-p/99673#M25697 <P>Well I used something like this.Atleast I was able to get what I want.</P> <P>| 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"</P> Wed, 08 Dec 2010 23:38:11 GMT https://community.splunk.com/t5/Splunk-Search/Help-on-query-for-user-login-info/m-p/99673#M25697 sanju005ind 2010-12-08T23:38:11Z