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!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...