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!

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Want a chance to win $500 to the Splunk shop? Take our IT Incident Management Survey!

  Top Trends & Best Practices in Incident ManagementSplunk is partnering up with Constellation Research to ...