Splunk Search

Help on query for user login info.

sanju005ind
Communicator

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.

Tags (1)
0 Karma
1 Solution

sanju005ind
Communicator

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"

View solution in original post

0 Karma

sanju005ind
Communicator

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"

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...