Splunk Enterprise

Build a query to identify users not logged in last 90 dys

pranay04
Explorer

For Audit purposes, I need to write a query to find out all the users who has not logged in last 90 days in the datapower servers so that we can identify them and remove their access. I have the query to find out all the user have logged in 90 days and then manually remove the one's who haven't. But we are looking to fully do an automated query that tells us who hasn't logged in.

This is the event that logs when a user is successfully logged in

user(xxxxx): [abc.10.hsc.120]: User logged into 'environment'.

Query I am using to find who has logged in

index=datapower environment=* "user logged into" NOT svc | eval Time=strftime(_time, "%H:%M:%S") | eval Date=strftime(_time, "%Y-%m-%d")|dedup UserID| stats count by UserID, environment, Date, Time

Need help to write something which will tell me who has not logged in.

Labels (2)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi pranay04,
if you have the list off all your users, you could:

  • create a lookup (called e.g. "my_users.csv") with all these users, in a column called "UserId"
  • run a search like the following

    index=my_index earliest=-90d@d latest=now
    | eval UserId=upper(UserId)
    | stats count by UserId
    | append [ | inputlookup my_users.csv | eval UserId=upper(UserId), count=0 | fields UserId count]
    | stats sum(count) AS Total BY UserId
    | where Total=0

Bye.
Giuseppe

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Now that you have a list of everyone who has logged in, you just need to compare that to a list of all users with access.

Use the means at your disposal to create a CSV file of all Datapower users and load the file in Splunk. The CSV file should have a column called "UserID". Then you can use that file as a lookup in a query like this one:

| inputlookup datapowerusers.csv 
| search NOT [index=datapower environment=* "user logged into" NOT svc |dedup UserID | fields UserID | format]
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...